Formula Errors

How to Create an Excel Payment Tracking Table with Formulas

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to construct a spreadsheet that logs payment dates and dynamically updates a running total balance by subtracting each logged payment amount.

Product: Excel
Device & OS: not provided
Scenario: Setting up a customized financial table to track outgoing payments and calculate remaining balances over time.
Observed behavior: A payment table needs to be established with accurate spreadsheet formulas to subtract payments from a starting total and display the current running balance without manual calculation.

Before you start

Determine your starting balance and decide on a clear table layout with dedicated columns for Date, Description, Payment Amount, and Running Total before writing your formulas.

Solution 1Recommended

Set Up a Standard Payment Tracking Table with a Running Total

Create a simple and effective table using basic subtraction formulas to maintain a running balance every time a new payment is entered.

To ensure the formula works seamlessly, the data structure needs to be organized consistently. By creating a fixed starting balance and dynamically subtracting new entries, you can track expenses effortlessly.

Create the Table Headers

Click on Row 1 and enter your column headers. For example: type 'Date' in A1, 'Description' in B1, 'Payment Amount' in C1, and 'Running Balance' in D1.

Enter the Starting Balance

In cell D2, input your initial total amount or starting balance (e.g., 5000). You can leave the other cells in row 2 blank or label it 'Starting Balance' in B2.

Input the First Payment Details

In row 3, enter the payment date in A3, the payment description in B3, and the payment amount in C3.

Apply the Subtraction Formula

Click on cell D3. Type the formula =D2-C3 and press Enter. This formula subtracts the first payment amount from your starting balance.

Copy the Formula Down the Column

Select cell D3, click and hold the small square at the bottom-right corner of the cell (the Fill Handle), and drag it down the column. This applies the running total formula to all future payment rows.

Pro Tip: Select your entire table, go to the 'Home' tab, and click 'Format as Table'. This ensures that as you add new payment rows at the bottom, your formulas and formatting are automatically extended.

Easily Track Payments with WPS Spreadsheet

WPS Spreadsheet provides powerful formula calculation and data formatting tools, making it incredibly easy to set up payment trackers, budgets, and financial logs.

1. Open WPS Spreadsheet: Launch WPS Office on your device and click on 'Spreadsheet' in the main dashboard to create a new blank workbook.
2. Set Up Your Layout or Use a Template: Search for 'Payment Tracker' in the Template library, or manually set up your headers for Date, Payment, and Balance.
3. Apply Formulas and Save: Input your running total formula (e.g., =D2-C3). Save your document locally or directly to WPS Cloud to access your payment tracker anywhere.

100% compatible with Microsoft Excel (.xlsx) file formats and formula syntax.Provides a variety of free, built-in financial templates for payment and budget tracking.Lightweight and fast, offering smooth scrolling and calculation even with large data sets.Multi-platform support allows you to track your payments seamlessly on PC, Mac, and mobile devices.

microsoft office alternative - wps office

Frequently Asked Questions

How do I prevent negative or identical balances from showing in empty rows?

You can use an IF statement to hide the balance if the payment cell is empty. Instead of using =D2-C3, click the balance cell and input the formula =IF(C3="","",D2-C3). Drag this down, and the balance column will remain blank until you enter a new payment.

Can I automatically format the payment and balance columns to show currency symbols?

Yes. Highlight the columns you want to format (e.g., Column C and D). Right-click and select 'Format Cells'. Go to the 'Number' tab, choose 'Currency' or 'Accounting', select your preferred symbol, and click OK.

Why is my running total formula showing a #VALUE! error?

This error typically occurs if text is entered into a cell that the formula expects to be a number. Check the previous balance cell and the current payment cell to ensure they contain strictly numerical data, without spaces or typed text characters.

Excel & Data Problems