Filter A List Of Data

1. Making A List Of Data Frames
2. Filter A List Of Data Excel

Watch in full screen HD or on. Download File Download the sample Excel file to follow along. (100.6 KB) The (Multiple Items) Dilemma The Filters area of the pivot table allows us to apply a filter to the entire pivot table. This is a great way to filter the report to only see data for certain time periods, categories, regions, etc. We can check the Select Multiple Items box in the filter drop down menu to filter the pivot table for multiple items in the field. This is a really powerful feature. However, when we filter for more than one item, the cell that contains the filter drop-down menu displays the phrase “(Multiple Items)”.

From the Data menu, click Clear Filters. Remove all filters that are applied to a table. Select the columns of the table that has filters applied, then from the Data menu, click Clear Filters. Remove filter arrows from or reapply filter arrows to a range or table. On the Standard toolbar, click Filter. For example, when filtering numbers, you’ll see Number Filters, for dates you'll see Date Filters, and for text you'll see Text Filters. The general filter option lets you select the data you want to see from a list of existing data like this.

There is no way to see what items the pivot table is being filtered for unless we open the filter drop-down menu and scroll through the list. This is time consuming, and can also cause confusion for readers and users of our Excel files. 3 Ways to Display the Filter Criteria on the Worksheet Even though there is no built-in way to display the filter list, I have 3 simple workarounds that can be implemented pretty quickly.

It's important to note that these solutions are additive. That means in order for solution #3 to work, we will need to implement solutions #1 and #2 first. Read on and you will see what I mean. Solution #1 – Add a Slicer to the Pivot Table The quickest way to see a list of the Multiple Items in the filter is to add a slicer to the pivot table. Select any cell in the pivot table. Select the Analyze/Options tab in the ribbon.

Click the Insert Slicer button. Check the box for the field that is in the Filters area with the filter applied to it.

Click to Enlarge A slicer will be added to the worksheet. The items that are selected in the filter drop-down list will also be selected/highlighted in the slicer. These two controls work interchangeably, and we can use both the slicer and the filter drop-down menu to apply filters to the pivot table.

Making A List Of Data Frames

The slicer is a great solution if you only have a few items in the filter list. If you have dozens or hundreds of items in the filter list, then the user is required to scroll horizontally through the slicer to see the selected items. So, it's not the best solution for long filter lists. Solution #2 – Add a Connected Pivot Table We can list out all of the selected filter items in cells on the worksheet with another pivot table. Here is a quick guide of the steps to create the connected pivot table. Please watch the video above for further instructions. It's important to note that we still need the slicer created in Solution #1 for this to work.

Select the entire pivot table. Copy and paste it to a blank area in the worksheet.

In the new pivot table, move the field in the Filters area to the Rows area. Remove all other fields in the pivot table so there is only one field in the Rows area. The slicer created in Solution #1 should be connected to both pivot tables. If not, right-click the slicer Report/Pivot Table Connections, and check the boxes for both pivot tables on this sheet. Click to Enlarge This new pivot table will display a list of the items that are filtered for in the first pivot table.

As filters are applied to the Filters area of the first pivot table, the second pivot table automatically updates to display the filter items. This happens because both pivot tables are connected by the slicer. Pretty cool stuff!

🙂 This solution allows us to create formulas based on the list of applied filter items in the pivot table. We can use this in all types of scenarios for creating interactive reports, dashboards and financial models. The possibilities are endless. Solution #3 is an example of how to use the results in a formula. Solution #3 – Create a Comma Separated List of Filter Items The list of filter items can also be joined into one list of comma separated values in one cell. This is nice if you want to display the list right next to the pivot table. We can easily create this list with the new TEXTJOIN function that was introduced in Excel 2016.

If you don't have Excel 2016 or Office 365 yet, then you can also do this with the CONCATENATE function. It's just more work to setup. Again, for this to work we will need to implement solutions #1 and #2 first.

Here are the steps. Checkout the video above for more details.

Type =TEXTJOIN( in the cell where you want to display the list. TEXTJOIN has 3 arguments. The first argument is the delimiter or separator between each cell value.

We can put just about anything we want in here. We just have to wrap the delimiter in quotation marks. To separate the values with commas, put a comma followed by a space in the argument: “, ” Then type a comma. The 2nd argument is the ignoreempty option. This allows us to ignore empty cells and requires a TRUE/FALSE value.

We will select TRUE to ignore any empty cells. That means empty cells will not be added to our list. The 3rd argument is the text. For this argument we can reference a range of cells. In this case we will reference the entire column of the second pivot table in Solution #2. Since the TEXTJOIN function is going to ignore empty cells, we can reference the entire column. The filter list will grow/shrink depending on how many filter items are selected.

This makes the output of TEXTJOIN dynamic, without having to create a dynamic named range. Close the parenthesis on the formula and hit Enter to see the results. The list will also contain the header label of the Rows area of the pivot table. We can remove this by turning off the Field Headers.

This is a toggle button on the Analyze/Options tab of the ribbon in the Show section. There are a lot of options with this solution. We can change the delimiter to a different character besides a comma. We can even use the line break character CHAR(10) to list each item on a new line in the same cell.

Just apply Wrap Text to the cell. Another option for the delimiter is the pipe character. ” ” What if I don't have TEXTJOIN? If you aren't using Excel 2016 or Office 365 yet, then you can create this formula with the CONCATENATE function.

It is just more work to setup. However, I have a for you, including the delimiter character. Multiple Ways to List Multiple Items Well, there are 3 ways to list and display the filter items on the worksheet. The magic here is in the slicer that allows us to create connections between pivot tables.

Checkout my article on for a detail explanation on this relationship. I also have a. You can share this with your co-workers and users that are not familiar with using slicers. I also have a that is part of my.

My explains more about creating interactive reports with slicers and charts. Please leave a comment below with any questions. I'm interested to hear how you will implement these techniques in your Excel files. Charles Henniker - August 2, 2018 Hi Jon. You are getting much closer to what I need to do but it’s not there yet. I have a pivot table with approx 200 customers and 1000 SKU Item# I regularly need to look at a list of let’s say 10 – 30 SKU and see who bought them, but this list varies.

For example, I might have 20 different types of widget (so 20 different SKU). I can generate that list easily from a different Excel sheet using Sort or Heading Filters. At the moment I have to go into the filter and check the 20 different boxes one by one.

So, I guess, what I am looking to do is take (copy/paste) my list and drop it somewhere so that the Pivot Table filters using that list. I hope that makes sense. Mark Hallam - September 22, 2017 Hi Jon, Some great tips here, I wonder if you can help on an issue I have with pivot tables? I have one set of data, and would like to filter down so that each filter’s results are ‘affected/refreshed’ by the preceding filter choice? However, I am finding that the data under each filter’s drop box is showing the full data list, not the filtered selection? I have tried Option 2 above but this does not solve my problem?

Filter A List Of Data Excel

Any help gratefully received! Many thanks Mark. Christopher Thompson - February 27, 2017 Hi Jon. Thanks for such a helpful tutorial!

I would like your input on an issue that may be related to this tutorial (or it might not be, I’m not really sure). For example, I would like to make a data placemat/dashboard that contains a mixture of data about different schools. I would like to target specific data about one school (e.g. # of graduates) for some areas of the placemat, but in other parts of the placemat I would like to compare this school’s data with other schools of my choosing (filter).

For example I would like to report the total number of graduates just for the school of interest, but would like to compare the graduation rate of this school to other schools in the region. Out of the total list of schools (which could be over 40) I would like to be able to tease out a few schools to compare the school of interest with on the placemat, but still have that particular school’s data highlighted on other areas on the placemat. I’m thinking having a list of the different filtered schools might be necessary to do this, but how would you go about doing this? Optimally the target school’s data would be highlighted in blue on the comparison graphs, with the other filtered school’s data in grey so it sticks out. February 28, 2017 Hi Dan, Great question! You could apply the filter in the Rows area of the Connected Pivot table for this case.

The Rows area filters allow us to apply Label Filters for criteria like (Begins With, End With, Contains, etc.). This filter criteria will be reapplied after new data is added and the pivot table is refreshed. If users are filtering in the Filters area, you could probably figure out a way to hide the filters area row, and have them apply filters in the Row label filter drop-down menu of the connected pivot table instead.

I hope that makes sense.

This Tutorial Covers:. Types of Filters in a Pivot Table Here is a demo of the types of filters available in a Pivot Table. Let’s look at these filters one by one:. Report Filter: This filter allows you to drill down into a subset of the overall dataset. For example, if you have retail sales data, you can analyze data for each region by selecting one or more than regions (yes, it allows multiple selections as well). You create this filter by dragging and dropping the Pivot Table field into the Filters area.

Row/Column Label Filter: These filters allow you to filter relevant data based on the field items (such as filter specific item or item that contains a specific text) or the values (such as filter top 10 items by value or items with a value greater than/less than a specified value). Search Box: You can access this filter within the row/column label filter and this allows you to quickly filter based on the text you enter. For example, if you want data for Costco only, just type Costco here and it will filter that for you. Check Boxes: These allow you to select specific items from a list. For example, if you want to hand pick retailers to analyze, you can do this here.

Alternatively, you can also selectively exclude some retailers by unchecking it. Note, there are two more filtering tools available to a user: and Timelines (which are not covered in this tutorial). Let’s see some practical examples of how to use these to filter data in a Pivot Table.

Examples of Using Filters in Pivot Table The following examples are covered in this section:. Filter Top 10 Items by Value/Percent/Sum. Filter Items based on Value. Filter Using Label Filter. Filter Using Search Box. Filter Top 10 Items in a Pivot Table You can use the top 10 filter option in a Pivot Table to:. Filter top/bottom items by value.

Filter top/bottom items that make up a Specified Percent of the Values. Filter top/bottom Items that make up a Specified Value.

Suppose you have a Pivot Table as shown below: Let’s see how to use the Top 10 filter with this data set. Filter Top/Bottom Items by Value You can use the Top 10 filter to get a list of top 10 retailers based on the sales value.

Here are the steps to do this:. Go to Row Label filter – Value Filters – Top 10. In the Top 10 Filter dialog box, there are four options that you need to specify:. Top/Bottom: In this case since we are looking for top 10 retailers, select Top.

The Number of items you want to filter. In this case since we want to get top 10 items, this would be 10. The third field is a drop down with three options: Items, Percent, and Sum. In this case, since we want the top 10 retailers, select Items. The last field lists all the different values listed in the value area. In this case, since we only have the sum of sales, it shows ‘Sum of Sales’ only. This will give you a filtered list of 10 retailers based on their sales value.

You can use the same process to get the bottom 10 (or any other number) items by value. Filter Top/Bottom Items that make up a Specified Percent of the Value You can use the top 10 filter to get a list of top 10 percent (or any other number, say 20 percent, 50 percent, etc.) of items by value. Let’s say you want to get the list of retailers that make up 25% of the total sales. Here are the steps to do this:. Go to Row Label filter – Value Filters – Top 10. In the Top 10 Filter dialog box, there are four options that you need to specify:.

Top/Bottom: In this case since we are looking for top retailers that make 25% of the total sales, select Top. In the second field, you need to specify the percent of sales that the top retailers should account for. In this case, since we want to get the top retailers that make 25% of the sales, this would be 25.

In the third field, select Percent. The last field lists all the different values listed in the value area.

In this case, since we only have the sum of sales, it shows ‘Sum of Sales’ only. This will give you a filtered list of retailers that make up 25% of the total sales. You can use the same process to get the retailers that make up the bottom 25% (or any other percentage) of the total sales.

Filter Top/Bottom Items that make up a Specified Value Let’s say you want to find out the top retailers that account for 20 million in sales. You can do this using the Top 10 filter in the Pivot Table.

To do this:. Go to Row Label filter – Value Filters – Top 10.

In the Top 10 Filter dialog box, there are four options that you need to specify:. Top/Bottom: In this case since we are looking for top retailers that make 20 million in total sales, select Top. In the second field, you need to specify a value that the top retailers should account for. In this case, since we want to get the top retailers that makeup 20 million in sales, this would be 20000000.

In the third field, select Sum. The last field lists all the different values listed in the value area. In this case, since we only have the sum of sales, it shows ‘Sum of Sales’ only. This will give you a filtered list of top retailers that make up 20 Million of the total sales.

Filter Items based on Value You can filter items based on the values in the columns in the values area. Suppose you have a pivot table created using retail sales data as shown below: You can filter this list based on the sales value. For example, suppose you want to get a list of all the retailers that have sales more than 3 million. Here are the steps to do this:.

Go to Row Label filter – Value Filters – Greater Than. In the Value Filter dialog box:. Select the values you want to use for filtering. In this case, it is the Sum of Sales (if you have more items in the values area, the drop down would show all of it). Select the condition. Since we want to get all the retailer with sales more than 3 million, select ‘is greater than’. Enter 3000000 in the last field.

This would instantly filter the list and show only those retailers that have sales more than 3 million. Similarly, there are many other conditions that you can use such as equal to, does not equal to, less than, between, etc.

Filter Data Using Label Filters Label filters come in handy when you have a huge list and you want to filter specific items based on its name/text. For example, in the list of retailers, I can quickly filter all the dollar stores by using the condition ‘dollar’ in the name. Here are the steps to do this:.

Go to Row Label filter – Label Filters – Contains. In the label filter dialog box:. ‘Contains’ is selected by default (since we selected contains in the previous step). You can change this here if you want. Enter the text string for which you want to filter the list. In this case, it is ‘dollar’. You can also use along with the text.

Note that these filters are not additive. So if you search for the term ‘Dollar’, it will give you a list of all the stores that have the word ‘dollar’ in it, but if you then again use this filter to get a list using another term, it will filter based on the new term. Similarly, you can use other label filters such as begins with, ends with does not contain, etc.

Filter Data Using Search Box Filtering a list using search box is a lot like the contains option in the label filter. For example, if you have to filter all the retailers that have the name ‘dollar’ in it, simply type dollar in the search box and it will filter the results. Here are the steps:.

Click on the Label filter drop down and then click on the search box to place the cursor in it. Enter the search term, which is ‘dollar’ in this case. You’ll notice that the list gets filtered in the below the search box and you can uncheck any retailer that you want to exclude. This would instantly filter all the retailers that contain the term ‘dollar’. You can use wildcard characters in the search box. For example, if you want to get the name of all the retailers that start with the alphabet T, use the search string as T.

(T followed by an asterisk). Since asterisk represents any number of characters, this means that the name can contain any number of characters after T.

Similarly, if you want to get the list of all the retailers that end with the alphabet T, use the search term as.T (asterisk followed by T). There are a few important things to know about the search bar:. If you filter once with one criterion and then filter again with another, the first criterion is discarded and you get a list of the second criteria.

The filtering is not additive. A benefit of using search box is that you can manually deselect some of the results. For example, if you have a huge list of financial companies and you only want to filter banks, you can search for the term ‘bank’. But in case some companies creep in that are not banks, you can simply uncheck it and keep it out. You can not exclude specific results.

For example, if you want to exclude only the retailers that contain dollar in it, there is no way to do this using the search box. This can, however, be done using the label filter using the ‘does not contain’ condition. You May Also Like the Following Pivot Table Tutorials:.