Excel Expense Report Template Generator

About this template

An expense report is a claim: a dated list of what you spent on someone else's behalf, with a total at the bottom that somebody has to approve. This workbook produces one claim per file, with the per-line arithmetic already wired so the figure being approved is derived rather than retyped.

It fits an employee claiming back travel and meals, a contractor passing costs through to a client, or a small team that reimburses by spreadsheet because an expenses system would be more process than the volume justifies. One report covers one person and one period.

The layout puts the claimant in column A rows 3 to 7 and the department or approver in column C. The reference block in columns E and F carries a report number and the period start and end dates. Row 9 heads the table with Date, Category, Description, Qty, Unit cost and Amount, and twenty blank claim lines follow. Underneath, F30 subtotals every line and F31 is the Total.

Quantity and unit cost, rather than a single amount column, is a deliberate choice: it is what makes a mileage claim work - 120 in Qty and 0.45 in Unit cost - without a separate calculation off to the side. The date cells are formatted yyyy-mm-dd so a sorted report reads chronologically whatever locale the approver opens it in.

How to use it

1. Head the report - Your name in A4, the department or approver in C4, and the reporting period in F4 and F5. Those two cells are formatted as dates, so a claim covering a month sorts and files correctly alongside the others.
2. One row per receipt - Date in column A, a category in column B, and enough description in column C that the approver does not have to ask. Keeping one row per receipt, rather than lumping a trip together, is what makes a query answerable later.
3. Enter quantity and unit cost, not the total - A single receipt is quantity 1 and its value as the unit cost. Mileage is the distance as the quantity and your rate per mile or kilometre as the unit cost. Column F multiplies them the moment both are numbers.
4. Check the two rows at the bottom - F30 adds every claim line and F31 is the Total you are submitting. Generating with the tax line switched on inserts a rate cell and a Tax row between them, for reclaiming VAT or GST.
5. Add lines inside the block - Insert a row between the first and last claim line so SUM(F10:F29) grows with it, then copy the Amount formula down from the row above into the new row.
6. Number your receipts to match - Write the row number on each receipt or attachment before you scan them. It costs nothing and turns an approval question from a hunt into a lookup.

Formulas in this workbook

Every formula below is written into the workbook itself, exactly as shown. The cell references are the ones the default options produce; change the row or column counts and they shift to match.

• =IF(COUNT(D10,E10)=2,D10*E10,"") - The Amount on each claim line: quantity times unit cost, but only once both cells hold numbers. COUNT is what tests that, and the empty string keeps unused rows blank on the printed claim.
• =SUM(F10:F29) - The subtotal across all twenty claim lines. The empty strings on unused rows are ignored, so the figure is right from the first line you enter.
• =F30 - The Total. With no tax line it simply mirrors the subtotal, which keeps the Total in a fixed place on the sheet whether or not tax is included - switch the tax line on and this becomes the subtotal plus the calculated tax instead.

Customising it

• Turn the Category column into a dropdown: select B10:B29, then Data > Data Validation > List, and type your categories separated by commas as the source. Consistent categories are what make the next tip work.
• Add a per-category total below the report with =SUMIF($B$10:$B$29,"Travel",$F$10:$F$29), copied once per category, so the approver sees the split without sorting anything.
• Hold your mileage rate in a spare cell and point the unit cost at it, for example =$H$1, so a rate change is one edit rather than twenty.
• Highlight anything that needs a receipt: select F10:F29 and add a conditional format for values greater than your receipt threshold.
• Sort the finished claim by date before submitting - select A9:F29 and use Data > Sort with a header row, so the report reads in the order the spending happened.