Link to home
Create AccountLog in
Avatar of Frank .S
Frank .SFlag for Australia

asked on

excel 2016 - filtering 2 rows in wksht from 2 different tables

hi experts i have 2 tables on the 1 worksheet, 1 under the other & i need to filter row 2 from the 1st table & row 21 from 2nd table but the filter function only lets me filter 1 row not 2, how do i filter 2 rows?
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Are they actual tables or just separate lists with AutoFilter?

With actual data tables, each table can have their own filter; with standard range/list you can only have one AutoFilter range per sheet.

I assume it is the same sheet you sent for the Conditional Formatting question. If so, data tables can't have multiple header rows, like you have for some of your columns, eg Door Handling
User generated image
Avatar of Frank .S


- yes same file as the "conditional formatting question" they are separate lists with auto filter.
- what would i need to do to make my lists data tables?
Just looking again, is it column A that you need to filter on "Location"? If so will the location in top table and bottom table be the same. If so, just include both tables in one autofilter.

Step 1 - decide what to do with the headers. You can put the section header (row 2 and 21) above the table if you want and just have the detail header (row 3 and 22) as the table header.
Step 2 - select the Table area including header, press Ctrl + T and it will bring up the Insert Data Table dialogue, check the area selected and "Data includes headers" is checked, click OK.
Formatting will change to Table formatting with alternating colour rows. Each header will now have its own filter option.

- im a little confused with your question because there are 2 rows to be filtered, row 2 & 21
- no, the location on the top table & bottom table are not the same, different
- in actual fact its rows 2&3, and then rows 21&22 that need the filter
- ill try understand your step 2 & give it a go
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ok thanks rob, looks like the cell drop downs were not copied into the tables you created, do i need to do this again?
Do you mean the Filter dropdowns? They are on rows 3, 22 and 40 on thew version I uploaded.

Do you mean Data Validation in cell selections? There weren't any in the file you uploaded.
hi rob, no not filter d/downs, but data validation.
There weren't any in the file you uploaded.
ok, thats strange.. i should have d/loaded the file rather than screenshotting..thanks for your reply