How to Use a Slicer to Filter a Table in Microsoft Excel

by
Spread the love

When you work with spreadsheet data in a standard table or pivot table, you may want to view only particular data. You can use Excel’s built-in filter feature; however, the options can be limiting as well as cumbersome. Instead, take advantage of the Slicer feature tp filter table data in Microsoft Excel.

Content

What Is a Slicer in Microsoft Excel?

In Excel, a Slicer is an easy-to-use tool that allows you to filter data in a table or pivot table. What makes this tool more flexible than the filter feature in Excel is that you can filter data with a single click and select multiple criteria for the filter in fewer steps. Additionally, you can move a Slicer anywhere on your sheet.

Using a Slicer, you also have a clear indicator of the current filter status. With a glance, you can see exactly what you’ve filtered by viewing the Slicer buttons.

Along with the features of the Slicer, you can adjust a few settings. You may want to lock the Slicer so that it can’t be accidentally moved or resize it to perfectly fit your Excel window and view.

The best way to explain how a Slicer works is to walk through how to use a Slicer in Excel.

How to Insert a Slicer in Excel

When you filter data in an Excel table or pivot table using a Slicer, it works the same way with either type of table. Be sure your data is formatted as a table, then follow these steps to insert a Slicer.

  1. Select any spot within your table and head to the “Table Design” tab. Click “Insert Slicer” in the Tools section of the ribbon.

  1. When the Insert Slicers box appears, you’ll see the fields from your table. Check the box next to the field you want to use for the Slicer and click “OK.”

  1. If you want to filter by multiple fields, you can check more than one box and will receive a separate Slicer for each field. Click “OK” to create the Slicers.

The Slicer will appear as a small box, ready to get to work.

How to Use a Slicer in Excel

Your Slicer has a button for each piece of data in the field you selected. For example, we selected the Date field and have buttons for each month.

  1. Select a button to filter by that particular data. Here, we are filtering by the month of January, so we only see the January data in our table, with the remaining table data hidden from view.

  1. On the top right of the Slicer, there is a “Multi-Select” button (checklist icon) to select multiple pieces of data. For example, if you want to filter by two items, such as January and February, you would click the “Multi-Select” button, then deselect all buttons except for January and February. The table will update to show only that data.

  1. When you finish using the current filter, you can clear it and apply another. Select the “Clear Filter” button (filter with an X icon) in the top-right corner of the Slicer.

How to Use More Than One Slicer

If you decide to create more than one Slicer in Excel, it gives you even more options to filter your table data. Each Slicer works on its own to filter the data but also works with the additional Slicer(s) for the data.

  1. In this example, we have a Slicer for Date and another for Totals. We can mix and match the buttons for the data in these fields. For example, we can choose “3100” in the Totals field Slicer and see three results for April, September, and November.

  1. In this example, we are choosing “April” in the Date field Slicer and see just the data for that month (which has 3100 for Totals).

Again, you can use the “Multi-Select” and “Clear Filter” buttons on both or each Slicer as needed.

Note: you can also filter and analyze data using Power Query and Power Pivot in Excel.

How to Adjust the Slicer Settings

If you’d like to rename a Slicer, hide the header, or sort the data, you can make these adjustments in the Slicer Settings.

  1. Right-click the Slicer and pick “Slicer Settings,” or go to the “Slicer” tab and pick “Slider Settings” on the left side of the ribbon.

  1. You can change the name in the “Name” field at the top.

  1. Hide the header by unchecking the “Display header” box. You can also change the caption (header) in the box beneath.

  1. Use the “Item Sorting and Filtering” section to choose ascending or descending order, use Custom Lists when sorting, and hide and customize the items with no data.

  1. Select “OK” to apply your changes, and the Slicer will update automatically.

How to Resize, Position, or Lock a Slicer

Along with the above settings for a Slicer, you can make further adjustments for size, position, and properties.

  1. Right-click the Slicer and pick “Size and Properties.” You can also find a few of the listed settings in the “Slicer” tab.

  1. This opens the Format Slicer sidebar with these expandable sections:
    • Position and Layout: enter the horizontal and vertical measurements, disable resizing or moving of the Slicer, choose the number of columns, or adjust the button height and width.
    • Size: adjust the height, width, rotation, scale height, and scale width for the Slicer’s dimensions. You can also lock the aspect ratio.
    • Properties: mark an option for moving and sizing with cells, moving but not sizing with cells, or not moving or sizing with cells. You can also choose to print just the slicer and lock or unlock the Slicer and its text.

  1. As you make formatting changes to your Slicer, you’ll see it update automatically. This is convenient if you want to immediately revert a change. When you’re finished making changes, close the Format Slicer sidebar using the “X” on the top right.

How to Delete a Slicer

When you’re done using the Slicer, you can remove it from the sheet if you would like.

  1. Select the “Clear Filter” button on the top right of the Slicer to return your table data to its original state.

  1. Right-click the Slicer and choose “Remove [Slicer name]” in the menu.

Frequently Asked Questions

Can I use a Slicer for normal data?

There may be a third-party tool or add-on that can be used for normal data, but if you want to convert your data to a table to check out the Slicer, follow these steps: select your data, go to the “Insert” tab, and choose “Table.” Confirm the cell range in the pop-up window and click “OK.” You’ll see your dataset formatted as a table and can use the Slicer feature.

Tip: If your data is in a PDF, you can convert it to an Excel spreadsheet.

Can I use a Slicer for a chart in Excel?

Because Slicers “connect” to tables, you can use a Slicer to adjust the data in the table that drives the data in the chart.

For example, if you use a table to create a pie chart, select the table and insert the Slicer as described above. When you filter the table data using the Slicer, you should see your chart automatically update to display that filtered data as well.

Can I change the color of a Slicer?

You can use one of Excel’s premade styles to change the color scheme for your Slicer.

To choose a style, select the Slicer, go to the “Slicer” tab that displays, and choose an option in the “Slicer Styles” box.

Image credit: Pixabay. All screenshots by Sandy Writtenhouse.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

Sign up for all newsletters.
By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time. Subscribe


Sandy Writtenhouse

With her BS in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She wanted to help others learn how technology can enrich business and personal lives and has shared her suggestions and how-tos across thousands of articles.

Comments are closed