How to Filter a Pivot Chart in Excel
Pivot Charts are fantastic for summarizing mountains of data, but their real power is unlocked when you start filtering. Slicing and dicing your data visually allows you to isolate specific information, discover trends within segments, and present clear, focused insights to your team. This guide will walk you through several effective methods for filtering Pivot Charts in Excel, from simple on-chart buttons to powerful interactive slicers.
What Exactly is a Pivot Chart?
Before we filter, let's do a quick refresher. A Pivot Chart is a visual representation of a Pivot Table. While a Pivot Table organizes and summarizes data in a spreadsheet format, a Pivot Chart takes that summary and turns it into a bar chart, line graph, pie chart, or another visual format. The best part? They are intrinsically linked. When you filter, group, or restructure your Pivot Table, the Pivot Chart updates automatically, and vice versa.
Filtering is what moves you from having a static summary to an interactive and analytical dashboard. It allows you to ask and answer specific questions from your data, such as:
"How did our North region perform compared to the South?"
"Which product categories brought in the most revenue last quarter?"
"What are the sales trends for our online channel versus our in-store channel?"
Without filtering, your chart shows everything at once, which can be noisy and overwhelming. With filtering, you can zero in on the exact story your data is trying to tell.
First, Let's Create a Basic Pivot Chart
You can't filter a chart that doesn't exist. If you're new to Pivot Charts, here is a quick setup guide. We'll use a standard sales dataset with columns for Order Date, Region, Product, and Sales Amount.
Select Your Data: Click anywhere inside your data table. Excel is smart enough to detect the entire range automatically, as long as there are no completely blank rows or columns.
Insert the Chart: Navigate to the Insert tab on the ribbon and click PivotChart. A pop-up window will appear.
Choose Placement: In the pop-up, confirm the data range is correct and choose where you want to place the chart - either on a "New Worksheet" (recommended) or the "Existing Worksheet". Click OK.
Build the Chart: You'll now see a blank Pivot Chart area and the PivotChart Fields pane on the right. Drag and drop your fields into the different areas. For our example, let's do this:
Drag Region to the Axis (Categories) area.
Drag Product to the Legend (Series) area.
Drag Sales Amount to the Values area.
You should now have a basic clustered-column chart showing total sales for each product, broken down by region. Now, let's get to the fun part: filtering.
Method 1: Use the On-Chart Filter Buttons
The most direct way to filter a Pivot Chart is by using the interactive buttons located directly on the chart itself. These are typically gray buttons with small dropdown arrows at the bottom-left of your chart for Axis, Legend, and sometimes other fields.
Step-by-Step Instructions:
Locate the Button: Find the field button you wish to filter. In our example, let's filter by Region. Click the button labeled "Region" (or whatever field is on your axis).
Select Your Items: A dropdown menu will appear with a list of all items in that field (e.g., "North", "South", "East", "West"). By default, all are selected.
Apply the Filter: To show only certain regions, uncheck the Select All box at the top, and then individually tick the boxes for the regions you want to see, for instance, "East" and "West".
Confirm Your Selection: Click OK. Your Pivot Chart will instantly refresh to display data only for the East and West regions.
This method is perfect for quick, ad-hoc analysis. Notice the little filter icon that appears on the "Region" button after you apply a filter - it's a handy visual cue that your view is currently narrowed down.
Method 2: Filter Using the PivotChart Fields Pane
Sometimes you might prefer to manage all your fields and filters from a single control panel. The PivotChart Fields pane, which you used to build the chart, is a great place to do this. This is also where you can find more advanced filtering options like "Value Filters" and "Label Filters".
Step-by-Step Instructions:
Open the Pane: If the PivotChart Fields pane isn't visible, click anywhere on your chart. Then, go to the PivotChart Analyze tab on the ribbon and click the Field List button.
Find the Field: In the pane, locate the field you want to filter in one of the four areas at the bottom. For example, find Product under the Legend (Series) area.
Click the Dropdown: Hover over the field name and click the small dropdown arrow that appears.
Apply Your Filter: You'll see the same checklist filtering option as before. You can uncheck items to hide them and click OK to apply.
Exploring Advanced Filters in the Pane:
This menu also gives you access to more powerful filter types:
Label Filters: This allows you to filter based on text. For example, in our Product field, you could choose to show only products whose names "Begin With" the letter 'T' or "Contain" the word 'System'.
Value Filters: This is extremely useful. It lets you filter one field based on the values in another. For instance, you could filter your Region field to show only the "Top 5" regions by Sum of Sales Amount.
The Fields Pane provides a more comprehensive level of control than the simple checkbox filters on the chart surface.
Method 3: Add a Dedicated "Page" Filter
What if you want to filter by a dimension that isn't already part of your chart's structure (i.e., not an Axis or Legend entry)? For this, you use the dedicated Filters area.
Let's say our dataset also includes a Sales Rep column. We want to see our Region/Product sales chart, but be able to filter it for a specific sales rep.
Step-by-Step Instructions:
Navigate to the PivotChart Fields pane.
Find the Sales Repp field in the list at the top.
Click and drag the Sales Rep field into the Filters area at the bottom.
Look at your worksheet. A new filter dropdown button labeled "Sales Rep" has appeared just above your Pivot Chart. Clicking this button lets you select a specific rep, and the entire chart will update to show only the sales data associated with that person. This technique is excellent for letting viewers of your dashboard slice the entire dataset by a high-level category.
Method 4: Supercharge Your Filters with Slicers
Slicers are by far the most user-friendly and visually appealing way to filter Pivot Charts. They are essentially large, interactive buttons that you can click to filter your data. They make dashboards intuitive and are perfect for presentations or for colleagues who may not be Excel wizards.
How to Add a Slicer:
Select Your Chart: Click on your Pivot Chart to activate the PivotChart Analyze contextual tab.
Insert Slicer: In the PivotChart Analyze tab, click on Insert Slicer.
Choose Your Fields: A new dialog box will pop up, listing all the available fields from your dataset. Check the box for each field you want to create a slicer for. Let's pick Region and Product. Click OK.
Two new slicer objects will now appear on your worksheet. They are floating windows that you can move and resize to fit your dashboard layout.
Using the Slicer:
Single Selection: Simply click a button in a slicer (e.g., click "North" in the Region slicer) to filter the chart.
Multiple Selections: Hold down the Ctrl key while clicking to select multiple non-adjacent items (e.g., "North" and "West").
Clear a Filter: To restore the full view, click the "Clear Filter" icon (a funnel with a red 'x') at the top-right corner of the slicer object.
Method 5: Filter by Date with Timelines
If your dataset contains a time-based column, a Timeline is the specialized, super-powered version of a Slicer. It's an interactive calendar control that lets you filter data by days, months, quarters, or years with a beautiful sliding interface.
Requirement: Your data must have a column with properly formatted dates for this to work.
How to Add a Timeline:
Select Your Chart: Click on your Pivot Chart.
Insert Timeline: Go to the PivotChart Analyze tab and click Insert Timeline.
Select Your Date Field: Excel will show you a list of available date-based fields. Check the box for your Order Date column and click OK.
A new Timeline object appears. You can use the control in the top-right to switch between filtering by Months, Quarters, or Years. To select a period, just click on it. To select a range, click the start period and drag the handles to cover the end period. It is an incredibly intuitive way to analyze performance over time.
Final Thoughts
Mastering these various filtering methods in Excel transforms you from someone who just builds charts into someone who performs actual data analysis. By combining on-chart filters, the Fields Pane, Slicers, and Timelines, you can construct fully interactive dashboards that pinpoint specific insights, uncover trends, and answer complex business questions with just a few clicks.
While mastering Excel is an excellent skill, connecting data from all your different sales and marketing platforms can be a manual, time-consuming process. Before you even get to filter a chart, you're busy exporting CSVs from Shopify, Google Analytics, and your advertising platforms. That’s why we created Graphed. We connect directly to all your data sources, allowing you to ask questions in plain English like, "show me a dashboard comparing Facebook Ads spend vs Shopify revenue by campaign for last month." We build the interactive, real-time reports instantly, so you can skip the setup and get straight to the insights.