Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a spreadsheet which uses 2 rows for header information and some of the columns have the 2 rows merged together as one cell. I a multiple columns merged together in the first row, but the second row underneath without the columns merged together. Filtering implicitly is assuming only 1 row of header information. Sorting has a header row check-box 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

Try this:

  1. Unmerge the headers that are merged.
  2. Select your data manually, from the second row downwards.
  3. Apply the filter.
  4. Re-merge the headers.

The filter dropdowns should stay on row 2.

share|improve this answer

You need to ensure that the value in advanced filter Field Range is set to include the rows below your header rows.

You can set this value manually or use a macro to do it for you.

EG in my spreadsheet, I have rows 1-3 as multi line headers. I constantly add rows of data below. The following macro sets the Filter Field Range to include rows 4 - to the last populated row on the spreadsheet. This ensures that any sort will treat row 3 as the header (provided you check the 'my data includes headers' checkbox)

(I incorporate this at the end of a more complex macro, to ensure that the Field Range property is always up to date after data imports - easier than remembering to manually re-set it each time)

Sub selector()


' find last row containing data in column A (column A will always be populated in this dataset)

Set WS = Worksheets("AESummary")
With WS
    Set LastCellC = .Cells(.Rows.Count, "A").End(xlUp)
        LastCellRowNumber = Application.WorksheetFunction.Max(LastCellC.Row) + 1
   End With

' set the filter Field Range value to include Row 4 to the last populated row in the worksheet

        Rows("4:" & LastCellRowNumber).AdvancedFilter Action:=xlFilterInPlace, Unique:=False

End Sub
share|improve this answer

Well, better late than never

Define a local (worksheet) name "Database" from the last header row to the last table row. That is:

Formulas » Name Manager » New » Name:Database, Scope:{sheet name}, Refers to:{select all table and manually change first cell's row}

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .