Pivot Sorting - 1059 - Learn Excel from MrExcel
Hey, welcome back to the Mr. Excel netcast. I’m Bill Jelen. Face it you, you start out with a massive amounts of data. Say, how are you going to analyze this, well, let’s fire up activity table and see if we can solve this problem. Hey, welcome back to the Mr. Excel netcast, I’m Bill Jelen. Now, today’s question came in from LisaB. I met Lisa in our Chicago boot camp and she had a question about pivot tables. And let’s just talk about pivot tables. There are a couple of cool sorting things we can do in pivot tables. Here, I created a pivot table with products down the side, regions across the top. First thing I want you to notice is the regions does not appear in alphabetical sequence. That’s because I have a custom list to find with central, west and east. So, first thing is we can control this order automatically, just by having a custom list that matches the order that you want. But we have products here that are appearing alphabetically. And I can control this. I can change that way the largest product appears at the top. Right click, sort, more sort options, and say descending base on sum of revenue. Click OK. Alright, so we have a few different sort options there. But up here in the filter area, it’s very frustrating that we don’t have any control over how to sort. You see that this one is sorted in some weird sequence. Not the sequence that we want, and Lisa’s question is, why can’t I control the sort order of that field? You know, it’s very frustrating, the things that might be new or appearing at the end of the list and so on. Why don’t I have any control? And the solution to this is kind of bizarre. What we’re going to do is we’re going to take the customer field and move it back to the row label area. Now that it’s in the row label area, check it out. We can right click and come in here and sort and say sort A to Z, and we get things back in their proper sequence. And then, once we sorted it down there in the row area. Take it back out to the report filter where it will now respect that sorting. So there, we can sort it in to the proper sequence. You can even, it’s interesting when it’s down here you can drag and drop things in to a new position. So if you want all the banks together, simply drag that up and you have bank of America and City Group. And then when we bring it back up to the report filter, we actually will find that they appear in the filter in that particular sequence, Bank of America and City Group. So kind of cool that you can actually customize how things appear on the filter. You might have two or three or four or five, I don’t see you want to have appeared on top. Those are the most common perhaps. Just take it down to the row label area. Do the sort, or drag and drop and then take it back up to the report filter. And you have control over how they appear. So, great question from Lisa, who I met in Chicago. And I want to thank her for sending that question in. I want to thank you for stopping by. We’ll see you next time for another netcast from Mr. Excel.