Lisa from Chicago sends in today's question. Why can't you sort the page field in a pivot table? Episode 1059 talks about several ways to sort the row fields in a pivot table and then a method for sorting the filter field.
This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!
This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question came in from Lisa Biel, I met Lisa at our Chicago boot camp and she had a question about Pivot Tables and let's just talk about Pivot Tables, there's a couple of cool sorting things that 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 did not appear in alphabetical sequence that's because I have a custom list defined with Central West Indies, so first thing is we can control this order or 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 the set with the largest product appears at the top, Right Click, sort, more sort options and say descending based on some of revenue, click OK, all right 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 store, you'll see that this one is sorted in some weird sequence not the sequence that we want at least this 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 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 a proper sequence and then once we've sorted it down there in the row area take it back up to the report filter where it will now respect that sorting, so there we've sorted it into the proper sequence you can even it's interesting you know when it's down here you can drag and drop things into a new position, so if you wanted all the banks together simply drag that up and you have Bank of America and Citi 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 Citi 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 items so you want to have up here at the 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 thank you for stopping by; we'll see you next time for another netcast from MrExcel.
Today's question came in from Lisa Biel, I met Lisa at our Chicago boot camp and she had a question about Pivot Tables and let's just talk about Pivot Tables, there's a couple of cool sorting things that 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 did not appear in alphabetical sequence that's because I have a custom list defined with Central West Indies, so first thing is we can control this order or 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 the set with the largest product appears at the top, Right Click, sort, more sort options and say descending based on some of revenue, click OK, all right 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 store, you'll see that this one is sorted in some weird sequence not the sequence that we want at least this 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 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 a proper sequence and then once we've sorted it down there in the row area take it back up to the report filter where it will now respect that sorting, so there we've sorted it into the proper sequence you can even it's interesting you know when it's down here you can drag and drop things into a new position, so if you wanted all the banks together simply drag that up and you have Bank of America and Citi 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 Citi 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 items so you want to have up here at the 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 thank you for stopping by; we'll see you next time for another netcast from MrExcel.
