To turn an expense report PDF to Excel, extract only the pages with the expense table, one row per line item with date, description, category and amount. Repeat the report number and employee on every row, rebuild mileage amounts from miles times rate, skip the scanned receipt pages, and check that the lines match the report total.
Expense report line items as spreadsheet rows
An expense report has a summary at the top (employee, report number, period, purpose, approver) and a table of expense lines below. In Excel, each expense line becomes a row and the summary fields become columns copied onto every row. That flat shape lets you combine reports from many people and months in one sheet.
Columns for each expense line
A layout that works for most templates:
- Report no, as text, so leading zeros survive.
- Employee, as printed on the report.
- Date, the date the expense happened, not the date the report was submitted.
- Merchant or description.
- Category, such as lodging, meals, travel or mileage.
- Amount, in the reporting currency.
- Original amount and original currency, if the expense was in another currency.
- Billable or project code, if the report has one.
- Receipt, a yes or no, or the receipt's page number in the PDF.
Report fields that live once
Some fields belong to the report rather than any line: the total claimed, any cash advance, and the amount due to the employee. Keep these in a second sheet with one row per report. The line sheet then has nothing but expense lines, and the report sheet is what you check the lines against.
Dates written in another format
Expense templates come from different software and different countries. One prints 09/08/2026, another 08.09.2026. Before you combine reports, confirm the date order in each template. A date such as 09/08 is valid in both orders, so Excel will not warn you; it will just file the expense in the wrong month.
Expense categories across many expense reports
Expense categories are the labels each line carries: lodging, airfare, meals, mileage, office supplies. Different templates and employees use different wording for the same thing. Map every printed category to one list of your own with a lookup table, so "Hotel", "Accommodation" and "Lodging" all total together.
A category map
Keep a two-column table: printed category, your category. Fill the Category column with =XLOOKUP(E2, Map[Printed], Map[Mine], "UNMAPPED"). Filter for UNMAPPED after each new batch of reports. Each one is a new wording to add to the map, and after a few months, new wordings become rare.
Categories left blank
Some employees leave the category empty and rely on the description. Do not guess silently. Give those rows a category of "to review", and fill them from the description when you have the receipt in front of you. A wrong guess in a meals versus entertainment split can matter more than an empty cell.
Similar layouts elsewhere
Expense reports are one of many reports where a header repeats over a list of lines. Property managers see the same structure in a rent roll, and turning a rent roll PDF into unit, tenant and rent rows uses the same flat-row approach for a very different document.
How should mileage rows on an expense report be converted?
A mileage row records a trip in a personal vehicle: miles driven and a rate per mile. Convert miles and rate into their own columns, then compute the amount as miles times rate in Excel. A mismatch with the printed amount means a misread digit, a wrong rate, or a rounding rule to note.
Keep miles and rate as numbers
The printed line often reads like "Client visit, 120 mi @ 0.76". Split that into Miles (120) and Rate (0.76), both as numbers, and keep the trip description separate. Then =ROUND(Miles*Rate,2) gives the amount. If the extraction left miles and rate inside the description, TEXTBEFORE and TEXTAFTER on "mi" and "@" can pull them out.
The rate can change during the year
If your company policy reimburses at the IRS business standard mileage rate, watch the date of each trip. The IRS standard mileage rates page lists 70 cents a mile for 2025, and for 2026 it lists 72.5 cents from January 1 to June 30 and 76 cents from July 1 to December 31. A sheet that applies one rate to the whole of 2026 will be wrong for half the year. Look up the rate by trip date, not by report date.
Mileage versus fuel
Mileage pays for the use of a personal car. Fuel bought for a company vehicle is a different expense, usually on a fuel card. If your business has both, keep them as separate categories. Converting a fuel card statement into vehicles, gallons and cost covers the fuel side, and comparing the two by date can catch a trip claimed as mileage and fuel at once.
Receipt pages attached to an expense report PDF
An expense report PDF exported from expense software often includes the receipts: scanned paper receipts and photos, one or more per page, after the expense table. Those pages are evidence, not data. Extract only the pages that hold the expense table, and record which receipt page backs each line.
Finding the table pages
The table pages are usually first, followed by a run of image pages. Open the PDF, note the last page of the table, and limit the extraction to that page range. Running text recognition on forty receipt photos takes time and fills your sheet with random rows from restaurant bills.
When you do want receipt data
Sometimes the receipt is the only place a detail appears, such as tax on a hotel bill or the attendees at a meal. Extract that one receipt on its own, into a separate sheet, and link it by report number and line. Keep it out of the main line table.
Check the result before you rely on it
After extraction, look at the output before any formulas: right number of rows, no receipt text mixed in, amounts in the amount column. What to check in a converted Excel file before you download it gives a short list that catches most extraction problems early.
Do the expense lines add up to the expense report total?
The expense lines should add up to the report total, and the total less any cash advance should equal the amount due to the employee. Check both for every report with SUMIFS on the report number. A gap usually means a line missed at a page break, a stray receipt row, or a personal item left out of the total.
Worked example: one report
The report below is an example. Report ER-0912, September 2026, with a cash advance of 200.00.
| Report no | Date | Description | Category | Miles | Rate | Amount |
|---|---|---|---|---|---|---|
| ER-0912 | 2026-09-08 | Hotel, 1 night | Lodging | 164.00 | ||
| ER-0912 | 2026-09-08 | Dinner with client | Meals | 58.40 | ||
| ER-0912 | 2026-09-09 | Taxi to airport | Travel | 32.60 | ||
| ER-0912 | 2026-09-09 | Client site visit | Mileage | 120 | 0.76 | 91.20 |
The lines sum to 346.20. The report prints a total of 346.20, an advance of 200.00, and 146.20 due to the employee. The check is three cells on the report sheet: =SUMIFS(Lines[Amount], Lines[Report no], A2) for the total, then that total minus the advance, then the difference from the printed amount due, which must be zero. The 0.76 rate is used here as an example of a July to December 2026 trip.
Personal items on a business bill
A hotel bill can include a personal charge the employee pays back, such as a film or a minibar. Some templates show it as a negative line; others reduce the hotel line. Either is fine as long as the total ties. If the printed total is lower than your line sum by a round amount, look for a personal deduction that was printed in a note instead of a line.
A short checklist per report
- Line count matches the printed table.
- Every row has a date inside the report period.
- Mileage amounts equal miles times the rate for that date.
- Lines sum to the printed total.
- Total minus advance equals amount due.
Foreign currency and split lines on an expense report
Expenses abroad come with two amounts: the original in local currency and the converted amount in the reporting currency. Some lines are also split, such as a dinner shared between two projects. Keep the original amount, the currency and the rate in their own columns, and make split parts separate rows that sum to the original.
Which rate was used
The rate on the report might be the card's rate, a company rate or a rate the employee typed. Store the rate the report printed, and compute converted amount as original times rate. If your computed figure differs from the printed one, keep the printed one, since it is what was reimbursed, and note the difference.
Split lines
A split line often appears as a parent row with the full amount and child rows below it. Keep only the child rows in the line table, or the parent is counted twice. The total check above will show the doubling at once.
The other side of the reimbursement
The reimbursement itself leaves the company bank account, and the corporate card bill shows the card spending. FastBankConvert is being built to turn those bank statement PDFs into Excel or CSV, with a check that each statement's balances add up. It is getting ready to open; join on the get-started page.