Insert Excel Table Slicers, and make it easy to filter data. Format the Slicers and change the option settings, to suit your filter requirements
In Excel 2013, and later versions, you can use Slicers to filter the data in a named Excel Table. To learn more about Excel Tables, go to the Create an Excel Table page.
To add a Table Slicer, follow these steps:
To make the Slicers easy to use, create a space for them on the worksheet, where they're always visible. In this screen shot, the top few rows are empty, and Freeze Panes has them locked in place.
The Slicers were moved to that section, and resized to fit. Also, the number of columns for each Slicer was changed.
To move a Slicer
To resize a Slicer
By default, when you add a Slicer, it has 1 column. You can change the number of columns, to best fit the list of items in the Slicer.
Follow these steps to change the number of columns
In a named Excel table, the heading cells have drop down arrows that you can use to sort or filter the data. You can continue to use those arrows, after adding Slicers.
However, Slicers make it quicker and easier to filter the data, and they show which criteria are currently selected. In the screen shot below, the table is filtered to show records from the East region, in 2019, for the Bars category.
To filter an Excel Table
To clear a filter:
When you filter the items with one Slicer, that might affect the visible items in another Slicer.
For example, if you click Snacks in the Category Slicer, only the products from the Snacks category are visible in the filtered Excel table.
The Product Slicer changes, and shows the Snack products at the top of the Slicer. Products from other categories are shown below, with lighter colours, to indicate that they are not available.
NOTE: You can change this behaviour in the Slicer Settings.
There are a few Slicer Settings that you can change, after adding Slicers to an Excel Table.
For Slicers that show dates, you might prefer to see the most recent dates at the top of the list.
To change the sort order for any Slicer, follow these steps:
By default, the Slicer caption shows the heading from the column in the Excel Table. You can change the Slicer captions, to make them shorter, or easier to understand.
In this example, Orderyr will be changed to Year
By default, the items with no visible data in the filtered Excel Table are shown at the bottom of the Slicer list, in light colours.
To change that setting:
Table Slicers: To download the sample file with the table and Slicers for this tutorial, click here: Table Slicers Sample File. The zipped file is in xlsx format, and does NOT contain macros.
Last updated: April 19, 2021 9:32 AM