How to turn a pile of receipts into a spreadsheet

Start with the columns

One row per receipt, with the same columns every time. For most expense records that means:

• Date, entered as a real date so the sheet can sort and group by month.
• Supplier, written the same way each time, since a filter on “Corner Café” misses “CORNER CAFE LTD”.
• Receipt or invoice number, which catches the same receipt entered twice.
• Amount before tax, tax and total, as numbers with no currency sign typed in, so they add up.
• Currency, in a column of its own if you ever pay in more than one.
• Category, and the file name of the scan, so a row can be traced back to its paper.

Keep the receipts as well as the rows

The row is a summary. A tax office or an accountant will usually want the receipt itself, often for several years. Till receipts are mostly printed on thermal paper, which fades, so photograph or scan them soon rather than at the end of the year. Name each file by its date and supplier, and the column above links the row to it.

Ways to get the figures in

• Typing. For a dozen receipts a month, typing them is quick and accurate, and there is nothing to set up.
• Your accounting software. Most bookkeeping programs read a photographed receipt into an expense, on their own servers. If you already keep your books in one, that is usually the best route.
• Excel’s Data from Picture in Microsoft 365 turns a picture of a table into cells. It is made for tables rather than receipts, so you still pick out the total yourself, and the picture is read by Microsoft’s online service.
• A program that reads receipts as receipts, finding the supplier, the date and the figures by their labels and filling a row. This saves the most typing, and it is where the next section matters most.

Check each row by its sums

A receipt checks itself. The items add up to the subtotal, the subtotal and the tax add up to the total, and the tax is the stated rate of the subtotal. When they agree, the figures are almost certainly read right. When they do not, look at the paper.

Machine reading tends to fail in the same few ways: a decimal point lost, so 12.50 becomes 1250; a currency sign read as a digit, such as a rupee sign taken for a 7; a 1 read as a 7; the day and the month of a date swapped. Each of them breaks the sums, which is why checking the sums catches them.

Watch the digits too. Receipts printed in Arabic-Indic or Urdu digits defeat many readers, which return letters or symbols in place of numbers. A reader that cannot be sure should say so rather than guess.

Then let the sheet do the adding

With clean rows the rest is quick. SUMIFS gives the total for a month and a category, a PivotTable gives every month by every category at once, and a filter on the tax column gives the figure a tax return asks for.