Multi-row headers and excel 2007 sort/filtering

Question

I have a spreadsheet which uses 2 rows for header information. Filtering implicitly is assuming only 1 row of header information. Sorting has a header row checkbox but that only toggles treating the first row as a header.

Is there a way to make excel treat the first two rows as headers?

Answer 1

No. Omit the first row from your range when you auto filter. This way the auto filter buttons appear only on your bottom header row and the data gets filtered. I expect that right now your second header row is getting pulled into your 'data'.

You can't select a single cell and have excel figure this out. You have to select the range of cells you want excel to include.

Answer 2

An easy way to accomplish the sort function using multiple header rows is to insert a blank row just above the row you want to sort by (ideally, it is bottom-most in your header. If not, make it so.). Then click on the 'row' number highlighting the empty row. Right click that row and select "Hide'. The new, empty row will vanish leaving your header the way you wanted it to look and Excel will interpret your category row as the header.

Answer 3

Excel 2007 can smartly detect if you have multi-row headers. I made a simple experiment just now before writing this.

Before making your first sort, position the cursor right below the header. That's it! Sorting is fine, filtering is fine. Excel ignored the 1st row of my headers. It just processes the 2nd row as the real header.

Answer 4

Or you could just merge the two header rows.

Answer 5

Highlight the cells that need to be sorted (all except the headers) and then select filter.

Answer 6

To filter a list with 2 header rows, where the second row contains the column headings, here is what I did.

• Cut the first row
• Turn filtering off
• Turn filtering on again so that the second row is filtered. This tells Excel which is the row with the column headings.
• Paste the first row back in again above the row with the filters and column headings
• Check to see if the filter now works, with the column headings on the second row.

Answer 7

Another way to accomplish in some cases is to enter a value in the second column that will cause the filter to include it, then make the text of that cell match the background colour of the cell. Not that pretty since the header (row 1) will not be horizontally centred in the cell but it will work...