Excel for accounting: the formulas accountants use every week
Accounting software keeps the books, but the analysis still happens in Excel. Exports from the ledger, bank statements and invoice lists all end up in a spreadsheet. Practice the four jobs that come up most often on one small set of real-looking data.
In short: most accounting work in Excel rests on four skills. SUMIFS totals transactions by account and period, a short formula backs tax out of a gross amount, date math turns an invoice list into an aging report, and XLOOKUP reconciles two lists against each other.
Where Excel fits in accounting
Excel is not a general ledger. It has no audit trail, any cell can be overwritten without a record, and nothing stops two people from editing different copies. That is why the books themselves belong in accounting software, and why rules for record keeping and tax differ by country. This page does not give tax or bookkeeping advice; check those questions with your accountant or the tax authority.
Around the ledger, though, Excel is everywhere. Accountants use it to summarize exports, build schedules, check balances, prepare month-end reports and answer the questions the standard reports do not cover. Small business owners often keep a simple income and expense sheet next to their software to see where the money goes. That analysis work is what you practice here.
Excel accounting formulas and skills for accountants
- SUMIFS to total transactions by account, period, vendor or customer.
- Percentages for tax, discounts, margins and variances.
- Absolute references so a tax rate or a reporting date stays put when you fill down.
- Date functions for due dates, periods and month ends.
- XLOOKUP or VLOOKUP to match invoices, payments and account names.
- ROUND so cents add up the way the ledger does.
- PivotTables for a fast trial-balance style view of any export.
- Excel tables so new rows flow into every formula automatically.
The practice case: closing the first quarter
You keep the numbers for a small design studio. Expenses come out of the accounting system as a list with date, account and amount, and sales invoices sit in a second list. The owner wants to know what office supplies cost in February, how much tax is buried in a stack of receipts and which customers are late paying.
Step 1: Expenses by account and month
Individual transactions are too detailed for a report. What you need is the total for one account in one period. Real transactions carry a date rather than a month number, so you set the period with a start date and an end date.
Task A2:C9 lists the expenses of the first quarter. In G4, use SUMIFS to total what was spent on the account in G1 between the dates in G2 and G3. Take the account and both dates from those cells so the same formula works for any month.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Account | Amount $ | Account | Office supplies | ||
| 2 | 1/1/2026 | Rent | 950 | From | 2/1/2026 | ||
| 3 | 1/9/2026 | Office supplies | 84.5 | To | 2/28/2026 | ||
| 4 | 2/1/2026 | Rent | 950 | Total $ | |||
| 5 | 2/5/2026 | Office supplies | 129.9 | ||||
| 6 | 2/13/2026 | Postage | 42 | ||||
| 7 | 2/24/2026 | Office supplies | 37.8 | ||||
| 8 | 3/1/2026 | Rent | 950 | ||||
| 9 | 3/4/2026 | Office supplies | 61.2 |
Put each month's start and end date in its own row and the same formula fills a whole expense summary by account and month. You do not have to type month ends: =EOMONTH(G2,0) returns the last day of the month in G2.
Step 2: Backing tax out of a gross amount
Receipts in countries with VAT usually show the gross amount only. To record or analyze them you need net and tax separately. The classic mistake is to take the tax rate of the gross amount, which overstates the tax.
Task The receipts show gross amounts that include 20% VAT; the rate is in F1. In C2, calculate how much VAT is included in the gross amount. The formula is filled down to C7 and must keep pointing at F1.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Receipt | Gross $ | VAT $ | VAT rate | 0.2 | |
| 2 | 1101 | 120 | ||||
| 3 | 1102 | 240 | ||||
| 4 | 1103 | 60 | ||||
| 5 | 1104 | 1200 | ||||
| 6 | 1105 | 360 | ||||
| 7 | 1106 | 48 |
The rule to remember: net = gross / (1 + rate). US sales tax works the other way around, because it is added on top of the price. There the tax is simply =B2*$F$1 and the total is =B2*(1+$F$1). Wrap the result in =ROUND(…,2) before you post or compare it, so the cents match.
Step 3: An aging report for accounts receivable
A list of unpaid invoices becomes useful once you can see how long each one has been overdue. For that you subtract the due date from today's date.
Task For the accounts receivable aging list you need the number of days each invoice is overdue as of the date in F1. If an invoice is not due yet, show 0 instead of a negative number. Write the formula in D2; it is filled down to D7.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Invoice | Customer | Due date | Days overdue | As of | 3/19/2026 |
| 2 | 2401 | Becker Ltd | 2/18/2026 | |||
| 3 | 2402 | Northlight Inc | 3/9/2026 | |||
| 4 | 2403 | Hansen Group | 3/24/2026 | |||
| 5 | 2404 | Weber Logistics | 2/27/2026 | |||
| 6 | 2405 | Shaw Partners | 3/31/2026 | |||
| 7 | 2406 | Becker Ltd | 3/15/2026 |
In your own file, put =TODAY() in the as-of cell and the list is current every morning. From the days overdue you can sort invoices into the usual buckets with IFS, for example =IFS(D2=0,"Current",D2<=30,"1-30",D2<=60,"31-60",TRUE,"61+"), and a PivotTable then totals the open amount per bucket and customer. A conditional formatting rule like =$D2>60 makes the oldest items stand out.
Step 4: Reconciliation with XLOOKUP
At month end you check which invoices were actually paid. Export the bank transactions to a sheet named Bank, then fetch the amount received for each invoice number:
=XLOOKUP(A2,Bank!B2:B200,Bank!C2:C200,0)
Next to it, =D2-E2 shows the difference between invoice and payment. Zero means settled, the full amount means unpaid, and anything in between is a short payment or an early payment discount worth a closer look. XLOOKUP needs Excel 2021 or Microsoft 365. With VLOOKUP the formula is =VLOOKUP(A2,Bank!B2:C200,2,FALSE), and the FALSE at the end is not optional: without it Excel may match the wrong invoice.
Common accounting spreadsheets
| Spreadsheet | Purpose | Excel tools |
|---|---|---|
| Expense summary | Spending by account and month | SUMIFS, PivotTable |
| Receipt log with tax | Net, tax and gross for each receipt | Percentage formulas, absolute references, ROUND |
| AR aging report | Who owes how much and for how long | Date math, TODAY, IFS, conditional formatting |
| Bank reconciliation | Invoices or ledger entries against the bank | XLOOKUP, VLOOKUP, IFERROR |
| Accrual and prepaid schedules | Spreading an amount over months | EOMONTH, simple division, absolute references |
Habits that keep accounting sheets reliable
One row per transaction
Date, reference, account and amount in separate columns, one transaction per row. No subtotals inside the data and no blank rows. Totals go beside the data or on a separate sheet.
Real dates, not text
A date typed as text such as "Feb 2026" breaks comparisons like ">="&G2 and sorts in the wrong order. Check that dates align to the right in the cell, which is how Excel shows numbers.
A control total
The total of your summary should equal the total of the export. A cell that subtracts one from the other and has to show zero catches a missing account at once.
Accounts from a fixed list
"Office supplies" in one row and "Office Supply" in the next means part of the spend drops out of SUMIFS. A drop-down list with the allowed account names prevents it.
FAQ
Is Excel enough for small business accounting?
It works well for tracking, summarizing and checking numbers. For the books themselves, accounting software gives you an audit trail, locked periods and reports that a plain workbook does not. What your business is required to keep depends on where you are, so ask your accountant.
What Excel formulas do accountants use most?
SUMIFS, XLOOKUP or VLOOKUP, IF and IFS, ROUND, EOMONTH and other date functions, plus PivotTables. Together they cover summaries, reconciliations, schedules and aging reports.
How do I calculate VAT from a gross amount in Excel?
Net is =B2/(1+rate), for example =B2/1.2 at 20% VAT. The tax included is the gross minus the net, =B2-B2/1.2. Multiplying the gross by the rate gives too much tax.
How do I calculate days past due in Excel?
Use =TODAY()-C2 with the due date in C2. To show 0 for invoices that are not due yet, write =MAX(0,TODAY()-C2).