Pivot tables start appearing in strange sequences in Excel 2007.
Szilvia noted a strange anomaly in Excel 2007 pivot tables. Microsoft started appearing at the top of every customer list! Episode #1170 takes a look at this mystery and the solution.
Szilvia noted a strange anomaly in Excel 2007 pivot tables. Microsoft started appearing at the top of every customer list! Episode #1170 takes a look at this mystery and the solution.
Transcript of the video:
The MrExcel podcast is brought to you by “Easy-XL”!
Hey, alright! Welcome back, it's Bill Jelen, ready for another year of MrExcel netcast, I want to thank you so much for stopping by, it's 2010, but we have a great question today.
First off, I want to show off my new mouse, check this out, I got this from Shar(?), Shar works here in the office, alright, so it's shaped like a car, right, got it?
But check this out, as it cruises around, the headlights actually light up.
Isn't that the geekiest coolest thing you've ever seen?
So thanks to Shar for that.
Alright, today's question from Szilvia, now I know Szilvia’s out on the West Coast, great trainer out there, if you're in LA you need to contact Szilvia.
And Szilvia said “Hey, I got a strange mystery with a Pivot table here, I'm in Excel 2003.
I go to Data, PivotTable, will click Finish, and let’s create a Pivot table with customers down the left-hand side and revenue here, and we start out AIG, AT&T, Bank of America.” Just like Szilvia got.
Now we go to Excel 2007, alright, and we're going to create a Pivot table, so Insert PivotTable, OK, check-mark Customer and check-mark Revenue.
What's this?
Microsoft comes to the top!
You got to be kidding me!
Same computer, and for some reason, Microsoft is putting themselves at the top of Excel 2007.
You know, that just seems like a really, really strange thing.
The cool thing about really strange things, is we get to learn something about how Excel really operates.
Once we figure out the real reason, that it's not just some bizarro, you know, ego trip on Microsoft's part, this sequence will always default to alphabetical, A-Z, unless it thinks that you have a custom list set up!
Alright, so even we don't have a custom list with all of these items, but we have a custom list that happens to have Microsoft.
Excel will see that and assume that we want everything organized by the custom list, and then after the custom lists, all the alphabetical things come next.
So we went into Excel Options, took a look at Edit Custom Lists, and down here at the bottom of Edit List, sure enough, there's a list with Microsoft, Intel, and Cisco.
Alright, so that was enough to cause Microsoft to come to the top.
The real bizarre thing that kind of threw us off for a second, was the fact that Excel 2003 did it one way, and Excel 2007 did it the other way.
And what we realized there is that the custom lists, when you set a custom list up in Excel 2007, it is not shared with the custom list in Excel 2003.
So that's why it had one behavior in 2003, one behavior in 2007, I love mysteries like that.
I want to thank Szilvia for sending that question in, and I want to thank you for stopping by, ready for another year of netcast from MrExcel!
Hey, alright! Welcome back, it's Bill Jelen, ready for another year of MrExcel netcast, I want to thank you so much for stopping by, it's 2010, but we have a great question today.
First off, I want to show off my new mouse, check this out, I got this from Shar(?), Shar works here in the office, alright, so it's shaped like a car, right, got it?
But check this out, as it cruises around, the headlights actually light up.
Isn't that the geekiest coolest thing you've ever seen?
So thanks to Shar for that.
Alright, today's question from Szilvia, now I know Szilvia’s out on the West Coast, great trainer out there, if you're in LA you need to contact Szilvia.
And Szilvia said “Hey, I got a strange mystery with a Pivot table here, I'm in Excel 2003.
I go to Data, PivotTable, will click Finish, and let’s create a Pivot table with customers down the left-hand side and revenue here, and we start out AIG, AT&T, Bank of America.” Just like Szilvia got.
Now we go to Excel 2007, alright, and we're going to create a Pivot table, so Insert PivotTable, OK, check-mark Customer and check-mark Revenue.
What's this?
Microsoft comes to the top!
You got to be kidding me!
Same computer, and for some reason, Microsoft is putting themselves at the top of Excel 2007.
You know, that just seems like a really, really strange thing.
The cool thing about really strange things, is we get to learn something about how Excel really operates.
Once we figure out the real reason, that it's not just some bizarro, you know, ego trip on Microsoft's part, this sequence will always default to alphabetical, A-Z, unless it thinks that you have a custom list set up!
Alright, so even we don't have a custom list with all of these items, but we have a custom list that happens to have Microsoft.
Excel will see that and assume that we want everything organized by the custom list, and then after the custom lists, all the alphabetical things come next.
So we went into Excel Options, took a look at Edit Custom Lists, and down here at the bottom of Edit List, sure enough, there's a list with Microsoft, Intel, and Cisco.
Alright, so that was enough to cause Microsoft to come to the top.
The real bizarre thing that kind of threw us off for a second, was the fact that Excel 2003 did it one way, and Excel 2007 did it the other way.
And what we realized there is that the custom lists, when you set a custom list up in Excel 2007, it is not shared with the custom list in Excel 2003.
So that's why it had one behavior in 2003, one behavior in 2007, I love mysteries like that.
I want to thank Szilvia for sending that question in, and I want to thank you for stopping by, ready for another year of netcast from MrExcel!
