Methods to convert a Microsoft Excel slicer right into a collection of filtering buttons on a PivotChart in Excel

Slicers are a great way to filter information in a Microsoft Excel PivotTable or PivotChart, however they take up a little bit of room. By transferring the buttons to the chart, you free room for extra visuals.

Picture: PixieMe/Adobe Inventory

PivotCharts and slicers go collectively like peanut butter and jelly — the slicer complementing the chart by permitting the viewer to filter. Nevertheless, each objects take up quite a lot of room, which could be a downside particularly in case you plan to maneuver the chart and slicer to a dashboard.

Fortuitously, the answer is simple. On this tutorial, I’ll present you easy methods to cut back a column of slicer buttons to a vertical row of buttons. Then, you’ll be able to simply transfer these buttons onto the chart. By doing so, you will have the performance of each objects within the area of just one.

I’m utilizing Microsoft 365 on a Home windows 10 64-bit system. The slicer object is out there for 365 subscribers, Excel on the net, and variations of Microsoft Excel right down to 2010.

SEE: Home windows, Linux, and Mac instructions everybody must know (free PDF) (TechRepublic)

Methods to add the PivotChart in Excel

When PivotTable and PivotChart objects have been new, you needed to base a PivotChart on a PivotTable. Now, Excel does that setup for you. Which means you’ll be able to create a PivotChart with out first making a PivotTable.

Now let’s suppose we would like a PivotChart that shows the info in Determine A. The information is in a Desk object named Fee. As you’ll be able to see, this information tracks gross sales and commissions for six workers. We wish to flip this information right into a PivotChart that permits you to filter the chart by worker: A slicer will accomplish that properly. Nevertheless, we don’t need two totally different objects — the chart and the slicer. We’d relatively the slicer filtering performance be part of the chart.

Determine A

Picture: Susan Harkins/TechRepublic. We’ll create a PivotChart on this information.

To start, we have to create the PivotChart as follows:

  1. Click on the Insert tab.
  2. Within the Charts group, click on PivotChart; don’t change any of the default settings. Excel will open the empty chart body in a brand new sheet (Determine B). If the PivotChart Fields checklist doesn’t open, click on the empty body.
  3. Within the PivotChart Fields pane, drag Fee to the Values checklist.
  4. Drag Personnel to the Axis (Classes) checklist.
  5. Then, right-click Personnel within the prime pane and select Add As Slicer (Determine C).

Determine B

Picture: Susan Harkins/TechRepublic. Excel opens a clean chart body.

Determine C

Picture: Susan Harkins/TechRepublic. Add the Personnel column to the Axis (Classes) checklist and as a slicer.

At this level, we’ve got a PivotTable, which Excel created for you, a PivotChart and a Slicer. You could possibly place the 2 and name it quits at this level, however we would like the slicer performance to be on the chart, not a separate object.

Methods to add slicer buttons to the chart in Excel

A slicer is an interactive object that shows buttons that you simply click on to filter information in tables, PivotTables and PivotCharts. By default, a slicer shows quite a lot of info: A filtering button for every distinctive worth within the class, a Clear Filter button and a scroll bar that permits scrolling when objects aren’t seen within the slicer.

Should you suppose transferring such a busy object onto a chart goes to create a large number, don’t fear. You possibly can reformat the slicer to indicate solely the buttons in a vertical row. First, let’s clear up the chart a bit to make room for the slicer buttons:

  • Enter a brand new title: October Commissions. Transfer it to the left margin.
  • Delete the Legend.
  • Conceal the Sum of Fee and Personnel buttons as follows: Within the PivotChart Fields checklist, choose Sum of Fee within the Values checklist and select Conceal All Subject Buttons on Chart (Determine D).

Determine D

Picture: Susan Harkins/TechRepublic. Conceal the sphere buttons.

Now let’s convert the slicer object right into a collection of buttons that we are able to transfer onto the chart as follows:

  1. Choose the slicer.
  2. Within the buttons group, change the Columns setting from the default 1 to six — there are six buttons (six workers).
  3. Enhance the width till you’ll be able to see the names on all six buttons.
  4. Click on Slicer Settings within the Slicer group.
  5. Within the ensuing dialog, uncheck the Show Header within the Header part. Now the slicer is one easy row of six buttons (Determine E).

Determine E

Picture: Susan Harkins/TechRepublic. Change the slicer from one column to 6 and take away the header.

The chart isn’t fairly large enough for the row of six slicer buttons. You possibly can improve the dimensions of the chart a bit, however the buttons will nonetheless obscure the title.

On this scenario, you’ll be able to transfer the title out of the chart and right into a cell above the chart. You possibly can’t transfer the title; you could delete the title within the chart after which enter the textual content above the chart. You then have room for the buttons, as proven in Determine F.

Determine F

Picture: Susan Harkins/TechRepublic. Transfer the title to make room for the buttons.

Merely click on the buttons to filter the linked PivotChart. Clicking a button will take away the column for that particular person. To make use of a number of buttons in a single filter, maintain down the Ctrl key whereas clicking buttons.

I’ve left the chart in the identical sheet with the PivotTable. Most certainly, you’ll wish to transfer it to a dashboard.