pivotchart-buttons-tutorial
Image: PixieMe/Adobe Stock

PivotCharts and slicers go together like peanut butter and jelly — the slicer complementing the chart by allowing the viewer to filter. However, both objects take up a lot of room, which can be a problem especially if you plan to move the chart and slicer to a dashboard.

Fortunately, the solution is easy. In this tutorial, I’ll show you how to reduce a column of slicer buttons to a vertical row of buttons. Then, you can easily move those buttons onto the chart. By doing so, you have the functionality of both objects in the space of only one.

I’m using Microsoft 365 on a Windows 10 64-bit system. The slicer object is available for 365 subscribers, Excel on the web, and versions of Microsoft Excel down to 2010. You may download the demonstration file for your convenience.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

How to add the PivotChart in Excel

When PivotTable and PivotChart objects were new, you had to base a PivotChart on a PivotTable. Now, Excel does that setup for you. That means you can create a PivotChart without first creating a PivotTable.

Now let’s suppose we want a PivotChart that displays the data in Figure A. The data is in a Table object named Commission. As you can see, this data tracks sales and commissions for six employees. We want to turn this data into a PivotChart that lets you filter the chart by employee: A slicer will do so nicely. However, we don’t want two different objects — the chart and the slicer. We’d rather the slicer filtering functionality be a part of the chart.

Figure A

ExcelSlicerPC_A
Image: Susan Harkins/TechRepublic. We’ll create a PivotChart on this data.

To begin, we need to create the PivotChart as follows:

  1. Click the Insert tab.
  2. In the Charts group, click PivotChart; don’t change any of the default settings. Excel will open the empty chart frame in a new sheet (Figure B). If the PivotChart Fields list doesn’t open, click the empty frame.
  3. In the PivotChart Fields pane, drag Commission to the Values list.
  4. Drag Personnel to the Axis (Categories) list.
  5. Then, right-click Personnel in the top pane and choose Add As Slicer (Figure C).

Figure B

ExcelSlicerPT_B
Image: Susan Harkins/TechRepublic. Excel opens a blank chart frame.

Figure C

ExcelSlicerPC_C
Image: Susan Harkins/TechRepublic. Add the Personnel column to the Axis (Categories) list and as a slicer.

At this point, we have a PivotTable, which Excel created for you, a PivotChart and a Slicer. You could position the two and call it quits at this point, but we want the slicer functionality to be on the chart, not a separate object.

How to add slicer buttons to the chart in Excel

A slicer is an interactive object that displays buttons that you click to filter data in tables, PivotTables and PivotCharts. By default, a slicer displays a lot of information: A filtering button for each unique value in the category, a Clear Filter button and a scroll bar that enables scrolling when items aren’t visible in the slicer.

If you think moving such a busy object onto a chart is going to create a mess, don’t worry. You can reformat the slicer to show only the buttons in a vertical row. First, let’s clean up the chart a bit to make room for the slicer buttons:

  • Enter a new title: October Commissions. Move it to the left margin.
  • Delete the Legend.
  • Hide the Sum of Commission and Personnel buttons as follows: In the PivotChart Fields list, select Sum of Commission in the Values list and choose Hide All Field Buttons on Chart (Figure D).

Figure D

ExcelSlicerPC_D
Image: Susan Harkins/TechRepublic. Hide the field buttons.

Now let’s convert the slicer object into a series of buttons that we can move onto the chart as follows:

  1. Select the slicer.
  2. In the buttons group, change the Columns setting from the default 1 to 6 — there are six buttons (six employees).
  3. Increase the width until you can see the names on all six buttons.
  4. Click Slicer Settings in the Slicer group.
  5. In the resulting dialog, uncheck the Display Header in the Header section. Now the slicer is one simple row of six buttons (Figure E).

Figure E

ExcelSlicerPC_E
Image: Susan Harkins/TechRepublic. Change the slicer from one column to six and remove the header.

The chart isn’t quite big enough for the row of six slicer buttons. You can increase the size of the chart a bit, but the buttons will still obscure the title.

In this situation, you can move the title out of the chart and into a cell above the chart. You can’t move the title; you must delete the title in the chart and then enter the text above the chart. You then have room for the buttons, as shown in Figure F.

Figure F

ExcelSlicerPC_F
Image: Susan Harkins/TechRepublic. Move the title to make room for the buttons.

Simply click the buttons to filter the connected PivotChart. Clicking a button will remove the column for that person. To use multiple buttons in one filter, hold down the Ctrl key while clicking buttons.

I’ve left the chart in the same sheet with the PivotTable. Most likely, you’ll want to move it to a dashboard.

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays