Excel’s FILTER Function: Filter Across Multiple Worksheets

In Excel 365 you can use the VSTACK function with the FILTER function to filter across multiple worksheets.  You can watch the video tutorial above or follow the instructions below.

Download the featured file here.

In my scenario I have a workbook that contains four worksheets, one for each customer type: Web, Store, Account Holder & International.  I am going to create a new worksheet in the same workbook and filter all four worksheets for sales of books.

I recommend that each data set in each of the worksheets is housed in an Excel table.  You can easily do this by selecting a single cell in the data set and then use the shortcut key CTRL T to create the table.  You will then need to click OK to confirm.

You should also give each table a name and you can do that on Excel’s Ribbon: make sure the Table Design tab is selected and then on the far left of the Ribbon enter a name in the Table Name box.

So in my new worksheet where I am going to perform the filter, I first use the VSTACK function to stack the data together.  As you can see below I list the tables separated with a comma: =VSTACK(web,store,ACCT_Holder,International)

I then use the FILTER function where VSTACK provides the array.  The includes argument also needs to use VSTACK, but this time I specify which column within each table I am applying the criteria to. The column in my example is the Product Group column. Notice the square brackets used to specify the column. =FILTER(VSTACK(web,store,ACCT_Holder,International),VSTACK(web[Product Group],VSTACK(store[Product Group]),VSTACK(ACCT_Holder[Product Group]),VSTACK(International[Product Group])=B1)

Use ENTER on your keyboard to confirm and return the filtered results.

 

7 thoughts on “Excel’s FILTER Function: Filter Across Multiple Worksheets”

  1. Nice post. I was checking continuously this blog and I’m impressed!
    Extremely useful information specially the last part :
    ) I care for such information much. I was looking for this certain information for a
    long time. Thank you and best of luck.

  2. You actually make it seem so easy with your presentation but I find this topic to be really
    something which I think I would never understand. It seems too complex and
    very broad for me. I am looking forward for
    your next post, I’ll try to get the hang of it!

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top