Step 1: The columns
Start with five columns in row 1: Date, Category, Description, Amount and Reimbursable. One row per receipt. Keep descriptions specific ("Taxi, client visit, Leeds") so whoever approves it does not have to ask.
Step 2: Dropdowns so totals stay accurate
Category totals only work if everyone spells categories the same way. Select the Category column, then go to Data → Data Validation → List and enter:
Travel,Meals,Accommodation,Software,Stationery,OtherDo the same on the Reimbursable column with Yes,No. Now nobody can type "travel " with a trailing space and drop out of the total.
Step 3: Total each category with SUMIF
Below the table, list the categories and total each one. If your rows run from 2 to 100:
=SUMIF(B2:B100,"Travel",D2:D100)Repeat for Meals, Accommodation and the rest, changing only the category name. SUMIF reads: in column B, find "Travel", and add the matching amounts from column D.
Step 4: Separate what is owed back
Not everything on a report gets repaid: personal items, company-card spending already paid, or costs above the policy limit. Total them separately:
- To reimburse:
=SUMIF(E2:E100,"Yes",D2:D100) - Not reimbursable:
=SUMIF(E2:E100,"No",D2:D100) - Total claimed:
=SUM(D2:D100)
The first two should add up to the third. If they do not, a row has a blank or misspelled value in the Reimbursable column.
Step 5: Make it easy to approve
- Add the claimant's name, the period covered and the date submitted at the top.
- Number receipts and put the same number in the Description, so a scanned receipt can be matched to its row.
- Leave space at the bottom for the approver's name and date.
- Save as PDF to send, and keep the Excel file for your records.
Mileage on an expense report
For car travel, claim distance rather than fuel. Add a row with the miles or kilometres in the Description and calculate the amount as distance times your rate, for example =120*0.45. If you drive for work often, keep a separate mileage log and put one monthly total on the report.
Common problems
A category total shows 0
The category text in the table does not exactly match the text in the SUMIF. Extra spaces are the usual cause; the dropdown in step 2 prevents this.
Amounts will not add up
Amounts pasted from a banking app are often stored as text. Select the column and use Data → Text to Columns → Finish to turn them into numbers.
Frequently Asked Questions
What should an expense report include?
Date, category, description and amount for each expense, whether it is reimbursable, totals by category, the claimant's name, the period covered and space for approval.
How do I total expenses by category in Excel?
Use SUMIF: =SUMIF(category_range,"Travel",amount_range). Repeat it for each category.
Can I use this in Google Sheets?
Yes. SUM, SUMIF and dropdown lists all work the same way in Google Sheets.
Why is my SUMIF total wrong?
Usually the category text has extra spaces or different spelling, or the amounts are stored as text. Dropdowns fix the first, Text to Columns fixes the second.