Forum Discussion
how do I use multiple stages of Excel filtering/slicing?
I have data in an Excel table. I want to first use either filters or slicers to limit the data down to specific cities and zip codes. This level of filtering should be dynamic because I want to be able to change this filtering depending on the area I am analyzing.
I then want to use the FILTER function to copy the data to other sheets to organize it by the month of the sale. ie. the first sheet will contain the data for Jan, and other for Feb, etc.
The Filter function on the secondary sheets should grab the data that is already limited by the slicers or filters in step one.
Edit:
I was going to add a sample .xlsx file to demonstrate, but I don't see the functionality that allows me to add anything but images. I am adding screenshots of my xlsx file, but if someone can tell me how to add it, I will.
The first sheet contains all the data. I want to be able to use either
1. the default filters that are given when a table is created or
2. slicers
to limit the data to say only in "Kirkland" and with 3 bedrooms under 1200sf. Each of the subsequent sheets for example June 2022, can be populated using a filter function (cell B2) to bring only the data with "Listed Date" during that month.
Yes I could do this by adding an initial sheet with variables, where I specify the filter values, but I want to be able to use the default filters, or slicers because with each analysis I may filter on different fields, and may have 1 filter, many filters, or no filters. I like the flexibility and ease of using built in filters or slicers.
How do I do this, since the Filter function is grabbing All the data, and not the data that is limited by the filtering (or slicers) in the first step?
cravis777 Add a helper column to the data table that flags the rows as visible or not. Then, in the month sheets you can filter the original for the time period you want AND where the visible flag = 1.
I used the file that OliverScheurich uploaded to demonstrates this method. See attached.
5 Replies
- How do you put a filter on a slicer?
Use a slicer to filter data
Select Insert > Slicer.
Select the fields you'd like to filter.
Select OK and adjust your slicer preferences, such as Columns, under Options. Note: To select more than one item, hold Ctrl, and then select the items that you want to show.
Select Clear Filter. to clear the slicer filter.
Hope this helps =FILTER('master sheet'!A2:C321,('master sheet'!A2:A321=F2)*('master sheet'!C2:C321=1)*('master sheet'!B2:B321>G2)*('master sheet'!B2:B321<H2))You can try this formula in the attached file if you work with Office365 or 2021. The example shows the sheet for january. In cells F1, G1 and H1 you can dynamically select the city and zip code range. The file has sheets for january to april and you can set up sheets for the other months accordingly.
- Thanks for the response OliverScheurich, I am hoping that there is a way to use the default filters (in the top row of the Table) or by using Slicers, rather than putting the values for the filters into a cell somewhere and use the values in the Filter function as you suggest. The fields that I filter on will change depending on my analysis, as will the number of fields I filter on.
cravis777 Add a helper column to the data table that flags the rows as visible or not. Then, in the month sheets you can filter the original for the time period you want AND where the visible flag = 1.
I used the file that OliverScheurich uploaded to demonstrates this method. See attached.
