Function Problems

How to Add an Excel Total Row Using VSTACK and HSTACK Formulas

Maira Mehtab Oct 10, 2026 871 views

Question details

The user wants to append a total row to a dynamically filtered and sorted dataset in Excel using formulas, without the unnecessary complexity of adding and removing labels using VSTACK and DROP.

How to Add an Excel Total Row Using VSTACK

Product: Excel
Device & OS: not provided
Scenario: Creating dynamic reports where filtered and sorted data needs a concluding 'Total' row at the bottom.
Observed behavior: The user was previously using VSTACK combined with DROP to add and then remove a label, which was functionally redundant and inefficient.

Before you start

Ensure your dataset does not contain completely empty rows within the target range, and confirm that your version of Excel or WPS Spreadsheet supports dynamic array functions like VSTACK and HSTACK.

Solution 1Recommended

Directly Append a Total Row Using VSTACK and HSTACK

Instead of adding and dropping labels, directly stack your filtered data with a customized total row using VSTACK and HSTACK.

The VSTACK function allows you to vertically stack multiple arrays into a single continuous block. By combining it with SORT and FILTER, you can dynamically display your data. Using HSTACK alongside VSTACK lets you create a custom total row at the very bottom without needing the DROP function to remove unwanted data later.

Define the target data range

Identify the data array you want to filter and sort. For example, use DP8:DT200 as your main data array and DP8:DP200 for your filter criteria.

Filter and sort the main data

Use the FILTER function to exclude blank cells and existing totals: FILTER(DP8:DT200,(DP8:DP200<>"")*(DP8:DP200<>"Total")). Wrap this in the SORT function to order the results based on your preferred columns.

Create the Total row array

Use HSTACK to create a single-row array containing the text label 'Total' and the calculated sum, such as: HSTACK("Total",SUM(DQ8:DQ200)/2).

Stack the data and total row together

Combine both elements using VSTACK. Enter the complete formula: =VSTACK(SORT(FILTER(DP8:DT200,(DP8:DP200<>"")*(DP8:DP200<>"Total")),{2,3,1},-1),HSTACK("Total",SUM(DQ8:DQ200)/2)) into the top-left cell of your destination range.

Directly Append a Total Row Using VSTACK and HSTACK

Adjusting Cell Ranges: Make sure to adjust the cell references (like DP8:DT200 and DQ8:DQ200) to perfectly match the columns and rows of the dataset in your specific worksheet.

Process Dynamic Arrays in WPS Spreadsheet

Easily Manage Dynamic Array Formulas with WPS Office

WPS Spreadsheet provides full support for advanced dynamic array functions, including VSTACK, HSTACK, FILTER, and SORT, allowing you to build complex, automated reports with ease.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your raw dataset.
2. Enter the formula: Select an empty cell with enough space below and to the right, then type or paste your VSTACK formula.
3. Execute the dynamic array: Press Enter to execute. WPS Spreadsheet will automatically spill the results, seamlessly displaying the sorted data and the appended Total row.

Full compatibility with Microsoft Excel dynamic array formulas like VSTACK and HSTACK.Lightweight and runs smoothly even with large, calculation-heavy datasets.Free to use with a familiar, intuitive interface that requires no learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VSTACK formula return a #SPILL! error?

A #SPILL! error occurs when there isn't enough empty space below or to the right of the cell containing the formula. Ensure the destination range is completely clear of other data or text before hitting Enter.

Can I use VSTACK in older versions of Excel?

No, VSTACK and HSTACK are dynamic array functions available only in Microsoft 365, Excel 2021, and modern versions of WPS Office. Older legacy versions will not recognize these functions and may return a #NAME? error.

How do I add multiple summary calculations like Average or Count to the bottom row?

You can expand the HSTACK part of the formula. For example, HSTACK("Total", SUM(DQ8:DQ200), AVERAGE(DR8:DR200)) allows you to place different calculations side-by-side in the final stacked row.

Why was DROP used previously, and why is it unnecessary here?

Users sometimes use VSTACK to append a predefined range that includes an unwanted header or label, then use DROP to remove that specific row. It is more efficient to precisely construct the arrays you actually want to combine using HSTACK, completely eliminating the need for DROP.

Excel & Data Problems