Function Problems

How to Create a Column-by-Column Running Total in Excel

Natalie Taylor Oct 12, 2026 869 views

Question details

The user wants to calculate cumulative totals that calculate continuously down one column and then proceed sequentially to the next column.

How to Create a Column-by-Column Running Total in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating continuous cumulative sums across multiple columns of data without breaking the total between column transitions.
Observed behavior: Using dynamic-array functions (VSTACK, TOCOL, SCAN, WRAPCOLS) or traditional formulas (SUM, OFFSET) allows the running total to flatten, calculate, and wrap correctly.

Before you start

Ensure you are using a recent version of your spreadsheet software (such as Microsoft 365) that supports dynamic array functions if you plan to use SCAN, TOCOL, and WRAPCOLS.

Solution 1Recommended

Use Dynamic Array Functions (Recommended)

Combine modern dynamic array functions to automatically flatten your data, calculate the running total, and wrap it back into columns.

This method is the most robust and elegant solution for newer versions of Excel. It seamlessly transitions the running sum from the bottom of one column to the top of the next.

Select the output cell

Click on the cell where you want the new running total matrix to start (for example, J5). Ensure there is enough empty space below and to the right for the data to spill.

Input the combination formula

Enter the following formula: =WRAPCOLS(SCAN(0,TOCOL(F5:G13,,TRUE),LAMBDA(a,I,SUM(a,I))),COUNTA(F5:F13)). Replace F5:G13 with your actual source data range, and F5:F13 with a single column of your source range to determine the row count.

Execute the formula

Press Enter. The formula will automatically spill the calculated cumulative totals into the matching column layout.

Use Dynamic Array Functions (Recommended)

Formula Breakdown: TOCOL flattens the range column by column, SCAN calculates the running sum across the flattened array, and WRAPCOLS restructures it back into the original column dimensions.

Solution 2

Use SUM, OFFSET, and ROW Functions

A traditional approach to create running totals by dynamically expanding the sum range, suitable for older versions of Excel.

Efficient Data Calculation

Calculate Running Totals Easily with WPS Spreadsheet

WPS Office Spreadsheet provides comprehensive support for advanced formulas and dynamic arrays, making it incredibly easy to calculate complex running totals across multiple columns.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
2. Select the destination: Click the cell where you want the running total array to appear.
3. Apply the formula: Paste the WRAPCOLS and SCAN formula into the formula bar and press Enter to instantly calculate your column-by-column running totals.

Fully compatible with Microsoft Excel formulas, including complex nested arrays.Supports advanced dynamic functions for seamless data manipulation.Lightweight, fast, and completely free to use with a familiar user interface.

Frequently Asked Questions

Why is my dynamic array formula returning a #NAME? error?

This error typically occurs if your version of Excel does not support new dynamic array functions like SCAN or WRAPCOLS. You will need to upgrade your software or use the alternative SUM and OFFSET method.

Can I create a running total row-by-row instead of column-by-column?

Yes, you can modify the array formula to calculate horizontally by using WRAPROWS and TOROW instead of WRAPCOLS and TOCOL.

How do I prevent circular reference warnings when calculating running totals?

Ensure that the destination cell containing your formula is not located within the source data range you are summing. Always output the result in a separate column or on a different sheet.

Excel & Data Problems