Filter by Values – Between

As someone who relies on Excel for data analysis, I’ve always appreciated the sheer power and flexibility that Pivot Tables offer. Among the many features that have transformed the way I work with data, the “Filter by Values – Between” option has proven to be a game-changer for isolating specific data ranges. Whether I’m analyzing sales numbers, tracking employee performance, or comparing product metrics, the ability to filter for values that fall between a minimum and maximum figure is essential for clear, actionable insights. In this article, I’ll take you through everything you need to know about using filter by values in Excel Pivot Tables.

Key Takeaways:

• Value Filters allow you to filter Pivot Table data based on numeric criteria instead of text labels.
• The “Between” filter helps isolate data within a specific range, like sales between $100,000 and $200,000.
• Filtering by values improves report clarity and removes distracting outliers from your analysis.
• You can also use “Not Between” to exclude certain value ranges, highlighting extremes or anomalies.
• Applying value filters is quick—just a few clicks can transform a chaotic dataset into a focused report.

 

Understanding Filters in Excel Pivot Tables

What Are Value Filters in Pivot Tables?

When I first started using Pivot Tables, I quickly discovered that filters are more than just a way to hide rows—they’re a robust tool for zeroing in on exactly what matters. Value Filters, in particular, 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,” and, of course, “Between.” Each serves a different purpose, but the “Between” filter is especially powerful when I want to analyze a subset of data within a specific numerical range, making it a staple in my data toolkit.

The Importance of the “Between” Filter

The “Between” filter is indispensable for scenarios where I need to focus on a middle range of values—think of it as a funnel that helps me block out outliers or irrelevant data. For example, if I’m reviewing sales performance and want to focus on transactions between $5,000 and $15,000, this filter makes it effortless. It ensures my analysis is targeted and relevant, saving time and reducing the risk of misinterpreting the data.

Using “Between” not only streamlines the data I see but also improves the clarity of my reports. I often use this filter to create executive summaries, financial dashboards, and inventory analyses that demand precision and focus. It’s one of those features that, once you start using, you can’t imagine working without.

 

Practical Applications – 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

Now we have the filtering applied in a flash! The Sum of SALES values now displays the dates where the sum of sales is not between 20,000 and 600,000.

 

Tips & Tricks

• Use Slicers in combination with Value Filters to enable quick visual filtering across categories while still maintaining numeric precision.

• Label your Pivot Table fields clearly (e.g., “Sum of SALES” instead of “Sum of Values”) 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 snapshots or summaries without losing the original context.
• Refresh your Pivot Table after making changes to source data, or filters may show outdated results.

 

Frequently Asked Questions (FAQs)

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

A Value Filter allows you to filter data in a Pivot Table based on numeric values, such as sales figures or quantities. Instead of filtering by name or label, you can set criteria like “greater than,” “less than,” or “between.” This is especially useful when analyzing performance metrics or financial data. It makes your reports more focused and actionable by highlighting only the relevant data points.

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

To apply a “Between” filter, click on the filter drop-down in your Pivot Table row labels, select “Value Filters,” and then choose “Between.” Enter your minimum and maximum values (e.g., 100000 and 200000), and hit OK. Excel will then show only those entries where the selected data field (like Sum of Sales) falls within that range. This is ideal for targeted analysis without manually scanning through rows.

3. Can I use the “Not Between” filter to exclude a data range?

Yes, the “Not Between” option is the inverse of “Between.” It excludes any data that falls within the defined range. For instance, if you want to analyze outlier transactions (e.g., sales below $2000 or above $600000), this is your go-to filter. It helps you spot anomalies, edge cases, or performance gaps that need attention.

4. Do these filters change the source data?

No, Value Filters only affect the Pivot Table’s view, not the underlying dataset. Your source data remains untouched. This is one of the biggest advantages of using Pivot Tables—you can slice and dice your data however you want without worrying about messing up the original spreadsheet. It also allows for dynamic reporting as new data comes in.

5. Are Value Filters available for all Pivot Table fields?

Value Filters only work on numeric value fields (like sums or averages) and not on text or categorical fields. So if you’re trying to filter a field like “Region” or “Employee Name” using “Between,” it won’t work. Make sure you’re applying these filters on numeric fields like “Total Sales,” “Profit,” or “Quantity” to unlock the full power of Value Filters.