When you add slicers and PivotCharts to a table summarized with a PivotTable, you get a PivotTable report where viewers click buttons to pick only the conditions they want and immediately see the results as a chart. A slicer is a button you click to apply a filter, and a PivotChart shows the data from its connected PivotTable as a chart. PivotTables that share the same data source can also be filtered together with a single slicer, so you can build a report that puts several tables and charts on one screen.
"I've summarized performance by channel in a PivotTable, but what if my manager wants to see just Naver (Korea's largest search engine)?" If you've built a PivotTable before, you've probably had a moment like this. When you open the filter list and toggle checkboxes one by one, it's easy for viewers to lose track of which conditions are applied to the table. My PivotTable basics post covered building a table with rows, columns, values, and filters. In this post, as the next step, I'll go over how to attach slicers and PivotCharts to build a PivotTable report.
1. How is a slicer different from a PivotTable filter?
The slicers help article describes slicers as "buttons you can click to filter tables or PivotTables." Besides fast filtering, it points out that slicers show the current filtering state on screen. In other words, you can tell what's in the table just by looking at the button colors.
It's easy to think of a slicer like the channel buttons on a TV remote. Instead of opening a menu and picking from a list, you press one of the buttons lined up in front of you, the screen changes right away, and you can see at a glance which button is pressed.
Here are the parts of a slicer as the help article describes them.
- Slicer header: Shows the category (field name) of the items in the slicer.
- Selected and unselected filtering buttons: Show which items are included in the filter.
- Clear Filter button: Selects all items to remove the filter.
- Scroll bar and resize controls: Let you scroll when there are many items, and change the slicer's size and position.
You can use them alongside existing filters too. The PivotTable filter help article suggests using slicers for broad criteria and AutoFilter to narrow down in more detail. So how do you add a slicer?
2. How do you add and connect slicers in a PivotTable report?
In Excel for Windows, the steps are simple.
- Click anywhere in the PivotTable.
- On the Insert tab, select Slicer. The Excel for the web help article points to Slicer on the PivotTable Analyze tab instead.
- In the Insert Slicers dialog, check the fields you want to filter by (for example, channel or campaign) and click OK.
You get one slicer for each field you check, and clicking a button automatically applies the filter to the connected PivotTable. To select multiple items, hold down Ctrl on Windows or Command on Mac while you click, and you can change the slicer's color by choosing a style on the Slicer tab.
It really starts to look like a report when you filter several PivotTables together with one slicer. For example, you put a table by channel next to a table by month, and change both at once with a single channel slicer. To do this, select the slicer you want to share, click Report Connections on the Slicer tab, and check the PivotTables you want to filter together. However, the help article notes that slicers can only be connected to PivotTables that share the same data source. So the second PivotTable has to be built from the same source data as the first.
To disconnect, select the PivotTable and uncheck it under Filter Connections on the PivotTable Analyze tab. To delete a slicer, select it and press Delete. If you filter by date a lot, it's also worth knowing about the PivotTable timeline. It's a date-only filter where you pick a unit (years, quarters, months, or days) and drag a bar to set the period, and for PivotTables that use the same data source, one timeline can filter several tables.
3. How do you create a PivotChart?
The Create a PivotChart help article explains that it's hard to see the big picture from raw, unsummarized data, and not everyone can grasp the situation quickly just by looking at numbers in a table. That's exactly why you need a PivotChart.
Here's how to create a chart from an existing PivotTable in Excel for Windows.
- Select a cell in the PivotTable.
- On the Insert tab, select PivotChart.
- Choose the chart type you want and click OK.
You can also select Insert > PivotChart directly from the source table, choose where to place it, and then pick the fields. According to the Overview of PivotTables and PivotCharts, a PivotChart is a graphical representation of the data in its connected PivotTable, and changing the PivotTable's layout or data is immediately reflected in the chart, and vice versa. So when you filter the PivotTable with the slicer you made earlier, the PivotChart changes to the same conditions. The help article's Mac instructions also say that using filters or slicers on the PivotTable filters the chart as well. When you create a PivotChart, a chart-specific filter pane also appears, and you can sort and filter the chart's underlying data from that pane.
4. How is a PivotChart different from a regular chart?
Most chart tasks work the same as with standard charts, but the overview help article lists a few differences separately.
| Comparison | Standard chart | PivotChart |
|---|---|---|
| Data connection | Linked directly to worksheet cells | Based on the data source of its connected PivotTable |
| Switching rows/columns | Switch in the Select Data Source dialog | Not possible from the dialog; switch by moving the PivotTable's row and column labels |
| Chart types | Not mentioned separately in the help article | Can change to any type except scatter (xy), stock, and bubble |
| Formatting after refresh | Applied formatting is kept | Most chart elements, layouts, and styles are kept; trendlines, data labels, and error bars are not |
The last row in particular is worth knowing if you refresh your report every time. If you carefully polished your data labels and they disappear after a refresh, this may be the reason. The help article also notes that you can create a new PivotChart based on a different PivotTable, but you can't create a new PivotChart directly based on another PivotChart.
※ Note: How do Mac, Excel for the web, and refresh differ?
Even within Excel, what you can do varies a bit by platform.
- Excel for Mac: You need to create the PivotTable first, then insert a column, line, pie, or radar chart from the Insert tab. The help article says treemap, statistic, and combo charts don't work with PivotTables yet.
- Excel for the web: You can only create slicers for local PivotTables. Slicers for tables, Data Model PivotTables, or Power BI PivotTables have to be created in Excel for Windows or Mac. For PivotCharts, create the PivotTable first, then add a chart from the Insert Chart menu on the Insert tab.
If the report doesn't change after you edit the source data, check the refresh. According to the Refresh PivotTable data help article, on Windows you can refresh manually with Refresh on the PivotTable Analyze tab or Alt+F5, and choosing Refresh All updates every PivotTable in the workbook at once. If you want it to refresh every time you open the file, check "Refresh data when opening the file" on the Data tab in Options. The help article also describes automatic refresh for PivotTables connected to local workbook data, but it says this is currently available to Microsoft 365 Insider program participants, so not every user can use it.
To sum up, the PivotTable handles the summary, slicers are the buttons viewers use to pick conditions, and the PivotChart turns the results into a picture. Just remember that all three need to be tied to the same data source for one button to move the tables and charts together. I'm planning to practice organizing my channel reports in this structure too. Menu locations may differ slightly by version, so please check each official help article.
FAQ
- What's the benefit of adding slicers to a PivotTable report?
- According to Microsoft's help article, slicers are buttons you click to filter tables or PivotTables. Along with fast filtering, they show the current filtering state, so it's easy to see what data is being displayed. For PivotTables that use the same data source, one slicer can filter several tables together.
- How do I make a PivotTable chart (PivotChart)?
- In Excel for Windows, select a cell in the PivotTable, choose PivotChart on the Insert tab, pick a chart type, and click OK. In Excel for Mac and the web, create the PivotTable first and then insert a chart from the Insert tab.
- I tried to connect a slicer to another PivotTable, but it doesn't show up in the list.
- Slicers can only be connected to PivotTables that share the same data source. Build the second PivotTable from the same source data as the first, then check it under Report Connections on the Slicer tab.
- How do I refresh an Excel PivotTable?
- On Windows, select the PivotTable and click Refresh on the PivotTable Analyze tab, or press Alt+F5. Choosing Refresh All updates every PivotTable in the workbook at once, and you can also set it to refresh automatically when the file opens on the Data tab of PivotTable Options.
Related posts
Posts worth reading when you build a PivotTable report.
- What Is an Excel PivotTable, and When Should You Use One? - How to build your first PivotTable with rows, columns, values, and filters
- VLOOKUP vs XLOOKUP: Which Excel Lookup Function to Use - How to look up and pull in values from another sheet before building a PivotTable
- How to Fix Broken Korean Text in Excel Data - Fixing garbled Korean characters when opening downloaded data in Excel