Use slicers to filter data

Slicers in Excel are interactive controls that allow you to filter data visually. They’re commonly used in PivotTables and Excel tables to make filtering easier and more user-friendly. Slicers give you buttons for each item in a column, which makes it simple to filter data with a click.

How to Use Slicers to Filter Data in Excel

1. Insert a Slicer for a Table

You can use slicers to filter data in an Excel table, which makes filtering more intuitive.

Steps to Add a Slicer for a Table:

1. Select a Cell in the Table: Click on any cell inside the table you want to filter with a slicer.
2. Go to the Table Design Tab:
   • On the Ribbon, click the Table Design tab (this appears when you’re inside a table).
3. Insert a Slicer:
   • In the Tools group, click on Insert Slicer.
4. Choose Fields for the Slicer:
   • A dialog box will appear with a list of the columns in your table.
   • Select the column(s) you want to filter by, and click OK. For example, if you have a table of sales data and you want to filter by Product or Region, select those fields.
5. Slicer Appears:
   • A slicer will appear as a separate box with buttons for each unique value in the selected field(s).

Using the Slicer to Filter Data:

• Click on the buttons in the slicer to filter the table data based on the selected values.
• You can select multiple items by holding the Ctrl key while clicking on the buttons.
• To clear the filter and show all data again, click the Clear Filter button (a small filter icon with a red “X”) in the top right of the slicer.

2. Insert a Slicer for a PivotTable

Slicers are also commonly used with PivotTables to filter and analyze data interactively.

Steps to Add a Slicer for a PivotTable:

1. Select a Cell in the PivotTable: Click anywhere inside your PivotTable.
2. Go to the PivotTable Analyze Tab:
   • On the Ribbon, go to the PivotTable Analyze tab (this appears when you’re inside a PivotTable).
3. Insert a Slicer:
   • In the Filter group, click Insert Slicer.
4. Choose Fields for the Slicer:
   • A dialog box will appear with a list of the fields in the PivotTable.
   • Select the field(s) you want to filter by and click OK.
5. Use the Slicer:
   • The slicer will appear as a box with buttons for each unique item in the selected field.
   • Click on any of the buttons to filter the PivotTable data based on that field.

3. Use Multiple Slicers for More Complex Filtering

You can use more than one slicer to filter data based on multiple criteria. This is useful when you want to filter by more than one column or field.

Steps to Add Multiple Slicers:

1. Add a slicer for one column as described above.
2. After that slicer is inserted, repeat the process for additional fields.
   • You can add multiple slicers for different columns or fields from the table or PivotTable.
3. Once you have multiple slicers, you can filter your data based on combinations of the selections from each slicer.

4. Format the Slicer (Optional)

You can customize and format slicers to fit the look and feel of your report or worksheet.

Steps to Format a Slicer:

1. Select the Slicer: Click on the slicer you want to format.
2. Go to the Slicer Options Tab:
   • In the Ribbon, click the Slicer tab (this appears when the slicer is selected).
3. Choose a Style:
   • You can choose from predefined styles, or click New Slicer Style to create your own.
4. Resize and Position:
   • You can resize the slicer by dragging its corners and position it anywhere on your sheet.
5. Customize Buttons:
   • You can change the number of columns and rows that show in the slicer buttons by clicking the Options button in the Slicer tab and adjusting the Number of columns.

5. Clear Filters from Slicers

To remove a filter and show all data again:

1. Click on the Clear Filter button (a small filter icon with a red “X”) in the top right corner of the slicer.
2. This will reset the slicer and display all items in your table or PivotTable.

Conclusion

Slicers make filtering data in Excel both simple and interactive, enhancing your ability to analyze data quickly. They are especially useful for filtering PivotTables and tables, as they allow you to filter multiple fields and display the data in a visually appealing way. Slicers are also highly customizable, allowing you to design them to fit your needs.