'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Create Dynamic Account Balances from Excel Transactions
Question details
The user needs to extract a unique list of accounts from "From" and "To" transaction columns to calculate balances without relying on Power Query.
- Product
- Excel, Google Sheets
- Device & OS
- not provided
- Scenario
- Creating an automated, dynamic list of accounts from transaction records to compute running or final balances.
- Observed behavior
- The user is looking for an automated formula-based approach that accommodates modern Excel, handles table limitations where spill formulas aren't allowed, and maintains compatibility with older versions or Google Sheets.
Ensure your transaction data is formatted as an Excel Table (e.g., named "Table1") to allow dynamic range references, and verify if your spreadsheet software supports dynamic array functions like UNIQUE and VSTACK.
Use UNIQUE and VSTACK Formulas for Dynamic Arrays
For modern Excel versions supporting dynamic arrays, you can instantly extract a unique list of accounts from multiple columns using a single formula.
Select your transaction data range and press Ctrl + T to convert it into an Excel Table. Ensure it is named Table1 in the Table Design tab.
In a new blank cell outside of the table, type the following formula: =UNIQUE(VSTACK(Table1[From],Table1[To]))
Press Enter. The formula will automatically spill downwards, creating a complete list of unique accounts from both the From and To columns.
Workaround for Formulas Inside Excel Tables
Since Excel Tables do not support spilled array formulas, use a combination of INDEX and ROW to extract the unique accounts row by row.
Alternative Approaches for Google Sheets and Older Excel
When dynamic array functions like VSTACK are unavailable, you can use alternative formulas native to Google Sheets or helper columns in older Excel versions.
Use WPS Spreadsheet for Seamless Dynamic Arrays
WPS Spreadsheet fully supports modern dynamic array functions like UNIQUE and VSTACK, making it incredibly easy to extract unique account balances from transaction data without needing complex workarounds.
- 1. Open your Workbook: Launch WPS Spreadsheet and open your transaction workbook.
- 2. Format as Table: Select your transaction data and press Ctrl + T to format it as a table.
- 3. Input the Formula: Click an empty cell and type =UNIQUE(VSTACK(Table1[From],Table1[To])).
- 4. Get the Results: Press Enter to instantly generate a dynamic, spill-enabled list of your unique accounts.
Frequently Asked Questions
Why do I get a #SPILL! error when using the UNIQUE function?
A #SPILL! error occurs when the cells below your formula aren't completely blank or when you try to use dynamic arrays inside an Excel Table. Clear any text, spaces, or formatting in the spill range to allow the formula to expand, or move the formula outside of the table.
Can I use VSTACK in Excel 2016 or 2019?
No, VSTACK is a dynamic array function only available in Microsoft 365 and Excel 2021 or newer. For older versions, you must manually stack the data or use Power Query to append the columns.
How do I calculate the actual balance after getting the unique account list?
Once you have the unique list of accounts, use SUMIFS to calculate the balance. Assuming A2 is your unique account name, the formula would be: =SUMIFS(Table1[Amount], Table1[To], A2) - SUMIFS(Table1[Amount], Table1[From], A2).
Why can't I use dynamic array formulas inside an Excel Table?
Excel Tables require structured, predictable data layouts and do not support dynamic arrays that change size automatically. You must either place the formula outside the table or use the INDEX and ROW workaround to force a single result per row.
