Default Sort sorting header row

Question

Excel 2007

Occasional I am working with a sheet of data that headers at the top and the ribbon sort works fine - the header stays put. But on occasion the sheet starts including the header row and it gets shuffled around where it doesn't belong.

Freeze top row doesn't keep it in place.

I tried to change the formatting on just the top row - no joy.

Naming the cells below the header row doesn't work.

How do I get back to the default that exlcudes the top row from the sort?

Answer 1

Found the answer - Data tab, Sort dialog - in upper right is a checkbox My Data Has Headers.

Answer 2

Hi,

I do not believe you can do that.  Furthermore, it is not a good practise to maintain multiple rows for headers.

Answer 3

Hi,

If you want Sort and Filter to pick up your first row as the header row every time, applydistinctive formatting to the header row, such as header in Bold. Now when you select the entire data set including the header row, "My Data has header" box will be checked.

Hope this helps..

Answer 4

Found the answer - Data tab, Sort dialog - in upper right is a checkbox My Data Has Headers.

This does not work if you have more than one header line.  How can I lock all header lines?  Thanks

Answer 5

This solution works, but I've found with one spreadsheet that I need to do this every time I open it, otherwise the header rows get sorted with the data as you've described.

What would be great is to turn this option on as a default, i.e. always assume my table has header rows, rather than always assuming that it doesn't. If this option exists, I can't find it.

I'm on Office 2011 for Mac, by the way. Not sure if that is different to the functionality in Office 2010.