The bottom of a receipt rarely has just one number. Subtotal, tax, tip, total, cash tendered, change: half a dozen amounts sit in a column, and picking the wrong one quietly throws off your budget or your club's books. Here's what each line means, which one to record, and how to let Excel check your work.
1. What the lines at the bottom mean
| Receipt line | What it is | Record it? |
|---|---|---|
| Subtotal | Items before tax (and before tip) | Usually no |
| Discount, Savings, Coupon | Taken off before the total | Already in the total |
| Tax, Sales Tax, VAT, GST | Tax on the taxable items | Usually no |
| Tip, Gratuity | Added by you, often written on a card slip | Yes, as part of what you paid |
| Total, Amount Due, Balance Due | What you were charged | Yes |
| Cash, Tendered, Change | Money handed over and handed back | No |
For a household budget or a club's books, record what you actually paid: the total, plus any tip you added afterwards. Subtotal and tax only split that total into parts. If you need the tax separately, for example to claim VAT back or for a business return, check your tax authority's guidance or ask an accountant, since the rules vary by country.
Watch out for Cash and Tendered. If you paid for a 32.40 purchase with a 50 note, the receipt shows Cash 50.00 and Change 17.60. Record 50.00 and you've overstated that expense by 17.60.
2. How the numbers relate
How tax appears depends on where the receipt is from:
- Tax added on top (most US and Canadian receipts):
Subtotal − Discount + Tax = Total - Tax included in prices (UK, EU, Australia and many others): the total already contains the VAT or GST, and the receipt just shows how much of it was tax. With UK standard-rate VAT at 20%, the VAT is one sixth of the total.
- Cash payments anywhere:
Tendered − Change = Total - Restaurant card slips:
Total + Tip = amount charged to your card
Stores round tax in slightly different ways, so a difference of a cent or so is normal and not an error.
3. Check a whole batch in Excel
Once your receipts are in a spreadsheet, add a check column and let a formula do the looking. For US-style receipts where tax is added on top:
| A Store | B Subtotal | C Tax | D Tip | E Total paid | F Check | |
|---|---|---|---|---|---|---|
| 2 | Grocery | 45.20 | 2.83 | 0.00 | 48.03 | OK |
| 3 | Diner | 32.00 | 2.56 | 6.00 | 40.56 | OK |
| 4 | Hardware | 18.75 | 1.17 | 0.00 | 18.92 | Check |
Put this in F2 and fill down:
=IF(ABS(B2+C2+D2-E2)<=0.01, "OK", "Check")
Anything off by more than a cent shows "Check". In the example, the hardware store total should be 19.92: the 9 was copied as an 8. Add conditional formatting to color the "Check" cells and they stand out even in a long list.
For VAT-inclusive receipts, you can check the VAT line against the total instead. At a 20% rate:
=IF(ABS(E2/6-C2)<=0.01, "OK", "Check")
This only works when every item was taxed at that one rate. Mixed baskets (UK groceries often include zero-rated food) won't match, so use the first formula for those.
4. The same idea when reading photos
The receipt tool puts only the total in the table, but while reading a photo it also looks at the subtotal, tax, tendered and change lines to see whether the total adds up. If it does, the total is treated as confirmed; if the numbers disagree, the total cell is marked yellow for you to check. When you see one, open the photo and run through the relationships above. The misread digit usually jumps out.
5. Common mistakes
- Recording the subtotal: you under-count every taxed purchase. Look for the last amount line, usually labelled Total or Amount Due.
- Forgetting the tip: the printed receipt is written before you tip. Use the card slip or your bank statement for the final amount.
- Counting the receipt and the card slip: some shops print both. Two rows with the same date and amount are worth a look; see How to find duplicate receipts in an Excel spreadsheet.
- Using the original after a return: subtract the refund receipt, or use the new total if the store reissued one.
Summary
Record what you actually paid, and use the subtotal and tax lines as a way to check that total. A single Subtotal + Tax + Tip = Total column catches most copying mistakes in seconds. For the different kinds of receipts and slips, see Receipts, card slips, invoices and e-receipts explained.