Excel: analyze table data with slicers and a total row
Excel

Excel: analyze table data with slicers and a total row

by HubSite 365 about Chandoo

Turn a data range into an Excel table, add a total row and slicers, then filter products or regions to see updated results.

Key insights

  • Use Ctrl+T to turn a data range into a table, then give the table a name.
  • Enable Total Row in the Table Design tab and choose a calculation for a column.
  • Slicers for fields such as Product and Region filter the table with button clicks.
  • The demonstrated total uses SUBTOTAL, which recalculates for visible filtered values.
  • Use SUBTOTAL function number 109 for a sum or 101 for an average.

Excel tables, a total row and slicers provide a quick way to filter a dataset and see a result for the rows that remain visible. This approach is useful for Excel users who need to answer straightforward questions about fields such as sales quantity, product or region without first building a separate report. Chandoo demonstrates the workflow with a sales dataset and shows the total changing as he filters it.

What it does

The method combines three Excel features: a table, its Total Row option and slicers. A table gives the data a structured format; the Total Row adds a calculation at the bottom; slicers provide clickable filters for selected columns. Together, they let you narrow the displayed records and read a calculation for that filtered view.

In the demonstration, the data includes sales quantities, products and regions. Chandoo creates a table named Sales, adds a total for the quantity sold, and adds slicers for Product and Region. Clicking a region or product filters the table, and the total updates to reflect the visible values.

This is a lightweight way to explore a dataset interactively. It does not require setting up a chart or a PivotTable for the particular questions shown. For more Excel guidance and related coverage, see our Excel topic page.

Who it is for and what you need

The workflow is aimed at people working directly with a list of data in Excel who want to filter by one or more fields and quickly inspect a calculation. The example uses sales records, but the described process depends on having a data range with columns that can serve as table fields and slicer choices.

Start with the dataset in a worksheet. The demonstration does not rely on a separate analytics service: the table, total and slicers are set up in Excel. It uses the Table Design tab to turn on the Total Row and the Insert tab to add slicers. The steps shown use the Ctrl+T keyboard shortcut to create a table.

The example calculates a sum and then changes the calculation to an average. Choose a column whose values make sense for the calculation you want to display. The demonstrated quantity column supports both a sum and an average; the total row’s result is intended to change as slicers filter the records.

How it works

  1. Select a cell in the data range and press Ctrl+T to create a table. Confirm the table setup, then assign a name such as Sales if you want to identify it by name.
  2. With the table selected, open Table Design and enable Total Row. Excel adds a row at the bottom of the table. Set the quantity column’s total calculation to Sum.
  3. Move the total row above the data if that layout suits your quick-analysis view. In the demonstration, Chandoo cuts the total row and pastes it at the top.
  4. Open Insert and choose Slicer. Select the fields you want to use as filters, such as Product and Region, and add the slicers to the worksheet.
  5. Click a slicer button to filter the records. The total row shows the result for the filtered data. To select multiple items, hold Ctrl while clicking the slicer choices.

The total row uses the SUBTOTAL function. In the example, function number 109 sums the visible values in the quantity column. For an average instead, replace 109 with 101. Changing the function changes what the total row calculates while the slicer filters determine which values are included.

Limitations and alternatives

This approach answers questions that can be expressed by filtering table fields and applying a total-row calculation. The example focuses on a sum or average for a selected field; the demonstrated workflow does not describe a broader report-building process. The total is a single calculation, while slicers let you change the records included in it.

Before using the result, make sure the field chosen for the total matches the question you are asking. A sum answers a different question from an average, and the visible selection depends on the slicer choices. If multiple slicer items are selected, the calculation reflects the filtered set rather than just one item.

PivotTables are the other method Chandoo identifies for quick data analysis. They are an alternative to this table-and-slicer setup, although the demonstration does not explain their steps. For a quick view of one calculation across filter selections, the table approach keeps the calculation in the total row and makes the filters directly clickable.

Sources

Frequently asked questions

How do I add a total row to an Excel table?

Select the table, open the Table Design tab and enable Total Row. Choose the calculation you want in the relevant column.

How can I filter an Excel table with slicers?

Select a cell in the table, use Insert to add a Slicer, and choose fields such as Product or Region. Click slicer buttons to filter the records.

Why does my Excel total change when I use a slicer?

The demonstrated total row uses SUBTOTAL, which calculates from visible values. Its result changes as slicers filter the table.

Which SUBTOTAL numbers calculate a sum or average?

The example uses 109 to sum visible values and 101 to calculate their average.