Excel Help - apply same filter across multiple sheets.

willhub · 27 May 2020 at 09:57

Hi.

At work I do team stats, and currently the system we use had each day's stats on a sheet, the thing is it's the whole floor, not just my team, so on each sheet I have to individually filter out the names of my team, then repeat on a different sheet, this adds extra time.

Is there a way to auto apply this filter for my team on every single sheet, without using any special addons (because work computer won't allow it)

Thanks.

beh · 27 May 2020 at 21:26

How many sheets? Do you mean sheets or workbooks? Is it the same ones each day or are new ones being added?

Depending on the above and whether everything is consistently formatted, not sure why you'd be filtering the data in place when you could just reference what you need in a pivot table on a separate workbook (you can leave the filters applied on the pivot table and just "refresh all" each day). If you need to join everything together first use power query.

willhub · 2 Jun 2020 at 08:18

So a new sheet just gets added for every day. Each sheet is a day's worth it stats, and yes these stats do change. sometimes there are delays and instead of just doing the previous days I need to go through up to 7 days.

beh · 3 Jun 2020 at 20:57

So the way I suggested should work fine then. Use powerquery (data > get data > from file > from workbook) to combine all the sheets, this seems to explain it well enough - https://www.youtube.com/watch?v=X5PxoM-0kBM . You can now use that query as a data source for a pivot or whatever. You could filter for your team members either in the query or the pivot. I'm skipping over a lot of the detail but once set up you can just go to data > refresh all whenever you need to get the latest stats. Whereas it sounds like at the moment you're going through each sheet manually, filtering it, and then copying the data you want?