Filtering data in Excel is already pretty powerful, but if you haven’t started using Slicers, you’re missing out on a faster, more intuitive way to filter and analyze your data. Whether you’re a business owner, a data analyst, or just someone who deals with large spreadsheets, Slicers can save you time and give you a clearer view of your information. In this blog post, I’ll walk you through how to filter data with slicers in Excel and show you just how easy it is to integrate them into both tables and PivotTables.

Here’s the tutorial video for those who prefer following along with visuals:

Getting Started with Slicers

Let’s say we have an annual sales report for a fruit company. Normally, you’d filter this data by selecting various drop-downs for each column in your table. While this method works, it’s not as quick or clear as using slicers.

Step 1: Insert Your Slicers

If your data is already in a table format, inserting slicers takes only a few clicks:

  1. Click anywhere inside your table.
  2. Head to the Insert tab and select Slicer.
  3. You’ll get a pop-up showing all the column headers of your table. Choose the columns you want to filter with slicers. For example, let’s select Location, Customer Name, and Item Description.

Once you hit “OK,” Excel will generate slicers for these selections. You’ll see boxes for each filter, and they can be moved around freely on your screen for ease of use.

Customizing Your Slicers

Excel gives you a lot of flexibility with slicers—presentation-wise and functionality-wise. You can make them fit your style and needs in several ways.

Changing Slicer Styles

By default, slicers come in a basic blue layout. However, Excel allows you to tweak the look:

  • Select your slicer.
  • Use the Slicer Tools tab to choose a preset style. You can make them any color, but often, sticking with something simple like blue makes the data easier to read.

Adjusting Columns and Button Sizes

If you have a long list of customers or items to scroll through, navigating a slicer with a single column can feel tedious. Fortunately, you can set slicers to display multiple columns.

  • Select your slicer, go to Slicer Tools -> Options.
  • You can pick, let’s say, 3 columns under the size options. Now, the names will appear across instead of having to scroll down endlessly.

You can also expand or shrink the size of the slicer itself by changing the width, height, and button size to fit your screen.

RELATED TOPIC: How to Create a Fillable Form With a Submit Button in Excel

Filtering Data Using Slicers

Now on to the real magic—how to actually filter data using your slicers.

Selecting a Filter

Let’s imagine you want to see just data from Asia. In the Location slicer, just click Asia, and only data from that region will populate your table. But it goes deeper than that:

  • Your other slicers will adjust dynamically based on this selection. For example, only the customer names from Asia will remain selectable (the rest are grayed out). From there, you could filter again down to Walmart for example, and even further by selecting specific fruits—say apples.

Each selection updates your table in real time, allowing you to really drill down into your dataset with little effort.

Clearing Filters

When you’re done narrowing down your data and want to return to the full table, you won’t need to manually deselect everything.

  • Just hit the Clear Filter button (located in the top-right of each slicer), and your full dataset will reappear instantly.

Multi-Select Filtering

Excel’s slicers also let you filter across multiple categories easily.

  • Hold CTRL while selecting multiple slicer values. For example, you could choose both Asia and US locations or even filter by multiple fruits like watermelons and apples at the same time.

Excel will handle the complex filtering behind the scenes, updating the table for just those regions and products. When you want to deselect one of the options (like removing Italy after selecting it), just click on it again.

RELATED TOPIC: How to Use the Quick Analysis Tool in Excel

Using Slicers in PivotTables

Slicers aren’t limited to regular tables. They’re also powerful tools in PivotTables, where they can act as visual filters.

Step 1: Insert Your PivotTable

  1. Select your data and go to Insert -> PivotTable.
  2. When you’re setting up your table, leave the Filters selection blank—you’ll use your slicers for that instead.

Step 2: Insert Slicers for the PivotTable

Once your PivotTable template is in place, you can add slicers in the same way you did for a regular table:

  1. PivotTable Analyze -> Insert Slicer.
  2. Choose filters like Location, Customer, and Item.

After setting the slicers up, you can easily filter your PivotTable the same way you would a typical table. Want to see how many apples were sold by Save-a-lot in Asia? Just select the relevant slicer buttons, and your PivotTable data will update immediately. Multi-select features work here too.

PivotTable Slicers are Dynamic

Here’s a bonus: slicers in PivotTables will automatically update if your dataset changes. Say you add a new sale for Trader Joe’s involving kiwis—as long as you refresh your PivotTable (through PivotTable Analyze -> Refresh), the slicers and PivotTable will pick up those changes. New data, new filters, all updated seamlessly.

RELATED TOPIC: How to Create a Fillable Form with a Submit Button in Word

Conclusion

Slicers offer a highly visual, easy-to-use way to filter data in Excel. Whether you’re using them in a standard table or a PivotTable, they help you drill down into your data much faster and more intuitively than regular filters. Plus, with options to customize styles, layout, and more, you can get them working exactly the way you want.

Take some time to test slicers out in your own worksheets—you’ll quickly see how they speed up your workflow and make data analysis in Excel that much smoother.

If you found this guide useful, be sure to check out the full tutorial in the video above. And don’t forget to subscribe for more tips on improving your Excel skills!

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR