Jeffrey wonders why the top row of his data won’t sort. He has a blank row between the data and the headers, which looks great, but is fooling the Intellisense. Episode 1051 shows you how.
This blog 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 the 377 tips from the book!
This blog 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 the 377 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How we're going to analyze this, let's fire up a pivot table.
Let's see, if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen, Today's question and file send in by Jeffrey, Jeffrey has this file and he wants to sort it descending.
So, it clicks the Z to A, button up here and you see that the first row is being treated as a headache.
So, Abilene, always stays at the top then followed by Zanesville and so Jefferson, like what's up with this.
Although, he admitted right there that the row right above that data, row 6 is a tiny little row they use to make things look good.
Well, that tiny little row is generally causing the problem.
But, there is an easy workaround rather than use, A to Z or Z to A.
Let's just go in to the Sort dialog and uncheck the box for My data has headers, and now, you can say we're gonna sort by column A and sort Z to A, click OK.
And it successfully sorts that data to the bottom.
So, if your data is set up correctly with one header row and then no blanks and all of the data in nice contiguous block, you can sort with a single click using the A to Z or Z to A buttons.
But, if you have something special like Jeffrey does, the the blank row for to make it look good aesthetically, then you have to go into the Sort dialog and say that your data does not have headers, still possible to sort through.
All right.
Hey, I want to thank you for stopping by.
See you next time for another netcast from Mr Excel.
Thanks for stopping by.
See you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How we're going to analyze this, let's fire up a pivot table.
Let's see, if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen, Today's question and file send in by Jeffrey, Jeffrey has this file and he wants to sort it descending.
So, it clicks the Z to A, button up here and you see that the first row is being treated as a headache.
So, Abilene, always stays at the top then followed by Zanesville and so Jefferson, like what's up with this.
Although, he admitted right there that the row right above that data, row 6 is a tiny little row they use to make things look good.
Well, that tiny little row is generally causing the problem.
But, there is an easy workaround rather than use, A to Z or Z to A.
Let's just go in to the Sort dialog and uncheck the box for My data has headers, and now, you can say we're gonna sort by column A and sort Z to A, click OK.
And it successfully sorts that data to the bottom.
So, if your data is set up correctly with one header row and then no blanks and all of the data in nice contiguous block, you can sort with a single click using the A to Z or Z to A buttons.
But, if you have something special like Jeffrey does, the the blank row for to make it look good aesthetically, then you have to go into the Sort dialog and say that your data does not have headers, still possible to sort through.
All right.
Hey, I want to thank you for stopping by.
See you next time for another netcast from Mr Excel.
Thanks for stopping by.
See you next time for another netcast from MrExcel.
