How to create an accounts receivable ledger spreadsheet in Excel
Learn how to build a simple yet effective accounts receivable ledger in Excel with step-by-step instructions and formulas. Plus, discover how Sage AR Automation offers a smarter, real-time alternative for tracking your receivables and key metrics effortlessly.
Excel is a widely used application that can sometimes be confusing to use. Especially when one small mistake can leave you spending valuable time troubleshooting “#VALUE” messages. If you’re interested in a simple solution, we’ve created a step-by-step way to create your own accounts receivable (AR) ledger in Excel.
If you want a solution that is even better than Excel, try the free Sage AR Automation that will show you real-time aging and other AR KPIs in minutes.
Formula to create your own accounts receivable ledger in Excel
Please note that the following directions are only an example of how you can set up your ledger in Excel. Every business is different, so feel free to customize your accounts receivable spreadsheet accordingly.
Step 1: Set up key invoice columns
Set up your Excel sheet to include “Invoice Dates” in column A, “Invoice Numbers” in column B, and “Due Dates” in column C to structure your AR spreadsheet. Then, enter your corresponding information.
Step 2: Add total due and payment columns
Add a column for “Total Amount Due” in column E and add the corresponding information. Use Columns F–I for customer payments. If customers are on payment plans, you can add as many or as little payment columns as needed. In Column J, add “Balance Due” to track outstanding amounts.
Step 3: Calculate balance due
In cell J3, the first cell under the “Balance Due” column, add the following formula: =E3-SUM(F3:I3). This formula automatically finds the difference between the total amount due and what the customer has paid so far.
Step 4: Auto-populate the Excel formula
After the number in cell J3 has been populated, use Excel’s drag-and-drop feature to expand the small green square on the right side of the cell down to the very last customer account in your spreadsheet. This will auto-populate the formula for you, so you do not have to enter it in for every cell.
Step 5: Calculate total outstanding receivables
Below the last balance due cell, create a cell for “Outstanding Receivables“. For this cell, you will enter in the following formula: =SUM(J3:J11). This will add up all of your balances due, letting you know the total amount of receivables still owed to your business.
