Receipt to Excel: Build a Clean Expense Sheet

To get a client's receipts into Excel, give each receipt one row with the same columns every time: date, merchant, amount, tax, currency, category, payment method and file name. Read or extract the fields, check each row against its image, then total and categorize with formulas. That receipt to Excel layout stays the same for every client.

A receipt-to-Excel column layout

A receipt-to-Excel column layout is a fixed set of columns that every receipt row fills in the same order. A useful set is date, merchant, amount, tax, currency, category, payment method and file name. Fixed columns let formulas, filters and pivot tables work the same way for every client and every month.

Each column has one job:

• Date. The transaction date printed on the receipt, entered as a real Excel date, not text. Use one format throughout, such as 2026-09-18.
• Merchant. The business name as printed on the receipt. Keep it consistent across rows so filtering by merchant works.
• Amount. The amount actually paid, including any tip written in by hand. For a refund, enter a negative number.
• Tax. The sales tax or VAT amount shown on the receipt, if any. Leave it blank if none is shown rather than guessing.
• Currency. A three-letter code such as USD or EUR. Most rows will say USD, and that is the point: the one that does not stands out.
• Category. The firm's expense category for the row. Leave it empty until categorization; filling it while typing mixes two jobs.
• Payment method. Card type and last four digits, or cash. This is what ties a receipt to a card statement.
• File name. The name of the image or PDF the row came from, so anyone reviewing a row can open the source.

Foreign currency receipts need three more columns: exchange rate, rate source and converted amount. They are covered in their own section below.

The file name column earns its place during review. IRS guidance on what kind of records to keep says expense documents should identify the payee, the amount paid, proof of payment and the date incurred, and describe what was bought. The sheet is a summary; the receipt image is the document. Linking each row to its file keeps the two together.

A sample receipt sheet with five rows

The client, merchants, amounts, card digits and exchange rate below are fictional. The rate is an example only, chosen for the arithmetic, and is not a real published rate.

The euro row converts 48.00 EUR at the example rate of 1.0850 US dollars per euro: 48.00 × 1.0850 = 52.08. The Harborview row includes an $11.00 handwritten tip on a printed total of $61.16. The Pinecrest row is a refund, entered as a negative. The USD amount column totals $203.11.

Converting receipts to Excel, step by step

Converting receipts to Excel runs from a folder of photos to a checked sheet in six steps: gather, name, extract, import, check and total. The order matters. Checking happens before totalling, so a misread amount is caught at the row instead of being hunted for later in a total that does not tie.

1. Gather every receipt for the period in one folder. Phone photos, email attachments and scanned PDFs all go together. If receipts arrive by several routes, collect them first, then work.
2. Name each file by date and merchant. A name like 0918-cafe-lyon.pdf sorts by date and tells a reviewer what to expect. If the client sent a shoebox of paper rather than files, the post on how to digitize a shoebox of receipts covers getting the paper scanned first.
3. Extract the fields. Type them, or use a tool that reads receipts and produces a CSV file. Either way, the output should follow the column layout above.
4. Import into Excel. A CSV file can be opened directly, but Microsoft's guide to importing or exporting text and CSV files describes Data > From Text/CSV, which shows a preview and a delimiter choice before loading. The same guide notes that a CSV opened directly uses Excel's current default format settings to interpret each column, which can turn dates in an unexpected order into the wrong values.
5. Check every row against its image. Open the file named in the row and compare date, merchant and amount. Look hardest at rows with a tip line, a faded total or a currency other than USD.
6. Total and summarize. Add a total row, then summarize by category or by payment method.

For step 6, SUMIFS adds the amounts that match a condition. Microsoft's reference for the SUMIFS function gives the syntax as SUMIFS(sum_range, criteria_range1, criteria1, ...). With the sample sheet in columns A to K and rows 2 to 6, this totals USD amounts paid on the Visa ending 4821:

=SUMIFS(G2:G6, J2:J6, "Visa XXXX-4821")

That returns 151.03 for the sample: 84.37 plus 18.00 plus 72.16, less the 23.50 refund. Compare it with the Visa statement for the same period.

Duplicate rows are worth a check before totalling. Microsoft's guide to finding and removing duplicates shows how to highlight duplicate values with conditional formatting. Highlight rather than delete: the same guide warns that Remove Duplicates deletes data permanently, and two identical coffee purchases on the same day can both be real.

Once rows are checked, assigning a category to each is its own job. The post on expense categorization for bookkeeping clients walks through categorizing an export line by line.

How should foreign currency receipts appear in Excel?

Foreign currency receipts should appear in Excel with four pieces of information: the original amount, its currency, the exchange rate used with its source and date, and the converted amount. Keeping the original next to the conversion lets anyone check the arithmetic, and recording the rate source shows where the number came from.

For receipts in foreign currency for bookkeeping, the sample sheet's Lyon row shows the pattern. The original amount stays as printed, 48.00. The currency column says EUR. The rate column holds the rate used, 1.0850 in the example. The rate source column says where that rate came from and for which date. The USD amount column holds a formula, not a typed number:

=ROUND(C5*E5, 2)

The IRS page on foreign currency and currency exchange rates says amounts reported on a US tax return must be expressed in US dollars. It says that, for a taxpayer whose functional currency is the dollar, items are generally translated at the exchange rate prevailing when the item is received, paid or accrued. Where more than one rate exists, it says to use the one that most properly reflects income, and it lists sources of rates such as the Treasury Department and the Federal Reserve Bank. Which rate a firm uses for a given client is the firm's decision; the sheet's job is to record it.

A few formatting details help:

• Show the currency symbol. Microsoft's guide to formatting numbers as currency shows how to pick a symbol in the Format Cells dialog. Formatting the original-amount cell as euros makes a foreign row obvious at a glance.
• Keep tax in the original currency. VAT printed on a foreign receipt is in that currency. Label it, as the sample does with 8.00 EUR, or convert it in its own column.
• Watch decimal commas. A receipt printed 48,00 means forty-eight euros. Check that it did not import as 4,800.
• Watch day-first dates. 18/09/2026 is 18 September. A receipt dated 03/09/2026 from Europe is 3 September, not March 9.

Why don't receipt totals in Excel match the card statement?

Receipt totals in Excel often fail to match the card statement because the receipt and the statement record different things. A tip added after the receipt printed, a different exchange rate and a separate foreign transaction fee, refunds posted in a later cycle, and receipts that never arrived all open a gap. Each has a distinct pattern.

Tips added later. A restaurant receipt printed before the tip shows the pre-tip total. The card charge includes the tip. If the sheet recorded the printed total, it is short by the tip. In the sample, recording $61.16 instead of $72.16 would leave the Visa total $11.00 under.

Currency conversion. The card issuer converts a foreign purchase at its own rate on its own date, which rarely equals the rate the firm chose. The Lyon receipt might appear on the statement as, say, $52.41 rather than $52.08. Some cards also charge a foreign transaction fee. Under Regulation Z, the CFPB's text of 12 CFR 1026.7 requires card statements to group fees together under the heading Fees, separate from transactions, so look for that fee in the Fees section rather than next to the purchase.

Refunds. A refund may post days after the return, sometimes in the next statement cycle. The same regulation requires each credit to show its amount and date, so search the following statement for a refund that seems to be missing.

Missing receipts. A charge on the statement with no matching row means a receipt the client did not send. List those charges and ask for the receipts, or note that the statement line is the only record.

Cut-off. A purchase on the last day of the month may post in the next cycle. Compare by transaction date, not posting date.

The tie-out on the statement side has its own method. The post on converting a credit card statement to Excel covers getting the statement's charges, credits and fees into rows so the two sheets can be matched line by line.

Exporting receipts to Excel or CSV from Schema CPA

Exporting receipts from Schema CPA produces an Excel or CSV file of the receipts read from a client's uploads. Receipts reach Schema CPA through the client's upload link along with everything else the client sends, each page is classified by type, issuer and period, and receipts sent twice are flagged rather than added twice.

The export is a spreadsheet file, so every step in this post still applies to it: the checks against the images, the SUMIFS summaries, the foreign currency columns and the tie-out to the card statement. Schema CPA does not connect to accounting software today; the file is what the firm takes into its own process. It also does not assign tax treatment to any expense. The workflow section of the homepage shows where export sits after collection, classification and duplicate checks.