TL;DR:
List each expense as one row with date, vendor, category, amount and a receipt marker.
Use SUMIF to total each category and SUM for the grand total, then check that they agree.
Match every receipt and note the submission deadline, which your employer sets.
It is the last week of the month and you have a pile of receipts, a card statement and a deadline you half remember. Reports written from memory at the last minute are where mistakes and missing receipts creep in.
A plain spreadsheet is enough to do this well. This article builds the report in layers, and the formulas work in both Excel and Google Sheets.
Gather what you need
The receipts or photos of them, for the month.
Your card or bank statement, as a cross-check for anything you forgot.
Your employer's expense policy: categories, limits and what counts. Use their rules, since they differ.
The submission deadline and how to submit.
Keep receipts as you go: a photo in one folder named for the month saves a search later.
Build the report step by step
Set up columns: Row 1 headers: Date, Vendor, Category, Amount, Receipt. Use one row per expense.
Enter each item: Fill in the real date, vendor and amount from the receipt. Use your employer's category names exactly, and mark Receipt as Yes when the file is attached.
Total by category: On the same sheet or a new tab, list each category once and use SUMIF to total it.
Check and submit: Compare the grand total with your statement, confirm every row has a receipt, and submit before the deadline.
Worked example: an expense report with receipts
Say these are your month's items. They are invented for the example.
Date | Vendor | Category | Amount | Receipt |
|---|---|---|---|---|
Nov 2 | Taxi | Travel | 18.40 | Yes |
Nov 5 | Team lunch | Meals | 64.25 | Yes |
Nov 9 | Design app | Software | 29.00 | Yes |
Nov 12 | Train | Travel | 42.50 | Yes |
Nov 16 | Print shop | Office | 12.35 | Yes |
Nov 20 | Taxi | Travel | 22.10 | No |
To total the Travel category, use SUMIF with the category column first, then the match, then the amounts:
=SUMIF(C2:C7,"Travel",D2:D7)The result is 83.00, because 18.40 + 42.50 + 22.10 = 83.00. Meals is 64.25, Software 29.00 and Office 12.35. The grand total is:
=SUM(D2:D7)It gives 188.60. Check: 83.00 + 64.25 + 29.00 + 12.35 = 188.60, so the category totals and the grand total agree. If they did not, a category would be misspelled in one row, for example "Travl", and SUMIF would skip it.
Row 7 is marked No for receipt. That item needs a receipt, a bank record or whatever your employer accepts, before you submit. Do not leave it for the finance team to chase.
Match receipts to the statement
Go down your card statement and tick each line that is a work expense against a row in your sheet. Anything on the statement that is not in your sheet is either personal or forgotten. Anything in the sheet that is not on the statement may be cash or a different card.
Deadlines and policy
Your employer sets the deadline, the limits and which expenses are allowed, so read the policy rather than guess. If you are unsure whether something qualifies, ask before you buy. Never alter a receipt or amount.
Mistakes to avoid
Waiting until the last day of the month.
Category names that do not match the policy.
Typing amounts as text, which SUM ignores. If a number is aligned left, it may be text.
Missing receipts.
Mixing personal and work items in one report.
Handling foreign currency, mileage and cash
If you paid in another currency, record the amount on the receipt and the amount your card charged, and follow your employer's rule for which one to report. For mileage or cash, ask what proof they accept. These are policy questions, not spreadsheet ones.
Add a Notes column for the business reason, such as who the lunch was with. It takes ten seconds while you remember and saves a question later. Keep the file with a clear name, such as the month and your name.
A weekly habit that makes month end easy
Spend five minutes each Friday adding that week's items to the sheet and saving the receipts. By the end of the month the report is nearly done, and you can use the last session just to check totals against your statement.
If you manage expenses for others, a shared template with fixed category names helps everyone report the same way and keeps your SUMIF totals dependable.
Last, put yourself in the approver's seat and reread the report. Is the purpose of each line clear, and does each amount agree with its receipt? If both answers are yes, queries are unlikely.
Doing it faster
Keep a running sheet through the month instead of one big session at the end. If a formula behaves oddly, DuckyHelper can look at your screen and talk through it when you ask out loud.
Frequently asked questions
Should I use a template from my employer?
Yes. If they have a form or system, use it. The sheet here helps you prepare and check the numbers.
Why does my total not match the statement?
Look for a missing row, a typo in the amount or a category spelled differently. Subtract the sheet total from the statement total to find the gap, then look for an amount that equals it.
Get started
