Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

#### Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

# Learn How to Filter Large Amounts of Data in a Pivot Table

We can use the filters in our PivotTable to retrieve values that we are interested in. For instance, we can retrieve values between a certain number and another. The steps below will walk through the process.

Figure 1- How to Filter Large Amounts of Data in a Pivot Table

## Setting up the Data

• We will use the created pivot table in figure 2 to illustrate how the filter tool works for Pivot Tables.

Figure 2- Setting up the Data

## Value Filters

With the data in our Pivot table, we can use the value filter to check all client expenditure for those lesser than a particular amount. If we want to know the client(s) that spent less than 10,000 at once, we will do the following:

• We will click on the drop-down arrow of Row Labels
• We will click on value filters
• We will select Greater Than or Equal To

Figure 3- Using Value Filters

• We will insert the amount (10000) into the dialog box and click OK

Figure 4- Value Filter (Country) dialog box

Figure 5- Clients Whose Expenditure is Greater than or Equal to 10,000

• When using filters, it is important to clear the filters after using them, else, they will apply to our next selection or use of the filter
• In figure 6, after deselecting France, the previous filter of values Greater than or Equal To 10000 will still be applied to the result
• We must use “Clear filter from Country” above Label filters in figure 6 if we do not want values Greater than or Equal To 10000 to apply

Figure 6- Clear filters

## Top 2 Countries By Sum of Expenditure

• We can use the value filters to find the Top 2 Countries by Sum of Expenditure.
• We will clear all current filters and place our cursor on any country, e.g. Argentina
• We will click on Row Labels, Value filters, and then, Top 10
• We will insert the fields in figure 7 and press OK

Figure 7- Filter by Country based on Sum of Expenditure

Figure 8- Top 2 Countries By Sum of Expenditure

• We must note that if we place our cursor on any client, the result will return as Top 2 Client by Sum of Expenditure

## Label filters

• We can use the Label filters to find values that contain certain text and several other parameters.
• If we want to find countries that begin with F in our Pivot table, with our cursor on any country or on Rows Labels, we will click on Row Labels, Label filters and Begins with

Figure 9- Using Label Filters

• We will enter “F” into the dialog box. This isn’t case sensitive

Figure 10- Label filter Dialog box

• We will click OK

Figure 11- Values that begin with “F”

## Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.