Excel slicer: visual filter for pivot tables and charts

This tutorial demonstrates how to add slicers to Excel tables, pivot tables, and pivot charts in Excel 2010 through 365. We'll cover advanced techniques like creating custom slicer styles, connecting a single slicer to multiple pivot tables, and more. This empowers you to create interactive and user-friendly reports.

Excel pivot tables are powerful tools for summarizing large datasets. Slicers, also known as visual filters, enhance their usability. Share your reports with colleagues, and they can filter data independently.

• What are Excel slicers?
• Slicers vs. PivotTable filters
• Adding slicers in Excel:
  • Adding to pivot tables
  • Adding to Excel tables
  • Adding to pivot charts
• Using slicers in Excel
• Customizing Excel slicers
• Connecting slicers to multiple pivot tables and charts
• Locking pivot tables while keeping slicers unlocked
• Removing slicers from a sheet

What are Excel Slicers?

Excel slicers are graphical filters for tables, pivot tables, and pivot charts. Their visual nature makes them ideal for dashboards and reports, simplifying data filtering. Introduced in Excel 2010, they're available in all subsequent versions (2013-365). Filter data by selecting one or more buttons within the slicer.

Slicers vs. PivotTable Filters

Both slicers and pivot table filters achieve the same outcome – displaying some data while hiding others. However, they have distinct advantages and disadvantages:

• Ease of Use: Pivot table filters can be cumbersome; slicers offer simpler, button-based filtering.
• Connectivity: Filters are limited to single pivot tables; slicers can connect to multiple pivot tables and charts.
• Flexibility: Filters are fixed to rows and columns; slicers are movable objects, positionable beside or even within chart areas for real-time updates.
• Touchscreen Compatibility: Slicers are more touch-friendly than pivot table filters (except in Excel mobile apps).
• Space: Pivot table filters are compact; slicers require more worksheet space.
• Automation: Pivot table filters are easily automated with VBA; slicer automation requires more advanced skills.

Adding Slicers in Excel

Let's explore how to add slicers to your Excel data.

Adding a Slicer to an Excel Pivot Table

Adding a slicer to a pivot table is quick:

1. Select any cell within the pivot table.
2. In Excel 2013 and later, navigate to the Analyze tab > Filter group, and click Insert Slicer. In Excel 2010, use the Options tab and click Insert Slicer.
3. The Insert Slicers dialog box appears. Select the fields for which you want to create slicers.
4. Click OK.

The image below shows two slicers filtering by Product and Reseller.

Resulting slicers:

For time-based filtering, use a timeline slicer.

Adding a Slicer to an Excel Table

Modern Excel versions allow adding slicers to regular tables:

1. Select a cell within your table.
2. Go to the Insert tab > Filters group, and click Slicer.
3. In the Insert Slicers dialog box, check the columns you want to filter.
4. Click OK.

Adding a Slicer to a Pivot Chart

To filter a pivot chart, create a slicer for its associated pivot table. The slicer will control both. For closer integration:

1. Select a cell in the pivot chart.
2. Go to the Analyze tab > Filter group, and click Insert Slicer.
3. Choose the desired fields and click OK.

The slicer can be positioned within the chart area for a streamlined look.

Using Slicers in Excel

Slicers are intuitive to use.

Slicers as Visual Filters

Click a slicer button to filter data. The pivot table updates instantly. Unselect a button to remove that item from the filter.

Slicers can filter data even if the corresponding field is hidden in the pivot table.

Greyed-out items in a slicer indicate no matching data based on other slicer selections.

Selecting Multiple Items

Select multiple items using Ctrl-click, the Multi-Select button, or Alt S (toggle multi-select).

Moving and Resizing Slicers

Drag slicers to reposition them. Resize by dragging edges or using the Slicer Tools Options tab.

Locking Slicer Position

Prevent slicer movement by right-clicking, selecting Size and Properties, and checking "Don't move or size with cells".

Clearing Slicer Filters

Clear filters using Alt C or the Clear Filter button.

Disconnecting Slicers

Disconnect a slicer from a pivot table using Report Connections (or PivotTable Connections in Excel 2010).

Customizing Excel Slicers

Slicers are highly customizable.

Changing Slicer Style

Use the Slicer Styles gallery on the Slicer Tools Options tab.

Creating Custom Slicer Styles

Create custom styles using the New Slicer Style button. Customize individual elements using the Format button.

Multiple Columns in Slicers

Adjust the number of columns in the slicer using the Columns box on the Slicer Tools Options tab.

Changing Slicer Settings

Use the Slicer Settings dialog box (right-click the slicer, then Slicer Settings…) to customize various options, including hiding the header, sorting items, and hiding items with no data.

Connecting Slicers to Multiple Pivot Tables

Connect a slicer to multiple pivot tables (based on the same data source) using the Report Connections dialog box.

Unlocking Slicers in Protected Worksheets

Unlock slicers in a protected worksheet by unchecking the Locked box in the Format Slicer pane, then protecting the sheet with the Use PivotTable & PivotChart option enabled.

Removing Slicers

Delete slicers by pressing Delete or right-clicking and selecting Remove.

Practice Workbook

[Link to Excel Slicer examples (.xlsx file)]

The above is the detailed content of Excel slicer: visual filter for pivot tables and charts. For more information, please follow other related articles on the PHP Chinese website!