Monthly Expense Summary for Google Sheets: A Copy-Ready Template

Maya scanned a Northbank Print receipt on 1 October, then sent her September expense report to her business partner. The report was short by 96.40. The receipt was dated 30 September, but the spreadsheet had grouped it by upload date.

Nothing was wrong with the amount. The wrong date had answered the wrong question.

A reliable expense summary Google Sheets workbook needs three things separated: the transaction record, the reporting rules, and the checks that expose incomplete work. Build those separately, and changing the month becomes a cell edit rather than another round of copying rows.

Set the reporting rules before building the monthly totals

This template reports gross spending by receipt transaction date, including refunds as negative amounts. It is a management view, not a calculation of deductible expenses, recoverable tax, or accounting profit.

If your business needs payment-date reporting or accrual accounting, agree the date basis with your bookkeeper before using the results. Keep receipt date and payment date in separate fields rather than silently changing what “Date” means halfway through the year. Tax treatment and retention requirements depend on your jurisdiction; check your local tax authority’s guidance and your accountant’s instructions.

Use one row per expense allocation. Usually that means one receipt. A receipt covering two categories needs two rows whose amounts add back to the receipt total.

Keep different currencies separate. A total of 100 EUR plus 100 USD is not 200 of anything useful. This workbook selects one currency at a time; it does not invent an exchange rate.

Create the Expenses, Lists, Summary, and Pivot tabs

Create four tabs with those exact names. Keep all months in Expenses; do not create a new transaction tab each month.

In Expenses, copy these headers into row 1. The example represents Maya’s corrected record; EUR is simply this example business’s currency.

Enter dates as actual Sheets dates, not text that merely looks like a date. Format B as yyyy-mm-dd and F as a number with two decimal places. Do not type currency codes into the amount cells.

Give every allocation a unique, permanent Entry ID. Do not derive IDs from row numbers: sorting would change them. Project is optional; the receipt link should open the complete supporting document.

In Lists, enter:

Replace the currency examples with those you actually use. Apply dropdown validation to Expenses D, G, and I using the corresponding Lists ranges, with invalid entries rejected. Require valid dates in B and numbers in F.

Pending means captured but not checked. Approved means reviewed for reporting. Excluded retains an accidental duplicate or non-business entry without counting it. Unassigned is a visible category for unresolved classification, not a convenient place to leave everything.

Scan2Sheet can extract receipt details into your own Google Sheet and link each row to its stored image. Align those synced fields with this ledger structure, and test how additional review and formula columns behave before relying on a live sync.

Add helpers that reveal what a monthly total hides

These formulas use English function names and comma separators. Some Sheets locales require semicolons instead. Set the spreadsheet locale deliberately before importing dates such as 04/05/2026.

Paste this in Expenses!K2 and fill through K5000:

=IF(ISNUMBER(B2),DATE(YEAR(B2),MONTH(B2),1),"")

Format K as yyyy-mm. It remains a real date, which makes pivot sorting reliable across years.

In L2, fill down:

=IF(OR(A2="",I2="Excluded"),"",IF(COUNTIFS($B$2:$B$5000,B2,$C$2:$C$5000,C2,$F$2:$F$5000,F2,$G$2:$G$5000,G2,$I$2:$I$5000,"<>Excluded")>1,"REVIEW",""))

This flags matching date, merchant, amount, and currency combinations. It is a candidate check, not proof: two genuine purchases can match. Compare receipt images or transaction references before excluding anything. Both matching rows are flagged, so two flags may represent one duplicate pair.

In M2, fill down:

=IF(COUNTA(A2:J2)=0,"",IF(I2="Excluded","",IF(OR(A2="",NOT(ISNUMBER(B2)),C2="",D2="",NOT(ISNUMBER(F2)),G2="",H2="",I2<>"Approved"),"CHECK","")))

This catches missing essentials, text dates, text amounts, and unfinished review. It deliberately ignores empty rows and excluded entries. It checks that a receipt link exists, not whether its permissions work; open links during review.

Protect K:M against accidental edits and apply conditional formatting to highlight REVIEW and CHECK. These formulas stop at row 5000. Extend the helper formulas, validation, summary ranges, and pivot source together before reaching that limit.

Build the Summary tab with period-safe SUMIFS

Use this exact layout:

B1 must be the first day of the selected month. Apply a custom validation rule =AND(ISNUMBER(B1),DAY(B1)=1) and reject invalid input. Give B3 the same currency dropdown as the ledger.

Put this in B4:

=SUMIFS(Expenses!$F$2:$F$5000,Expenses!$B$2:$B$5000,">="&$B$1,Expenses!$B$2:$B$5000,"<"&$B$2,Expenses!$G$2:$G$5000,$B$3,Expenses!$I$2:$I$5000,"Approved")

In A8 enter =Lists!A2 and copy down to A14. That brings across all seven categories.

In B8, copy down to B14:

=SUMIFS(Expenses!$F$2:$F$5000,Expenses!$D$2:$D$5000,$A8,Expenses!$B$2:$B$5000,">="&$B$1,Expenses!$B$2:$B$5000,"<"&$B$2,Expenses!$G$2:$G$5000,$B$3,Expenses!$I$2:$I$5000,"Approved")

In C8, copy down and format as percentages:

=IFERROR(B8/$B$4,0)

The date conditions mean “on or after this month’s start, before next month’s start.” Unlike an end-of-month midnight cutoff, they also include a transaction carrying a time on the last day. Prefer date-only ledger entries, but do not let imported timestamps silently lose a day’s spending.

Shares can be negative or exceed 100% when refunds offset other categories. They describe net spending composition, not budget performance; a zero-total month displays zero shares here.

Test the template with a refund and a foreign subscription

Maya’s shop has these approved EUR entries:

With B1 set to 1 September, B4 returns 144.40: 48.00 + 96.40. Supplies returns 96.40. Changing B1 to 1 October gives -24.10 if there are no other October entries.

Record the refund as a new row dated when the refund occurred, with its own supporting document and a note referencing EXP-0261. Do not reduce September’s original receipt to 72.30: that would rewrite the earlier period and conceal the later refund.

Now contrast a consultancy buying software internationally. It has a September EUR subscription of 44.16 and a separate USD subscription of 48.00. Selecting EUR returns 44.16; selecting USD returns 48.00. Neither report should show 92.16.

If consolidated reporting becomes necessary, add explicit original amount, original currency, exchange rate, rate date/source, and reporting amount fields. Agree the conversion basis with your bookkeeper, then summarize the reporting amount. Do not overwrite the original receipt values with the card statement’s converted charge.

For split receipts, allocate the gross total rather than repeating it. A 180.00 purchase split between supplies of 120.00 and installation services of 60.00 needs two IDs, the same receipt link, and notes identifying the split. Never keep a third 180.00 total row marked Approved.

Put three weekly controls beside the monthly summary

Add these labels in D2:D4 and formulas in E2:E4:

The first two controls cover the whole ledger, not just the selected month. That is intentional: an invalid date cannot reliably belong to a monthly review queue.

Filter M for CHECK, correct the evidence or data, then approve the row. Investigate L separately. Confirmed distinct purchases can remain flagged with an explanatory note; do not alter real amounts to silence the check.

E4 should be zero. If an imported 12.75 expense carries an old category called Office, B4 includes it but the category table does not. E4 shows 12.75. Reclassify it, or deliberately add the category to Lists and extend the Summary rows and control range.

A zero difference is not proof of completeness: equal positive and negative omissions can cancel. Keep dropdown validation and review the ledger against card, bank, and cash records. A receipt that was never captured cannot trigger a spreadsheet formula.

Use the Pivot tab when the reporting question changes

The fixed Summary tab answers a stable question: what did we spend by category this month? A pivot table is better when your partner asks which project bought the software, or which merchant drove the increase.

Select Expenses!A1:M5000, choose Insert → Pivot table, and place it in Pivot!A1. Configure:

• Rows: Category, then Merchant.
• Columns: Month.
• Values: Amount, summarized by SUM.
• Filters: Status = Approved; Currency = one selected currency; Month = the periods required.

For project reporting, replace Merchant with Project. The underlying rows stay untouched. Hand-built cross-tabs require new formulas for each dimension, and one missing category can produce a polished but incomplete report.

Check the pivot’s selected-period grand total against Summary B4. Never remove the currency filter without separating currencies in the pivot layout. Keep the fixed summary for routine review and the pivot for exploration; neither replaces supporting documents.

Copy the tabs, test the sample entries, and connect your receipt workflow with Scan2Sheet when you are ready to reduce manual entry.