How to use table slicers in Microsoft Excel

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 take you through a scenario in which you could use table slicers and share step-by-step instructions for creating them.

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.

Using table slicers 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.

1. 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.
2. 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.
3. 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

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.

Table slicers and 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:

1. Select your range of data and create a Line Chart.
2. To have a line in the chart for each year/location combination, we need to select Switch Row/Column from the Chart Design tab. 
3. 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 how to further improve your Excel skills with Becker's Excel Fundamentals and Data Analytics Certificate program.