"My data has headers" -- ALWAYS

[mgard01]

Using Excel 2010; have an annoying problem, and would greatly appreciate some help.

After years of having no sorting problems using the A-Z and Z-A sort buttons when the cursor is on a single cell within a large range, all of a sudden the header row is unpredictably sorted along with the data rows.

The row above the header row is completely blank. The row below the last data row is also completely blank. Every cell in the header row contains a label. Every label is different in some respect, usually data type, from the data below it.

Now, when the header row is unpredictably sorted along with the data rows, I must undo the sort, manually go into Data->Sort, then check "My data has headers." This is extremely annoying because it is so time consuming.

Is there any way to make Excel default to a checked box next to "My data has headers"? Or is there any alternate way to eliminate the problem?

Thanks,
Marv

 

[shg]

Try formatting the header row differently than the data. My header rows are centered with borders and shading, and Excel invariably guesses correctly.

 

[mgard01]

I put a thick border at the top of each header cell. For a while, it looked as if that worked, then it started failing again. Unpredictably.

Might a different font in all the header cells be a sufficient difference for Excel to "guess" correctly?

By the way, has Microsoft documented the logic it employed to "guess"?

 

[shg]

Not that I've ever seen; someone else may have.

 

[xenou]

For what it's worth, I've never had a problem with Excel not finding headers. If anything, it's the other way (when I *don't* have headers sometimes I have to uncheck the headers box manually). I'm not sure what to make of your experience - can you provide sample data that you are using when this happens?

 

[hunycombs]

This is very annoying to me as well... even after i go in and remove the header option and save...it still comes back after I save, close and then come back in. I would greatly appreciate a FIX on this problem !!

 

[xenou]

Hit {Control + Shift + Spacebar} all at once to select the current region - it should pick up all your data. I actually do this as a matter of course whenever I sort data, in order to select the data I wish to sort.

 

[ChrisNC]

I realize this is an old thread, but I'm sure the following will still be of help to others even now. For a long time, I couldn't figure out why "My data has headers" was constantly deselected in only one of my workbooks. I didn't *think* I had any blank cells in the header row, but I did: in MERGED cells. When merging, you know how it says it only keeps values in the upper left and discards other values? If you merge, it makes one header cell blank, which causes "My data has headers" to constantly be deselected. I un-merged them and entered values into each, now "My data has headers" is always selected. If you want the opposite, then merge two header cells, or insert a blank column into the worksheet and hide it. I haven't tried the latter, but I think it would have the same effect.

 

[BC4now]

OMG ChrisNC ... you had the solution!! This has been bugging me for a while; I have had all of the intermittent issues described; Once I read your post, realized my spreadsheet was guilty of that. Fixed! Thank you!!