How to build pivot tables in Excel - Chapter 4: Filters, Slicers and Timelines

To illustrate the use of filters, slicers, and timelines, we will use a fictional dataset of sales data.

The data used represents sales data with various attributes such as the date of the sale, the region, the salesperson, the product sold, the number of units sold, and the total revenue. 

Here is a simplified version of our data:  

Filters in pivot tables allow you to narrow down the data displayed, focusing on specific aspects that are most relevant to your analysis. 

Here’s how you can effectively use filters

Adding a Filter

1. Insert a Pivot Table:

   • Select your data range and go to Insert > Pivot Table.

   • Choose where you want the pivot table to be placed.

2. Add Fields to the Filter Area:

   • In the PivotTable Fields pane, drag the field you want to filter by into the Filters area. For example, drag the "Region" field.

3. Apply the Filter:

   • A filter dropdown will appear above your pivot table.

   • Click the dropdown and select the items you want to include or exclude from your pivot table.

Example

Suppose you want to analyze sales data only for the "North" and "West" regions. Drag the "Region" field to the Filters area and select "North" and "West" from the dropdown.

Benefits of Using Filters

• Focus on Specific Data: Quickly narrow down large datasets to focus on specific data points.

• Improved Readability: Make your pivot tables more readable by displaying only relevant data.

• Dynamic Analysis: Easily change the criteria to explore different aspects of your data without modifying the pivot table structure.

Slicers provide a visual way to filter data in pivot tables. They are especially useful for making your reports interactive and user-friendly.

Adding Slicers

1. Select the Pivot Table:

   • Click anywhere inside your pivot table.

2. Insert Slicers:

   • Go to the Analyze tab (or Options in some versions of Excel).

   • Click Insert Slicer.

   • Select the fields you want to use as slicers, such as "Salesperson" and "Product," and click OK.

3. Using Slicers:

   • Slicers will appear as buttons. Clicking on these buttons will filter the pivot table accordingly.

   • You can select multiple items by holding down the Ctrl key while clicking.

Example

Imagine you want to filter the data by salespersons. Insert a slicer for the "Salesperson" field. Now, you can easily click on the slicer buttons to see data for each salesperson.

Benefits of Slicers

• Visual Appeal: Slicers provide a clear and visually appealing way to filter data.

• Ease of Use: Slicers are more intuitive and user-friendly, especially for those less familiar with Excel.

• Interactivity: Slicers enhance the interactivity of your reports, making it easier for users to explore the data.

Timelines are a specific type of slicer designed for date fields. They provide a visual and interactive way to filter dates, making it easy to analyze data over different periods.

Adding a Timeline

1. Select the Pivot Table:

   • Click anywhere inside your pivot table.

2. Insert a Timeline:

   • Go to the Analyze tab (or Options).

   • Click Insert Timeline.

   • Select the "Date" field and click OK.

3. Using the Timeline:

   • The timeline control will appear. You can drag the slider to filter data by different time periods such as days, months, quarters, or years.

   • Use the handles on the slider to select a custom date range.

Example

Suppose you want to analyze sales data for the first quarter of 2024. Insert a timeline for the "Date" field and adjust the slider to cover January to March 2024

Benefits of Timelines

• Ease of Date Filtering: Timelines make it simple to filter data by dates, which is particularly useful for time-based analyses.

• Interactive Analysis: Users can quickly change the time periods to see how the data trends over different time frames.

• Enhanced Data Exploration: Timelines enable a more in-depth exploration of data, allowing for better insights into trends and patterns.

Filters, slicers, and timelines are powerful tools that can significantly enhance the functionality and usability of Excel Pivot Tables. 

Filters allow you to focus on specific data, while slicers and timelines add a layer of interactivity and visual appeal. 

By incorporating these tools into your pivot tables, you can create more dynamic, user-friendly, and insightful reports that help you make better data-driven decisions.

Explore these features in your next Excel project to unlock the full potential of your data analysis capabilities!