Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

10 Answers 10

up vote 9 down vote accepted

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.

share|improve this answer
    
It is. I didn't realize I could select a specific cell to start the filtering at, as opposed to doing a column select. –  Dan Neely Nov 12 '09 at 14:45
add comment

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.

share|improve this answer
add comment

Okay, the following works in Excel 2010, even after saving the file back as an Excel 2007 and re-opening (so presumably works in Excel 2007 as well...)

Assuming a 3 row header. Set the spreadsheet filter range to Start at cell $A$4 and ensure that it covers the full extent of data you want to sort. SAVE THE FILE.

Henceforth, any sort will treat rows 1 to 3 as headers and therefore sort from row 4 onwards only. - UNLESS you change or cancel the filter range....

share|improve this answer
    
The response from deedgess worked for me. When you add a filter, excel assumes every row above it is a header. In my case, I have a 2 row header. I selected row 2 and turned on the filter. Then I selected custom sort and checked the box for header. When the sort happened, both row 1 and row 2 remained as header. –  user254440 Sep 15 '13 at 17:11
add comment

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.

share|improve this answer
3  
Nope, this doesn't work. It's true that Excel 07 can autodetect multi-row headers in some cases, but it's based on cell contents, not on which cell you click. For example, if the first two rows are text and everything below is numeric, then Excel will assume the first 2 rows are both headers. –  Josh Mar 23 '10 at 16:30
add comment

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

share|improve this answer
add comment

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.
share|improve this answer
add comment

I am using Excel 2010. To retain the heading rows (e.g. the first 4 rows), highlight row 5 and then turn on the filters. Rows 1 - 4 are excluded from the filter.

share|improve this answer
add comment

If you select an entire row (by selecting the row number to the left of the row) and then enable your filter (Data > Filter) then it will give you filters for everything below the selected row and ignore everything above it.

share|improve this answer
add comment

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...

share|improve this answer
    
Sorry but I don't understand this at all - why "second" column, are you assuming (quite reasonably!) the first row has merged cells, why hide the added text, does this process require changing the content as the filter selection is changed, why not centered in the cell? –  pnuts Oct 19 '12 at 23:36
add comment

I had the same problem and found a solution that works for me. For every row in the header that you don't want to see, you make te letters white (or the backgroundcolor). When you sort you don't see all the same headers in the rows. To bad when you want to change something, but in that case you can also make them light grey.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.