Pivot Tables are great to manipulate large amounts of data and creating reports for various stakeholders. They filter our data.
We can all agree that these representations of data are not very user-friendly. Luckily, we have a solution for this as well. To present our data in a much neater way, we can use visual filters, better known as slicers.
Excel Slicers are graphic representations of tables, pivot tables, and pivot charts. They can be used in all kinds of reports. The earliest version of their appearance is in Excel 2010.
In their essence, there is little difference between pivot table filters and slicers. However, unlike pivot filters, slicers are easier to use, you can connect them to multiple tables and charts, they can be moved, and work better in touch screen environments. On the downside, they cannot be easily automated with VBA code.
Insert Slicer into Excel Table
As in previous examples, we will create a table with NBA players, their clubs, conferences, and their statistics from three nights of basketball.
Our table has 28 rows, but we are only showing 10 for a preview.
To insert the slicer, we need to go to Insert >> Filters >> Slicer
Slicer will not work if we have not created our data as a table. When we select our data and go to Slicer, the following window will appear:
A reason behind this is that Excel does not see a connection between our data. It just views them as random numbers. So, we need to select our data, go to Insert >> Tables >> Table
Our NBA players tables should look like this now:
Now when we go to Slicer, a pop-up window will be different and will include all column names:
For our example, we will only select the Player column, click OK, and then select just a couple of players to be presented in our table:
Only the players that we selected in the Slicer are now visible in the table.
Insert Slicer into Pivot Table
We will remove the filters from our table and then create the Pivot Table from our data in a new sheet.
Then we will insert Players into the Row field, points, and assist into the Values field:
To insert Slicer, we will click anywhere on the Pivot Table, and go to Pivot Table Tools >> Analyze >> Insert Slicer.
We will be presented with the same pop-up window as before, and we will choose the player’s column again.
Once we do, we will have our slicer created.
You will notice a little list icon on the top right corner of the Slicer. This icon enables/disables the selection of multiple items to be presented in the Slicer.
Insert Slicer into Pivot Chart
Slicers in Pivot Charts are also a very good and handy option. We will create a Pivot Chart from our Pivot Table by clicking anywhere on the Pivot Table, and then going to PivotTable Tools >> Analyze >> Pivot Chart.
Again, we will choose a simple chart that looks like this:
Then, we click on our chart, go to PivotChart Tools above the ribbon, click on Analyze and click on Insert Slicer (familiar icon from the previous two examples).
Our chart automatically changes, as well as our original Pivot Table.
As seen, Slicers are a great way to make our Pivot Table and Pivot Table Charts very responsive and to better manage our data.
How to Use and Manage Slicers in Excel
So far, we have only used one slicer to show how they are created. To show how useful they can be, we will create another slicer with conferences.
If we want to select just one of these conferences, we have to unselect multiple select buttons. We can do this by clicking on it or with the combination of ALT + S.
We will select only the Eastern conference in our case.
Since we already have only three players selected (Joel Embiid, Kevin Durant, and Anthony Davis), selecting the Eastern conference will remove one player from our chart (Anthony Davis) who does not belong to this conference.
Our chart now looks like this:
Generally, when clicking anywhere on the Slicer, another tab will be presented above our ribbon- Slicer Tools. This is a place where we manage our slicers (resize them, bring them forward, or send them backward in regard to another object).
For example, to increase the size of fields in Slicer we click on the Player Slicer, go to Slicer Tools >> Options >> Buttons:
We will choose number 3 for our Columns (this means we will have our fields presented in three different columns), and increase the height to 0.5 inches and the width to 1.5 inches. Our Slicer now looks like this:
You will also notice one pretty cool thing: the Western Conference players are greyed out since we selected only the Eastern Conference.