on May 23, 2022, 8:17 AM PDT Slicers are a good way to filter data in a Microsoft Excel PivotTable or PivotChart, but they take up a bit of room. By moving the buttons to the chart, you free room for more visuals. 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 ExcelWhen 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 Image: Susan Harkins/TechRepublic. We’ll create a PivotChart on this data.To begin, we need to create the PivotChart as follows:
Figure B Image: Susan Harkins/TechRepublic. Excel opens a blank chart frame.Figure 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 ExcelA 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:
Figure 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:
Figure E 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 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. Also See
Which is not true about slicer?Which is not true about Slicers? Aggregate values do not respond to slicers.
Which option would clear a PivotTable filter on a state field?What action would clear a pivottable filter on a state field? Click the funnel icon to the right of the state filter and then select all and then click OK.
Which of the following functions is the default summary statistic for Pivottables?When you add a numerical field to the pivot table's Values area, Sum will be the default summary function. (Note: If the field contains text or blank cells, Count will be the default.)
Which button removes a filter from a slicer?Clear slicer filter
Click anywhere in the slicer box, and press the Alt + C shortcut. Click the Clear Filter button in the upper right corner.
|