Home > Data > Tables > Slicers Filter with Excel Table SlicersInsert Excel Table Slicers, and make it easy to filter data. Format the Slicers and change the option settings, to suit your filter requirements |
IntroductionIn 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. Add a Table SlicerTo add a Table Slicer, follow these steps:
|
Arrange the SlicersTo 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. Move a SlicerTo move a Slicer
Resize a SlicerTo resize a Slicer
|
Change Number of ColumnsBy 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
|
Items Not AvailableWhen 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. Change Slicer SettingsThere are a few Slicer Settings that you can change, after adding Slicers to an Excel Table. Sort OrderFor 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:
|
Change Slicer CaptionBy 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
Hide ItemsBy 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:
|
Download the Sample FileTable 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. Related Excel Tutorials
|
Last updated: December 6, 2023 2:36 PM