As an accountant or finance professional, Microsoft Excel can be your life saver or the bane of your existence. If you use the program’s shortcuts and tools, it should be making your job easier by automating tasks and organizing dumps of data.
Table slicers are one such magical Excel tool. You can use them to create charts and graphs that automatically update when new data is entered. In this article, we’ll walk you through how to add a slicer in Excel.
What is a slicer in Excel?
A slicer allows you to filter data in tables, PivotTables, and PivotCharts easily and offer interactive filtering so you can include buttons to filter data more easily. Slicers in Excel also offer a more visual and intuitive way to filter your data and can be formatted to suit the design, layout, and style of your worksheet.
Creating a table in Excel
Suppose you are CFO of a multinational corporation. Your CEO wants an easy way to find US and international sales for any subset of years. The CEO also wants to be able to have your calculations automatically update if new information (more years and locations) is added to the data.
As shown in Figure 1, you have data on monthly US and international sales during the years 2020-2025.
Figure 1 Monthly Sales 2020-2025
To solve this problem, you need to utilize Excel tables. When a range of cells has headings in the first row you can make the range an Excel table by selecting the range and using the Ctrl+T keyboard combination. Once you have made a range of cells an Excel table, charts and formulas automatically update when new data is added to the table range.
In our example, we selected the range A15:N27 and after hitting Ctrl+T and checking the My Table has Headers button, we made our data a table. With the cursor in the table, click on the Table Design Tab and change the name of the table to Sum with Slicers.
How to add a slicer in Excel
Recall that our CFO wants to be able to sum up sales for any subset of years and locations. This requires the use of table slicers. Here are step-by-step instructions to create table slicers.
- In cell A3 we add up our total sales by applying the SUM function to the range C16:N27. The formula appears as =SUM(sumwithslicers[[January]:[December]]). This formula tells Excel to add up columns January through December in the sumwithslicers table. Since this range is a table, the formula would update automatically if new data is added.
- With your cursor anywhere inside the table, select Slicers from the Insert tab and select the Year and Location fields. You will now see the Slicers shown in Figure 2.
- To show any subset of the table data, select the desired years and locations. For example, in Figure 2 we selected the US and the years 2021 and 2023 (you can clear filters by clicking on the funnel.)
Figure 2 2021 and 2023 US data selected with slicers
Download our FREE ebook Excel automation for accountants
With step-by-step tutorials and real world examples, learn valuable automation functions in Excel that save time, improve accuracy, and and enhance your skills!
The amazing AGGREGATE function
As seen in figure 2, only the US sales from 2021 and 2023 are shown. We would hope that our SUM formula in cell A1 would update to total sales just for the US in 2021 and 2023, but it does not.
For a total sales calculation reflecting the rows of data selected with the slicers, we can use the AGGREGATE function. Here’s how:
In cell A4 we entered the formula
=AGGREGATE(9,5,C16:O27).
The first argument of 9 tells Excel to compute a sum and the 2nd argument of 5, tells Excel to ignore hidden rows. The third argument of the function is the range that will be summed. Since we are ignoring hidden rows, we now see that 2021 and 2023 US sales total to 3,571,000.
Let’s suppose in 2026 we start selling on Mars (watch For All Mankind on Apple TV, a really great show about space travel!) In row 28, enter 2026 and Mars and add some data. If your slicers have been cleared, then you will find that the AGGREGATE formula updates to include the 2026 Mars data. Also note that your slicers now include 2026 and Mars. If you click on 2026 and Mars, you will find that the AGGREGATE formula gives the total sales on Mars in 2026.
Adding a slicer in Excel and creating charts
Now your demanding CEO wants to be able to select any subset of the data and create a chart based on the selected data. Here’s how we accomplish this:
- Select your range of data and create a Line Chart.
- To have a line in the chart for each year/location combination, we need to select Switch Row/Column from the Chart Design tab.
- If we select any subset of our data with the slicers, the chart is only based on the selected data. Also, if we add a new row of data and include it in the slicer selection, the new row’s data is charted. For example, after adding sales data for Mars in 2026, we obtain the following chart showing sales non US sales during the years 2025-2026.
Figure 3 Chart of 2025 and 2026 data in Mars and INTL
Table slicers are a user-friendly, powerful tool that can be used to create charts and calculations that automatically update when you add new data to a spreadsheet. Use this tool often and wow yourself and your boss.
Learn more with Excel CPE
Excel offers a wide variety of functions, formulas, and automation that can save you hours of work while improving accuracy and outcomes. To help you make the most out of this software, we offer a wide variety of CPE courses to support your learning!
Check out Becker's wide range of CPE courses that teach you to make the most of this powerful tool:
- Excel: Technical Analysis Trading Strategies
- Excel: Enterprise Risk Management
- Excel: Magic with Excel
- Excel: Solve Hard Problems in Corporate Finance
- Excel Metrics: Best Practices
- Python for Excel Users: A Gentle Introduction
These and many more Excel-focused, CPE credit-earning courses are included in Becker's Prime CPE subscription. Sign up now for 12 months of access to over 1700 on-demand, webcast, and podcast CPE courses!
Unlock unlimited CPE with a Prime Subscription
Becker makes it easy to meet your CPE requirements, gain new skills, and stay aware of critical updates and changes in the industry!
With Prime, you can access over 1,700 courses for a full year and earn unlimited CPE credits.
