Function Problems

How to Add a Total Row After a Filtered Excel Table

Kushani Nimanthika Oct 12, 2026 869 views

Question details

The user wants to display a dynamically adjusting total row immediately following the last row of data returned by a FILTER function.

How to Add a Total Row After a Filtered Excel Table

Product: Excel
Device & OS: not provided
Scenario: Extracting nonempty rows from a source dataset using the FILTER function and ensuring a total row is always pinned to the bottom of the spilled array.
Observed behavior: A standard FILTER function only extracts data rows; it does not automatically calculate or append a summary total row at the end of the dynamic results.

Before you start

Ensure your source data is formatted as an official Excel Table (using the Ctrl+T shortcut). This enables structured referencing, making it much easier to pull both the data and the built-in total row dynamically.

Solution 1Recommended

Include the Source Table's Total Row in the FILTER Range

Enable the built-in Total Row in your source table and adjust your FILTER formula to include the entire table (headers, data, and totals), using conditional formatting to style the total row.

By enabling the Total Row in your original dataset and extending your FILTER array reference to include it, the total row will dynamically appear at the bottom of your extracted results. This method is highly reliable as it relies on built-in table features.

Enable the Total Row in the source table

Click anywhere inside your source table (e.g., Table1). Navigate to the 'Table Design' tab on the ribbon and check the 'Total Row' box. A total row will appear at the bottom of your source data.

Apply the FILTER function to the entire table

In your destination cell, enter a FILTER formula that references the entire table structure rather than just the data body. Use structured references like: =FILTER(Table1[#All], Table1[[#All],[Column1]]<>""). This ensures headers, nonempty rows, and the total row are all returned.

Format the Total Row with Conditional Formatting

Select the cell range where your dynamic array spills. Go to Home > Conditional Formatting > New Rule. Choose 'Use a formula to determine which cells to format', enter a formula that identifies the word 'Total' (e.g., =$A1="Total"), and apply bold formatting or a top border to distinguish the total row.

Include the Source Table's Total Row in the FILTER Range

Structured References: Using the [#All] modifier in your table reference is the key step, as it forces Excel to look beyond just the data body rows.

Solution 2

Use VSTACK to Append a Calculated Total Row

If you cannot modify the source table, use the VSTACK function to stack the filtered data array on top of a dynamically calculated total array.

Advanced Spreadsheet Management

Easily Manage Filtered Tables and Totals with WPS Spreadsheet

WPS Office provides a highly capable, free spreadsheet tool that fully supports structured table referencing and advanced dynamic array functions like FILTER and VSTACK, ensuring you can manage data seamlessly.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the source dataset.
2. Format as Table: Select your data, go to the Home tab, and click 'Format as Table'. Check the 'Total Row' option from the Table Tools tab to instantly add summary calculations.
3. Extract with Dynamic Arrays: Use the FILTER function to dynamically spill your table data alongside the appended total row, exactly as you would in Microsoft Excel.

100% compatible with Microsoft Excel (.xlsx) formatsFull support for advanced dynamic array functions like FILTER and VSTACKIntuitive Table Design tools for quick data summarizationFree and lightweight alternative for powerful data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does my manual total row disappear when I use standard AutoFilters?

When you apply standard filters to a range of data, Excel hides the rows that do not meet your criteria. If your manual total row is part of that range, it might get hidden. Converting your data into an official Table and enabling the built-in Total Row prevents this.

Can I use the SUBTOTAL function instead of FILTER for this task?

The SUBTOTAL and AGGREGATE functions are excellent for filtering data in place, as they automatically ignore hidden rows. However, if you need to extract and spill the filtered data to a different location or worksheet, the FILTER function is required.

Are FILTER and VSTACK functions available in older versions of Excel?

No. FILTER and VSTACK are dynamic array functions that are only available in Microsoft 365, Excel 2021, and modern alternatives like WPS Office. If you are using an older version of Excel, consider using Pivot Tables to dynamically filter and total your data.

Excel & Data Problems