To check whether your monthly report picked up the new month, compare the refreshed data against numbers from outside the spreadsheet: your POS or accounting system’s month-end summary. Check four things: the latest order date, the number of source files, the number of orders, and total sales. If any one doesn’t match, the month didn’t fully load, even if the refresh finished without an error.
The setup below works in Google Sheets or Excel, including data you combine with Power Query, and needs no add-ons. It takes about 15 minutes to build once. After that, it’s a one-glance check before you send the report each month.
Why “No Error” and “I See April” Don’t Prove the Month Loaded
Here is a made-up sales log on a tab called Combined_Sales, where one row represents one order. It holds eight orders from four monthly files, totaling $11,400.00: six Q1 orders worth $8,100.00 and two April orders worth $3,300.00 (ORD-2007 on April 14 and ORD-2008 on April 27).
| Row | source_file (A) | order_id (B) | order_date (C) | customer_id (D) | net_sales (E) |
|---|---|---|---|---|---|
| 2 | sales-2026-01.csv | ORD-2001 | 2026-01-10 | CUST-301 | $1,250.00 |
| 3 | sales-2026-01.csv | ORD-2002 | 2026-01-24 | CUST-302 | $1,350.00 |
| 4 | sales-2026-02.csv | ORD-2003 | 2026-02-11 | CUST-303 | $1,400.00 |
| 5 | sales-2026-02.csv | ORD-2004 | 2026-02-25 | CUST-301 | $1,200.00 |
| 6 | sales-2026-03.csv | ORD-2005 | 2026-03-12 | CUST-304 | $1,500.00 |
| 7 | sales-2026-03.csv | ORD-2006 | 2026-03-28 | CUST-302 | $1,400.00 |
| 8 | sales-2026-04.csv | ORD-2007 | 2026-04-14 | CUST-305 | $1,800.00 |
| 9 | sales-2026-04.csv | ORD-2008 | 2026-04-27 | CUST-301 | $1,500.00 |
Make sure order_date holds real dates and net_sales holds real numbers. If either column is stored as text, the checks below quietly skip those cells.
Monthly updates usually fail in one of two ways:
- State A: the new month is missing entirely ($8,100.00 loaded, $3,300.00 or 28.95% missing). When you combine monthly CSV files with Power Query, refreshing the query can finish without an error if sales-2026-04.csv was saved to Downloads instead of the folder the query reads. In Google Sheets or Excel for the web, the same gap happens when April’s rows are pasted below a summary formula that still stops at row 7.
- State B: the new month is only partly there ($9,900.00 loaded, $1,500.00 or 13.16% missing). If April’s file was exported on April 15 and never replaced after month-end, “2026-04” shows up and all four files load, so a quick glance passes. But 45.45% of April’s revenue is missing. This is a classic timing cutoff mismatch across systems.
Step 1: Write Down What the Month Should Look Like
Add a second tab called Checks. In column B, type the numbers your month should produce. Take them from your POS or accounting system’s closed-month summary, not from the spreadsheet you’re checking. If the expected numbers come from the same data, the check can never fail.
| Row | Check (A) | Expected (B) | Actual (C) | Difference (D) | Result (E) |
|---|---|---|---|---|---|
| 2 | Latest order date | 2026-04-27 | 2026-04-27 | 0 | OK |
| 3 | Source files | 4 | 4 | 0 | OK |
| 4 | Orders in new month | 2 | 2 | 0 | OK |
| 5 | Total orders | 8 | 8 | 0 | OK |
| 6 | Total sales | $11,400.00 | $11,400.00 | $0.00 | OK |
| 7 | Repeated order IDs | 0 | 0 | 0 | OK |
| 9 | Month status | READY TO SHARE |
Type B2 as a real date (for example, =DATE(2026,4,27)) and format B2:C2 as dates. Format D2:D7 as plain numbers, so a gap shows as -13 days rather than a date. Repeated order IDs should always be 0.
Step 2: Add the Checks
Each formula reads whole columns on Combined_Sales, so new rows are picked up automatically. You never have to edit a range when the next month arrives.
C2, latest order date. If this is earlier than the last sale in your POS, the end of the month is missing. MAX ignores the header row:
=MAX(Combined_Sales!C:C)
C3, source files. In Power Query, this is the Source.Name column. The formula counts distinct file names and subtracts the header:
=COUNTA(UNIQUE(FILTER(Combined_Sales!A:A, Combined_Sales!A:A<>""))) - 1
C4, orders in the new month. This counts orders dated in the same month as B2:
=COUNTIFS(Combined_Sales!C:C, ">="&DATE(YEAR(B2), MONTH(B2), 1), Combined_Sales!C:C, "<="&EOMONTH(B2, 0))
C5, total orders, and C6, total sales:
=COUNTA(Combined_Sales!B:B) - 1
=ROUND(SUM(Combined_Sales!E:E), 2)
C7, repeated order IDs. This is total order IDs minus distinct order IDs, so anything above 0 means an order was loaded twice:
=COUNTA(Combined_Sales!B:B) - COUNTA(UNIQUE(FILTER(Combined_Sales!B:B, Combined_Sales!B:B<>"")))
D2 and E2, then fill both down to row 7:
=ROUND(C2 - B2, 2)
=IF(D2=0, "OK", "CHECK")
E9, the one cell to look at before you send the report:
=IF(COUNT(B2:B7)<6, "ENTER EXPECTED VALUES", IF(COUNTIF(E2:E7, "OK")=6, "READY TO SHARE", "STOP: DON'T SEND YET"))
What Each Failure Looks Like
Here is what the Checks tab shows for each state of the sample data. Both failures stop on more than one line, and the Difference column tells you where to look.
| Check | Expected | State A (no April) | State B (mid-April export) | State C (complete) |
|---|---|---|---|---|
| Latest order date | 2026-04-27 | 2026-03-28 | 2026-04-14 | 2026-04-27 |
| Source files | 4 | 3 | 4 | 4 |
| Orders in new month | 2 | 0 | 1 | 2 |
| Total orders | 8 | 6 | 7 | 8 |
| Total sales | $11,400.00 | $8,100.00 | $9,900.00 | $11,400.00 |
| Repeated order IDs | 0 | 0 | 0 | 0 |
| Month status | STOP: DON’T SEND YET | STOP: DON’T SEND YET | READY TO SHARE |
Notice that State B passes the file count, which is exactly why “all four files loaded” isn’t enough. The latest order date, the April order count, and total sales all catch it.
Keeping It Working Each Month
- Update only column B. Each month, type the new expected values from your closed POS or accounting summary. The formulas don’t change.
- Point your summaries at whole columns too. If your monthly sales pivot table in Google Sheets or summary formulas stop at a fixed row, the Checks tab can say READY while the report still shows last month.
- Keep Checks on its own tab. Power Query rewrites the sheet it loads into on every refresh, so formulas placed beside the loaded table can be overwritten.
- Use a current version. UNIQUE and FILTER need Google Sheets, Microsoft 365, or Excel 2021 or later.
Two things these checks can’t catch on their own. First, two edits that cancel out (+$100.00 on one order and -$100.00 on another) leave every total unchanged; only an order-by-order comparison finds those. Second, an ID typed as “ORD-2001 ” with a trailing space, or in different capitalization, may not count as a repeat. Trimming and uppercasing IDs in Power Query or your export prevents that.
Frequently Asked Questions
Why did my summary still show $8,100.00 after the Checks tab said READY TO SHARE?
The Checks tab only proves the data on Combined_Sales is complete. If a separate summary or pivot table still reads a fixed range like A2:E7, it never sees the new rows. Change its source to whole columns or the full table.
Where do the expected numbers come from?
From any system that closed the month independently of your spreadsheet: your POS month-end report, or a sales-by-month report from your accounting software. Type them in by hand. Never use formulas that read Combined_Sales, because then the check compares the data to itself.
Does this work if I combine files with Power Query?
Yes. Load the combined query to a sheet named Combined_Sales, and keep Power Query’s Source.Name column as column A so the file count works. Refresh, then look at E9 on the Checks tab before sharing anything.
What to Do Next
Once the Checks tab says READY TO SHARE, update your monthly reporting pack template, review what to include in a monthly business report, and run the small business data audit checklist.