Cellwise Start free

Excel guide · 5 min read

How to make an expense report in Excel

Quick answerGive each receipt one row: date, merchant, category (from a drop-down), amount, currency and receipt Yes/No. Look up the exchange rate for each currency from a small rate table and round the claim to cents with =ROUND(amount*rate,2). Then total by category with SUMIFS, flag lines over the policy limit by comparing the converted claim with the limit, count missing receipts with COUNTIFS, and add mileage as miles x your rate per mile.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1DateMerchantCategoryAmountCurrencyRateClaim (USD)ReceiptCurrencyUSD per unit
2Oct 12, 2026Delta Air LinesTransport612.4USD1612.4YesUSD1
3Oct 13, 2026Hotel AvenidaLodging189EUR1.16219.24YesEUR1.16
4Oct 14, 2026Hotel AvenidaLodging189EUR1.16219.24YesGBP1.34
5Oct 14, 2026Cervejaria RamiroMeals74.5EUR1.1686.42Yes
6Oct 15, 2026UberTransport18.2EUR1.1621.11No
7Oct 15, 2026Pret A MangerMeals12.85GBP1.3417.22Yes
8Oct 16, 2026StaplesSupplies46.99USD146.99No
9Oct 16, 2026DishoomMeals52GBP1.3469.68Yes

Download this sheet as an Excel practice file (.xlsx)

Set up the columns and the input tables

The report above is a four-day trip with receipts in three currencies. Columns A to E and H are typed: date, merchant, category, the amount on the receipt, its currency, and whether you have the receipt. Columns F (rate) and G (claim in your home currency) are formulas, so nobody types a converted number by hand.

Everything the company decides lives in small tables to the right, not inside formulas: exchange rates in K:L, miles and the rate per mile in N2:O2, and the limit per item for each category in Q:R. The numbers in them are examples. Use the rates your expense policy tells you to (often the rate on your card statement, or a published rate for the day of the expense) and the per-mile rate your employer sets.

  1. Select C2:C50 (room for more lines), then Data > Data Validation.
  2. Under Allow pick List, and in Source enter =$Q$2:$Q$5 (the category column of the limits table). Click OK.
  3. Do the same for E2:E50 with =$K$2:$K$4 so only currencies in the rate table can be chosen, and for H2:H50 with the source Yes,No typed in directly.

Why a drop-down instead of typing?

SUMIFS matches text exactly. "Meal", "meals " (with a trailing space) and "Food" each form their own category, so the totals quietly miss them. A list limited to the categories in Q2:Q5 means every line lands in a total.

Convert foreign receipts at your rate

F2 looks up the receipt's currency in the rate table: =XLOOKUP(E2,$K$2:$K$4,$L$2:$L$4). USD is in the table with a rate of 1, so home-currency lines need no special case. In Excel 2019 or earlier, use =VLOOKUP(E2,$K$2:$L$4,2,FALSE) instead.

G2 multiplies and rounds to cents: =ROUND(D2*F2,2). Round on each line, not only the total. Finance pays line by line, and unrounded lines can make your total differ from theirs by a cent or two.

=ROUND(D5*F5,2)

Result: 86.42

86.42: the 74.50 EUR dinner at 1.16 dollars per euro. Over the whole report, the converted claims add up to 1,292.30.

Can I just add up the Amount column?

Not when currencies are mixed. =SUMIFS(D2:D9,C2:C9,"Meals") returns 139.35, which adds euros to pounds and means nothing. Always total the converted Claim column.

=SUMIFS(D2:D9,C2:C9,"Meals")

Result: 139.35

139.35, a meaningless mix of 74.50 EUR, 12.85 GBP and 52.00 GBP. The right meals total, from column G, is 173.32.

Total by category with SUMIFS

Approvers want the claim split by category, and so does whoever books it. One SUMIFS per category does it. In Excel 365 you can spill the whole summary at once by giving SUMIFS the list of categories as its criteria; HSTACK puts the names next to the totals.

=HSTACK(Q2:Q5,SUMIFS(G2:G9,C2:C9,Q2:Q5))

Result: spills 4 rows × 2 columns

Transport633.51
Lodging438.48
Meals173.32
Supplies46.99

Transport 633.51, Lodging 438.48, Meals 173.32, Supplies 46.99. In older versions, put =SUMIFS($G$2:$G$9,$C$2:$C$9,Q2) next to Transport and fill down.

How do I check nothing is missing from the summary?

The category totals should add up to =SUM(G2:G9), 1,292.30 here. If they don't, a line has a category that isn't in the list (usually typed before the drop-down was added).

Flag lines over the policy limit

Policy limits are in your home currency, so compare the converted claim in column G, not the receipt amount. This is the gotcha in the sample: the Lisbon dinner was 74.50 EUR, which looks under a 75 limit, but it's 86.42 USD, which isn't. Comparing D5 with the limit says FALSE; comparing G5 says TRUE.

  1. To colour the rows instead: select A2:H9, then Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
  2. Enter =$G2>VLOOKUP($C2,$Q$2:$R$5,2,FALSE). The $ before G and C keeps every cell in the row looking at the same claim and category.
  3. Click Format, pick a light orange fill, and click OK twice.
=FILTER(B2:B9,G2:G9>XLOOKUP(C2:C9,Q2:Q5,R2:R5))

Result: Cervejaria Ramiro

Cervejaria Ramiro, the only line over its limit, 11.42 over the 75.00 meals limit.

Missing receipts and mileage

Count lines without a receipt with COUNTIFS, and total their value with SUMIFS, so the approver sees both how many and how much. Here the Uber ride and the Staples order have no receipt: 2 lines worth 68.10.

Mileage for driving your own car is miles x your rate per mile, rounded to cents: =ROUND(N2*O2,2). The 0.60 in O2 is a placeholder: put in the per-mile rate your company sets (many US employers follow the IRS standard rate, which changes every year, so check it each January). For several trips, give mileage its own small table of date, from, to and miles, and total the miles.

Prefer a fill-in form? The free expense report template has mileage, category limits and receipt checks set up. For the cash tin in the office rather than your own spending, see the petty cash log.

=COUNTIFS(H2:H9,"No")

Result: 2

2 lines without a receipt. =SUMIFS(G2:G9,H2:H9,"No") gives their value: 68.10.

What's the total claim?

The converted lines plus mileage. If you were given a cash advance before the trip, subtract it in one more cell: a positive result is owed to you, a negative one is what you hand back.

=SUM(G2:G9)+ROUND(N2*O2,2)

Result: 1402.7

1,402.70: 1,292.30 of receipts plus 110.40 for 184 miles at 0.60.

Common mistakes

Comparing the foreign amount with the limit

A 74.50 EUR dinner looks under a 75 USD limit until it's converted. Compare the Claim column (G), which is in the same currency as the limits.

Typing exchange rates into each formula

=D3*1.16 hides the rate and needs editing on every line when it changes. Keep rates in a table and look them up.

Rounding only the total

Round each line with ROUND(...,2) so your total matches what finance adds up from the lines.

Hard-coding a mileage rate as if it were official

Rates differ by employer and change over time. Keep the rate in an input cell and update it from your policy.

FAQ

Does Excel have an expense report template?

Yes, search for "expense report" under File > New, and Cellwise has a free expense report template with category limits and receipt checks built in. Building your own, as above, is worth it when your policy has its own categories or currencies.

How do I convert currency in an expense report?

Keep a rate table (currency and rate to your home currency), look up each line's rate with XLOOKUP or VLOOKUP, and multiply: =ROUND(amount*rate,2). Use the rate your policy names, often the one on your card statement.

How do I add a drop-down for expense categories?

Select the category column, Data > Data Validation > Allow: List, and point the source at your list of categories. Typed entries that aren't in the list are then rejected.

How do I total expenses by category?

=SUMIFS(claim column, category column, "Meals") for one category, or give SUMIFS the whole category list as criteria in Excel 365 to spill every total at once.

Functions used

Related guides

Updated