microsoft excelmicrosoft-office
I have a spreadsheet which uses 2 rows for header information. Filtering by selecting a column is implicitly 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?
Beware of merging cells, both vertically and horizontally.
Determine what headers you want to sort by. You don't need to create a table to sort items by their headers. But if you choose to create an Excel table, then the headers must be unique.
Consider creating a structure starting in row 2 with header names like item, RMB, USD, RMB USD
Enter some data. Then turn on autofilter for that range. Now you have the sort/filter drop-down buttons in the header cells. Next, enter any text in row 1. You can even merge cells A1 and A2 if you really feel the need.
In short, you don't HAVE to merge cells. Use formatting instead. You don't HAVE to use a table to benefit from sorting and filtering functionality.
Consider:
=AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*16-14),0,0,16,1))
should be placed in B1
If copied down:
B1 will show the average of A2 thru A17
B2 will show the average of A18 thru A33
etc.
EDIT#2:
To get B2 to represent the average of A2 thru A17, use:
=AVERAGE(OFFSET(INDIRECT("A"&ROW(A2)*16-30),0,0,16,1))
Best Answer
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.