Function Problems

How to Automatically Populate Excel Category Tables from a Receipts Table

Tauseeq Magsi Sep 28, 2026 869 views

Question details

The user needs to automatically distribute entries from a primary receipts table into separate, category-specific tables (such as Income or Bills) while maintaining a running total for each category.

How to Automatically Populate Excel Category Tables from a Primary Receipts Table

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Organizing financial data by automatically sorting a master list of receipts into separate category sheets or tables, and dynamically calculating running totals for those categorized entries.
Observed behavior: Receipts entered into the primary table must seamlessly filter into their respective category tables, and running total formulas need to update accordingly.

Before you start

Before applying formulas, ensure your primary receipts data is formatted as an official Table (press Ctrl+T). This ensures that any new receipts you add will automatically update your filtered category tables without needing to adjust formula ranges.

Solution 1Recommended

Use the FILTER Function to Auto-Populate Category Tables

The dynamic FILTER function is the most efficient way to automatically extract records from a master table based on specific criteria like 'Income' or 'Bills'.

Dynamic array formulas like FILTER allow you to extract data without relying on complex, resource-heavy INDEX and MATCH combinations. Note that you cannot use dynamic arrays inside an official Excel Table, so the destination area should be a standard data range.

Format Master Data as a Table

Select your entire primary receipts data range, go to the Insert tab, and click Table (or press Ctrl+T). Name this table 'MasterReceipts' in the Table Design tab.

Apply the FILTER Formula

Navigate to the sheet where you want your 'Bills' category to appear. In the top-left cell of your destination range, enter the formula: =FILTER(MasterReceipts, MasterReceipts[Category]="Bills", "No Data"). This will automatically spill all matching rows.

Calculate the Running Total

In the column immediately to the right of your spilled data, enter a SUM formula with an expanding range. For example, if your amounts are in column C, enter =SUM($C$2:C2) in cell D2, and drag the fill handle down to calculate the running total for each row.

Use the FILTER Function to Auto-Populate Category Tables

Dynamic Updates: As you add new rows to the 'MasterReceipts' table, the FILTER formula will automatically expand and update your category lists.

Solution 2

Use Pivot Tables for Categorization and Running Totals

If you want a formula-free solution that handles both categorization and running totals automatically, Pivot Tables are an excellent choice.

Effortless Financial Management

Automate Data Organization and Running Totals with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER as well as highly customizable Pivot Tables, giving you multiple ways to automate your receipts tracking without any hassle.

1. Import Your Master Data: Open your receipts workbook in WPS Spreadsheet and verify your main data is organized in columns.
2. Convert to a Dynamic Table: Highlight the master data range and press Ctrl+L to convert it into a dynamic table.
3. Use the FILTER Function: On a new worksheet, type =FILTER(Table1, Table1[Category]="Income") to automatically extract all income-related receipts.
4. Apply Running Totals: Create an adjacent column and use =SUM($C$2:C2) to track the running total for your freshly categorized data.

Fully compatible with Microsoft Excel file formats (XLSX) and table structuresSupports modern dynamic array formulas including FILTER for auto-populating dataIntuitive Pivot Table features for effortless categorization and running totalsFree, lightweight, and perfect for organizing daily financial records

Frequently Asked Questions

Why is my FILTER function returning a #CALC! or #NAME? error?

A #NAME? error occurs if your spreadsheet software version does not support dynamic array formulas like FILTER. Ensure you are using an updated version of WPS Spreadsheet or Excel 365. A #CALC! error typically means the formula found no matching records based on your criteria (e.g., no receipts matched the 'Bills' category).

How do I calculate a running total next to dynamic array data without dragging the formula?

To make running totals expand automatically with spilled array data, you can use the SCAN and LAMBDA functions (if supported) like this: =SCAN(0, C2# , LAMBDA(a,b, a+b)), where C2# represents your spilled amounts.

Can I filter my receipts table by multiple categories at once?

Yes. You can add multiple conditions in the FILTER formula by multiplying them for an 'AND' logic or adding them for an 'OR' logic. For example, to filter for either 'Income' or 'Bills', use: =FILTER(MasterReceipts, (MasterReceipts[Category]="Income")+(MasterReceipts[Category]="Bills")).

Will my category tables update automatically when I close and reopen the workbook?

Yes, as long as your formulas are set up correctly and your workbook's calculation mode is set to 'Automatic' (which is the default setting in WPS Spreadsheet and Excel). Any new data entered into the master table will immediately reflect in the corresponding category tables.

Excel & Data Problems