HCHTech
Well-Known Member
- Reaction score
- 4,237
- Location
- Pittsburgh, PA - USA
This is something that has been bugging me for a while and I finally forced myself to try and find the answer.
When you sort a range in Excel, it tries to help you by guessing whether or not you have headers in the first row. If it thinks you do, then it pre-checks the "My data has headers" checkbox on the sorting dialog. If it thinks you DON'T, then it leaves this checkbox blank. So far, so good - except it seems that this guessing process is often wrong - resulting in you having to either uncheck the box or check the box when it guesses wrong.
In two business-critical spreadsheets that I am in every single day and sort multiple ways to answer different questions, it was getting this guess wrong 100% of the time. I have a blank row between the titles and the data, and I never include that row in the sorting range, yet it insisted on pre-checking that box. Every time.
I turns out that ANY content in any of those cells, or ANY special characteristics applied to those cells (centering, shading, etc.) causes Excel to think they are titles and therefore pre-check that damned box. Since both of the spreadsheets where I have this problem are old and have been saved daily for years and years, some attribute or another must have gotten stuck on for one or more of these blank-looking cells. Ultimately, I deleted that row altogether, inserted a new row, then checked the attributes on every cell to make sure there were not invisible borders, or white shading, etc. - things that would look blank but not BE blank.
What do you know, it has guessed correctly that there are no headers in my sort range every time for a week now. I cannot tell you how these little thorn-in-the-side issues get to me over time. Well, it appears that dragon is now slain, so there is one less thing bothering me every day now.
I hope this helps someone!
When you sort a range in Excel, it tries to help you by guessing whether or not you have headers in the first row. If it thinks you do, then it pre-checks the "My data has headers" checkbox on the sorting dialog. If it thinks you DON'T, then it leaves this checkbox blank. So far, so good - except it seems that this guessing process is often wrong - resulting in you having to either uncheck the box or check the box when it guesses wrong.
In two business-critical spreadsheets that I am in every single day and sort multiple ways to answer different questions, it was getting this guess wrong 100% of the time. I have a blank row between the titles and the data, and I never include that row in the sorting range, yet it insisted on pre-checking that box. Every time.
I turns out that ANY content in any of those cells, or ANY special characteristics applied to those cells (centering, shading, etc.) causes Excel to think they are titles and therefore pre-check that damned box. Since both of the spreadsheets where I have this problem are old and have been saved daily for years and years, some attribute or another must have gotten stuck on for one or more of these blank-looking cells. Ultimately, I deleted that row altogether, inserted a new row, then checked the attributes on every cell to make sure there were not invisible borders, or white shading, etc. - things that would look blank but not BE blank.
What do you know, it has guessed correctly that there are no headers in my sort range every time for a week now. I cannot tell you how these little thorn-in-the-side issues get to me over time. Well, it appears that dragon is now slain, so there is one less thing bothering me every day now.
I hope this helps someone!
