Slicers in Excel make tables appear interactive and can help you visualize the same data with different criteria. The pivot table summarizes the entire table in a report, and slicers help you to visualize slices of the whole table.
Slicers help analysts to view data in the way they like. They present crisp data of every column you added to the main table. A prerequisite is that you need to have a table before you can work with a slicer. This feature is available in all versions of Excel from Excel 2010 onwards.
But what exactly is a slicer in Excel? And how can you use this tool to improve your data visuals? In this article, we will explain and show you step by step, so you can become a pro.
What is a Slicer in Excel?
Slicers apply filters for tables, pivot tables, and pivot charts. They are widely used for creating dashboards that display the summary report of the table. You can move your mouse across the table and check a particular column in the report.
Slicers indicate the current filtering state, making it easier to understand what is currently displayed. You can connect multiple slicers to multiple pivot tables to create reports.
How to Insert a Slicer into a Table
To get started, you need a table to work with a slicer, preferably a pivot table. Start with some sales data in the worksheet. The following sample worksheet includes sales data about the products, branches, salespeople’s names, and the total sales in $. This is the sample table that will be used throughout this article to explain Excel slicers.
The above example is a screenshot of the pivot table that summarizes the sales table. After creating a pivot table, you find a new tab PivotTable Analyze. Click on that to get various options. In the toolbar, click on Filter group, then click on Insert Slicer and you get a dialogue box asking you to Insert Slicers. The column headers are given options; you must choose one or more options to get the pivot table slicers.
In the example above, I have selected the Product column in the Insert Slicer dialogue box. All the products listed in the column are listed here. Slicers provide a dashboard where you can browse through all the products listed. When you click on a specific product, it displays the salesperson who sold it and the total sales they have done.
The slicer is like a pivot table but displays the sliced data alone rather than the entire summary report given by the pivot table.
How Do You Use Data Slicers in Excel?
Add a slicer in Excel to create slices to sort the required data from a large repository.
Step 1: Creating a Table
First, create a table by clicking on any of the cells in the worksheet that contains the data. Click on Insert → Table; you should see a Create Table dialogue box where you can enter the range of your table. You can also use the keyboard shortcut Ctrl+T to create a table.
Check the box that prompts you with the message My table has headers. When you check this box, the table is created with headers that have filters.
Step 2: Creating a Pivot Table
Click on Insert → PivotTable. You get the dialogue box PivotTable from the table or range where you need to mention your table’s cell range, then choose where you wish your pivot table to be. This can be in a new worksheet or an existing worksheet.
Step 3: Creating a Slicer
Now, you have the table and the pivot table with the summary of the table. There are two methods by which you can insert a pivot table slicer.
In the first method, click on any of the cells in the table and see a new tab TableDesign. Then click on TableDesign → Insert Slicer. When you click on that, it opens a dialogue box that prompts you to select the items you wish to insert into the slicer.
The second method to insert a slicer is to click on any cell in the pivot table, and you find two new tabs in the menu bar: PivotTable Analyze and Design. Click on PivotTable Analyze → Insert Slicer and select the items you wish to display in the sliced report.
More with Slicers
The screenshot below shows the table, pivot table, and the slicer. A slicer is created that lists all the products in the sales table. Since I chose Product in the Insert Slicer dialogue box, the name of the slicer is Product. As you click each product, the corresponding data is displayed in the summary report.
For instance, if I choose the product Cables, the summary report displays the salesperson’s name and the total sales of that product. It is a similar step for each product.
Now, click on the slicer, and you see a new tab in the menu bar: Slicer. When you click on that option, it unfolds and reveals the following groups:
- Slicer
- Slicer Styles
- Arrange
- Button
- Sizes
In the Slicer group, you can change the Slicer Caption. In Slicer Settings, you can mention how you wish your column names to appear (in ascending or descending order). You can set the style in Slicer Styles or design a new style for the slicer.
If you don’t want the slicer to be displayed, click on the filter button on the top right corner of the slicer. The whole summary report is displayed instead of a sliced report.
FAQs
1. What is an interactive dashboard?
An interactive dashboard is a data visualization tool to analyze and display metrics of various sorts. An interactive dashboard features tables, charts, maps, timeline charts, and other components to help users understand the meaning of the data. There is also a static dashboard that offers surface-level details leaving the users to resort to other options if they wish to see alternative outcomes based on various criteria or ask follow-up questions.
2. What’s the difference between a slicer and a pivot table?
| Pivot table filters | Slicers |
|---|---|
| Pivot table filters are a bit clumsy as there are too many options in terms of labels and value filters. You need to choose criteria based on how the filtered table will appear. | With slicers, you just need to click on any one of the options, and the data that meet that criteria alone will appear sliced from the main table. |
| Filter options can be applied to the data in that table alone. | Slicers can be tied to multiple pivot tables and pivot charts. |
| Filters are locked to a particular column and row. | Slicers are dynamic objects and can be moved anywhere. For example, you can put a slicer next to the pivot chart and update the contents in real-time. |
3. Can you make a slicer without having a pivot table?
Yes, you can insert a slicer without a pivot table, however you must have a table to insert a slicer. Let’s look at the steps to insert a slicer without a pivot table.
Step 1: Select any data in the table and click Insert → Table, or you can insert a table using a keyboard shortcut by pressing Ctrl+T.
Step 2: Select the option My table has headers and press OK.
Step 3: You get a new tab: Table Design. Now click Table Design → Insert Slicer.
Step 4: It shows the headers of your table. Choose the field you wish to get in slices, and then press OK.
Step 5: On the top of the slicer, you can see the Filter icon. When you click on any data in the slicer, you get the sliced column filtered according to the value you clicked.
Step 6: If you want to close the slicer, you can click on the Clear Filter icon.
4. What is slicer multi select?
You can use the slicer multi select button to filter multiple criteria at the same time. Once you have your slicers set up, simply click the multi select button. (This is the button with three check marks at the top of the dashboard; See gif above.) Then you are able to filter multiple criteria.
Closing Thoughts
Slicers in Excel come along with pivot tables to filter out the required information from the table. A pivot table provides the complete summary of the table. Use a slicer when you wish to project a particular column of a table to review, compare, and analyze that data alone.
You can format the slicers and make changes in the slicer settings. Keep checking our blogs to know more about creating pivot tables, slicers, lookup functions, and much more in Excel. Check our courses in Excel and Microsoft Office applications to learn how to use slicers effectively. You will earn Micro-credentials on completion of the course.
