How to Create an Excel Slicer? 2 Easy Ways
(Note: This guide on how to create an Excel slicer is suitable for all Excel versions including Office 365)
Excel is usually preferred for its ability to store and access large amounts of information. To improve user-friendliness, Excel has a lot of options and functions available to help the user search, find, or retrieve data. One among them is the Excel Slicer option.
In this guide, I will show you what is Excel slicer, how to create a Slicer in Excel, how they work, and how to customize them.
You’ll Learn:
- What is Excel Slicer?
- Creating a Table for Slicer in Excel
- How Does Excel Slicer work?
- Customizing the Excel Slicer
Related Reads:
How to Group in Pivot Table? ( 2 Easy Methods)
How to Delete a Pivot Table in Excel? 4 Best Methods
How to Sort a Pivot Table in Excel? 6 Best Methods
What is Excel Slicer?
Sorting or filtering data from pivot tables with huge amounts of data can be crucial and time-consuming. In such cases, using the Excel slicer is the best option for quick and easy filtering of the tables.
Excel Slicer works like a filter helping sort out information from PivotTables and Excel tables. They help filter out and point out the required information from the large repository of data and present them in an easy-to-read and appealing format.
Creating a Table for Slicer in Excel
The main requirement to create a slicer in Excel is to have an Excel table or PivotTable. You cannot perform slicing on large data in Excel. Large data are considered databases but not tables.
In this case, we need to convert the database into tables. You can either convert them to a PivotTable or Excel table.
How to Create a Slicer for Pivot Tables
1. Create a Pivot Table
- Select the data you want to convert to a PivotTable.
- Navigate to Insert. Under Tables, click on the PivotTable option. From the dropdown select From Table/Range. This opens a dialog box asking you to specify the detail to create the PivotTable.
- Enter the range of the table or you can select the table by dragging on the cells.
- Choose if you want the PivotTable to be created in a new worksheet or on the same sheet.
- Click the OK button.
- Now, select the elements/heading you want to add to the PivotTable. This creates the PivotTable. The advantage of using a PivotTable is that you can choose the elements and the headers from a table consisting of many headings or elements.
2. Creating Excel Slicer from PivotTable
When you have a PivotTable in Excel, you can create the Excel slicer using the PivotTable Analyze option.
- Under Filter, click on Insert Slicer. This opens up an Insert Slicers dialog box.
- In the dialog box, select the elements to create slices of. You can choose one or more slices.
- Click OK.
This creates the slices. Now, you can sort the required data from a large repository of data easily.
How to Create a Slicer for Excel Tables
1. Create an Excel Table
- You can also create an Excel table from the data by using the keyboard shortcut Ctrl+T. This opens a Create Table dialog box.
- Enter the range of the data, or you can select the cells.
- Check the ‘my table has headers’ checkbox
- Click the OK button.
This creates a table with all the data. Now that we have a table, we can move on to creating an Excel Slicer.
2. Creating Excel Slicer from Excel Table
- To create a slice from an Excel table, go to the Insert menu.
- Under Filters, click on Slicer.
- You can also select the Insert Slicer from the Table Design menu under the Tools section.
- This opens up an Insert Slicers dialog box. Select the elements you want to create slices for.
- Click OK.
This creates the slices for the Excel table where you can sort out data easily. Now that we have created the Excel slicer, let us see how it works.
Also Read:
How to Sort Dates in Excel? 6 Easy Methods
How to Custom Sort Excel Data? 2 Easy Steps
How to Filter in Excel? A Step-by-Step Guide
How Does Excel Slicer work?
The main purpose of the Excel slicer is to filter data from a large existing table and present them in a more aesthetic and presentable way.
Consider the above-mentioned example. The table consists of a list of students along with their name, city, major, subject marks, and their total and average.
It is hard to manually sort out and select data from a large repository. So, you can use Excel slicing to get the data easily.
Suppose, you want to filter out only the students from Buffalo city. What you have to do is:
- Go to the Insert menu and select Slicer.
- Now, select “City” in the Insert Slicers dialog box and click OK. This creates a slice with the data pertaining to the city parameter. This gives you the liberty to select the data from the table pertaining to the information we select.
- Now, for the list of students coming from the city of Buffalo, click on Buffalo from the slice dialog box. This shows you all the entries in the table from the city of Buffalo.
You can also select multiple parameters while slicing elements from the table. For example, if you want to know about the students from two cities: Buffalo and Brunswick. You can use the multi-select option to select two or more parameters from the City slice.
You can clear the parameters using the Clear Filter option to clear all the parameters and show all the elements in the original table.
You can also select multiple slices to get consolidated from the table. For example, if you want to know the students from Buffalo city who majors in Arts. Select the “City” and “Major” parameters, and click OK.
This gives you two slices of the table consisting of data from City and Major. Select Buffalo from the city slice and Arts from the major slice.
This shows you the entries from the city of Buffalo majoring in Arts.
Customizing the Excel Slicer
You can customize the slice parameters using the Slicer Menu in the menu bar.
- Under the Slicer option, you can change the slicers headers and sort the elements in the table. You can also use the Report Connection to connect two PivotTables and slice data in them both.
- Under the Slice Styles option, you can change the styles to make the slices more appealing and with matching color schemes based on your preferences.
- Under the Arrange option, you can align the slice, choose the slice element to display, group, or rotate the slice filter box.
- You can change the height, width, and size of the buttons and of the slice box under the Buttons and Size option.
Suggested Reads:
3 Best Methods to Find Duplicates in Excel
VLOOKUP for Dummies (or newbies)
How to Make a Pie Chart in Excel? A Step by Step Guide
Closing Thoughts
Slicers in Excel are a great way to retrieve important information from a large set of data resulting in lesser manual work and providing maximum efficiency.
In this guide, we have seen how to create an Excel Slicer, how they work, and how to customize them. Use them in the right places to ease your effort and make the data more presentable.
If you need more high-quality Excel guides, please check out our free Excel resources center. Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
