Career

Spice up your PivotTables with Slicers and Timelines

Laptop surrounded by papers

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
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
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
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
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
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.

Try Becker's CPA Exam Review FREE for 14 days
FacebookLinkedinXEmail

About the author

Dr. Wayne Winston is a Professor Emeritus of Decision Sciences at the Kelley School of Business at Indiana University. He holds a B.S. in Mathematics from M.I.T. and a PhD in operations research from Yale. He won more than 40 teaching awards at Indiana University. He has written more than a dozen books including "Marketing Analytics", "Data Analysis and Decision Making", "Operations Research", "Practical Management Science", "Office 365 Data Analysis and Business Modeling", and "Mathletics." Dr. Winston has taught classes and consulted for many leading global organizations. He is also a two-time Jeopardy! champion and has consulted for the NBA’s Dallas Mavericks and New York Knicks. His Linkedin class on Forecasting has been completed by over 250,000 learners. He has also published 30 research articles.

Featured

7 things every new public accountant should know
7 things every new public accountant should know
Read More
Behavioral interviewing techniques
Behavioral interviewing techniques
Read More
What do accountants do?
What do accountants do?
Read More

Now Leaving Becker.com

You are leaving the Becker.com website. Once you click “continue,” you will be brought to a third-party website. Please be aware, the privacy policy may differ on the third-party website. Adtalem Global Education is not responsible for the security, contents and accuracy of any information provided on the third-party website. Note that the website may still be a third-party website even the format is similar to the Becker.com website.

Continue