Function Problems

How to Create Dynamic Account Balances in Excel or Google Sheets

Steve K Sep 28, 2026 870 views

Question details

The user is looking for a formula-based method to calculate dynamic account balances and generate a unique account list from transaction data without using Power Query.

How to Create Dynamic Account Balances in Excel or Google Sheets

Product: Excel / Google Sheets
Device & OS: not provided
Scenario: Generating a unique list of accounts from multiple transaction columns (From and To) and calculating their ongoing balances.
Observed behavior: The user wants a dynamic solution using functions because array formulas cannot be directly placed inside an Excel Table without encountering issues or requiring workarounds.

Before you start

Ensure your transaction data is well-organized with clear 'From', 'To', and 'Amount' columns, and format your data range as a Table for easier and more dynamic formula referencing.

Solution 1Recommended

Use Dynamic Arrays outside of Excel Tables

Use modern dynamic array functions like UNIQUE and VSTACK to automatically extract a distinct list of accounts and calculate their balances.

This is the most efficient formula-based alternative to Power Query. Since dynamic array formulas like UNIQUE cannot spill inside an Excel Table, you must place these formulas in a standard range (outside of any formatted table).

Format data as a Table

Select your source transaction data, press Ctrl + T, and ensure it is formatted as an Excel Table (e.g., named 'Table1'). This ensures your references automatically update as new data is added.

Extract the unique account list

Click on an empty cell outside of your table. Enter the formula =UNIQUE(VSTACK(Table1[From], Table1[To])) and press Enter. This will stack the 'From' and 'To' columns and output a unique, deduplicated list of all accounts.

Calculate the dynamic balances

In the column adjacent to your spilled unique list (e.g., cell D2), use the SUMIFS function to calculate the balance. A standard formula would add incoming amounts and subtract outgoing amounts: =SUMIFS(Table1[Amount], Table1[To], C2#) - SUMIFS(Table1[Amount], Table1[From], C2#).

Use Dynamic Arrays outside of Excel Tables

Spill Operator Usage: Notice the '#' symbol after C2 in the SUMIFS formula. This is the spill operator, which tells Excel to apply the formula to the entire dynamic range generated by the UNIQUE function.

Solution 2

Use INDEX workaround inside Excel Tables

If you absolutely must place your resulting account list inside another formatted Excel Table, use this workaround to bypass the dynamic array limitation.

Advanced Spreadsheet Features

Calculate Dynamic Balances Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas including UNIQUE, VSTACK, and SUMIFS. You can effortlessly manage complex accounting transactions and generate dynamic reports without installing heavy add-ins.

1. Open your transaction file: Launch WPS Office and open your .xlsx file containing the transaction records.
2. Apply the UNIQUE formula: Select an empty cell and enter the formula =UNIQUE(VSTACK(Table1[From], Table1[To])) to generate your account list.
3. Use SUMIFS for balances: In the adjacent cell, input the SUMIFS formula referencing the spilled range (using the # operator) to automatically calculate balances.

Full compatibility with Microsoft Excel (.xlsx) formats and standard syntaxNative support for modern dynamic array formulas like UNIQUE and VSTACKLightweight, fast, and totally free alternative for financial data processingSeamless integration with comprehensive data analysis tools

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a #SPILL! error when using the UNIQUE function?

A #SPILL! error occurs when the formula attempts to display multiple results, but the required adjacent cells are not empty. Ensure that there is enough blank space below the formula to accommodate the extracted list of accounts. Additionally, Excel Tables do not support spilled arrays.

Can I use the VSTACK function in older versions of Excel?

No, VSTACK and dynamic array functions like UNIQUE are only available in Microsoft 365, Excel 2021, and modern spreadsheet alternatives like WPS Office. For older versions, you would need to use complex INDEX/MATCH arrays or Power Query.

How do I calculate a running balance instead of a total balance?

To calculate a running balance, use a SUMIFS formula with mixed references. Set the starting row of your range as absolute (e.g., $A$2) and the ending row as relative (e.g., A2). This tells the formula to sum all transaction amounts from the top of your data up to the current row.

Excel & Data Problems