Welcome to another entry in our series of Dr. Wayne Winston’s best Excel tips! I’m Wayne Winston, Becker’s resident expert on all things Microsoft Excel. Let’s learn another helpful Excel skill that can make your accounting and finance work a bit easier.
PivotTables are the most commonly used tool to summarize data. Many users of PivotTables (and PivotCharts) don’t know how to use Slicers and Timelines to turn a single PivotTable into potentially thousands of PivotTables. In this article, you will learn how to use Slicers and Timelines to enhance the usefulness of your PivotTables.
Copy and paste this link into a new browser tab to open the Excel workbook and follow along.
Suppose we own the local Quickie Mart, with sales data shown below and in the workbook.
Figure 1: Daily sales at the Quickie Mart
We begin by using a PivotTable and PivotChart to summarize daily sales of each product. With the cursor anywhere in the data, select Insert àPivotTable, making sure the correct data is selected to input into the table. Fill in the field list as shown in Figure 2.
Figure 2: Quickie Mart PivotTable
We now have a PivotTable that summarizes monthly revenue for each product group. With the cursor anywhere in the first column of the pivot table, right clicking and choosing Ungroup gives us a breakdown of daily sales by product (you can also do this via PivotTable Analyze àUngroup.) With your cursor in the PivotTable, go to the PivotTable Analyze tab à PivotChart à Clustered column and you will produce the PivotChart shown in Figure 3.
Figure 3: PivotChart of product sales
Of course, this chart and the PivotTable are cluttered. With a Slicer Timeline, you can choose any set of products to show in the PivotTable and PivotChart, and with a Timeline, you can control the set of dates used to compute the PivotTable and create the PivotChart.
Adding a Slicer
To insert a Slicer, put your cursor anywhere in the PivotTable. From the Insert tab, choose Slicer from the ribbon and check the Product box. From the Slicer shown in Figure 4, you can choose (with the Shift or Control keys) any subset of the products to key the determination of the PivotTable and PivotChart – in the below example, we chose deli and magazines.
Figure 4: PivotTable and PivotChart based on deli and magazine sales
Clicking on the funnel within the Slicer clears the filter. With your cursor inside a Slicer, you can click on the Slicer option from the ribbon and adjust the number of rows and columns in the Slicer, as well as its size.
Adding a Timeline
To add a Timeline to a PivotTable, your source data needs to have a column of dates (see Column B in your original source data.) With the cursor inside the PivotTable, navigate to PivotTable Analyze à Filter group à Insert Timeline, and select the Date box. You will now see a Timeline (shown in Figure 5.)
Figure 5: Timeline
From the Timeline, you can select any contiguous range of Years, Quarters, Months or Days. We chose the data from April-June 2022. Now our PivotTable and PivotChart are based only on deli and magazine sales during April-June 2022.
Using just the Slicer, you can create 63 different PivotTable x PivotChart combinations, and the Timeline can create thousands of different charts for each Slicer choice. The appeal of Slicers and Timelines is not only that you can easily create many different views, but you can also easily see the combination of products and dates that were used to create your tables and charts.
I hope this has been a helpful tip that will help you in your accounting or finance career.
Want to improve your accounting Excel skillset? Enroll in Becker’s Microsoft® Excel Fundamentals + Data Analytics Certificate and learn essential Excel functions for accounting, from fundamentals all the way to in-depth applications of data analytics.
