Filter by Values – Between

Pivot Table is a very useful tool for analysing data in Excel. Filter by Value – Between is a feature available in Pivot that is used to isolate specific data ranges. It provides the ability to filter values between a minimum and a maximum. In this article, you will learn how to use the Filter by Value – Between filter in an Excel Pivot table.

Key Takeaways:

• Value Filters let you filter Pivot Table data using numbers.
• The Between filter shows values within a selected range.
• The Not Between filter excludes values in a selected range.
• Value Filters make reports easier to analyze and understand.
• Always refresh the Pivot Table after updating the source data.

 

Filters in Pivot Tables

What Are Value Filters?

Value Filters allow me to filter Pivot Table data based on numerical values rather than labels or text. This means I can display only those data points that meet specific criteria, such as sales greater than $10,000 or quantities less than 50.

There are several types of Value Filters in a Pivot Table:

• Equals
• Does Not Equal
• Greater Than
• Less Than
• Between

The “Between” filter allows you to analyze a subset of data within a specific numerical range.

Benefits of Using the Between Filter

The Between Value Filter helps you focus only on the data that matters by displaying values within a selected range. This makes large Pivot Tables easier to read and analyze. Other benefits include:

• It can identify records within the specified range.
• It allows you to exclude very high or very low values from the dataset.
• You can create a cleaner report with relevant data only.
• It allows easy comparison of data.

 

How to use Filter by Value

Example 1: Filter by Values – Between

STEP 1: Click on the Row Label filter button in the Pivot Table.

STEP 2: Select Value Filters.

You will see that we have a lot of filtering options. Let us try out – Between

STEP 3: Type in between 100000 and 200000. You can see that the Value Filter will be applied to the Sum of SALES.

Click OK

Now we have the filtering applied in a flash! The Sum of SALES values now displays the ones between 100,000 and 200,000.

Example 2: Filter by Values – Not Between

STEP 1: Click on the Row Label filter button in the Pivot Table.

STEP 2: Select Value Filters.

You will see that we have a lot of filtering options. Let us try out – Not Between

STEP 3: Type in between 2000 and 600000. You can see that the Value Filter will be applied to the Sum of SALES.

Click OK

The Sum of SALES values now display the dates where the sum of sales is not between 20,000 and 600,000.

 

Tips & Tricks

• Use Slicers with Value Filters to enable quick visual filtering across categories.

• Label your Pivot Table fields clearly so you can easily recognize what you’re filtering.
• Use conditional formatting after applying Value Filters to highlight top performers or underperformers.

• Copy your filtered Pivot Table to another sheet to create summaries without losing the original context.
• Refresh your Pivot Table after making changes to source data, or filters may show outdated results.

 

FAQs

1. What is a Value Filter in Excel Pivot Tables?

A Value Filter allows you to filter data in a Pivot Table and displays only values that fall within a specified range.

2. How do I use the “Between” option in a Value Filter?

To apply a Between filter in Excel:

• Click on the filter drop-down in your Pivot Table row labels
• Select Value Filters
• Choose Between
• Enter your minimum and maximum values
• Press OK

3. What does Not Between filter do?

The Not Between option is the inverse of Between. It excludes any data that falls within the defined range.

4. What happens to the source data when you apply a value filter in a Pivot Table?

Value Filters only affect the Pivot Table’s view. The source data remains untouched.

5. I have applied the filter, but the result is not updating. Why?

If the result is not updating even after applying the filters, you need to refresh the Pivot.

• Go to the Data tab
• Click on Refresh