From the course: Excel PivotTables: Mastering PivotTables and PivotCharts

Troubleshooting incorrect sorting - Microsoft Excel Tutorial

From the course: Excel PivotTables: Mastering PivotTables and PivotCharts

Start my 1-month free trial

Troubleshooting incorrect sorting

“

- [Instructor] Alright, quick pro tip here as far as sorting is concerned. There are certain cases where you may want to try to sort a list alphabetically, and you see some weird values that don't seem to be following true alphabetical order. And I'll show you an example of that, and why it sometimes happens. So to do that, let's kind of reset our pivot table view a little bit, and I'm going to pull country and genre out, going to bring title in, as a row label, and let's lose that second instance of gross revenue. So in my title column, let's just set it A to Z, and this should be alphabetical, but you'll see some weird titles in the top of the list. We've got May in row seven, Friday, November, these are actual movie titles, but they start with the letter M, F, and N. There's no reason why they should be at the top of the list when we're sorting alphabetically from A to Z. You can see that starting here in row 10, from then on out, everything looks to be pretty accurate. Got our movies that start with numbers, and then heading into the As, Bs, Cs, Ds, so everything else seems fine. It's just these three titles up at the top. And one thing you may have noticed is that these are all date-related titles. So the words May, Friday, and November are part of Excel's custom lists behind the scenes, that help it understand how to interpret those as dates. And since custom lists take precedence over alphabetical ordering, we end up seeing those fields at the top of our list. So luckily this is an easy fix. All you need to do is go into your pivot table tools, options on the left, go into totals and filters, and just uncheck this box that says use custom lists when sorting, and press OK. Once you do that, those three values will disappear, they'll show up later in the list according to normal alphabetical logic, and there you go. So you may or may not ever run into that issue, but if you do, now you know what's causing it.

Contents