Learning goals

Choose a load destination for each query: table, connection only, or none
Run a refresh in the right order and know what Refresh All guarantees
Read the Monthly Report's SUMIFS layer over the loaded tables
Rebuild the Invoiced in period metric and reconcile it to the invoice list

Concepts

Where each query loads

Close & Load To (the dropdown under the Close & Load button) decides what a query becomes in the workbook. Northgate loads three queries as tables on their own sheets -- Clean Pipeline, Invoice Facts, Utilization -- because people read them. Hours Tidy loads as a table too (it is the audit trail behind utilization). The three staging queries load connection only: they exist to feed the transforms, and a staging sheet nobody reads is a staging sheet somebody will 'fix'.

Connection-only queries still refresh, still cost a moment, and still appear in Queries & Connections with their names and row counts visible in the pane. That pane is the fastest health check in the workbook: if Clean Pipeline says 10 rows and the report says 12 opportunities, something refreshed out of order.

💡 Tips:

Load connection only first, then promote a query to a table the day somebody asks to see it. The reverse order -- loading everything, then deleting sheets -- leaves orphaned connections behind.

Properties > 'Enable background refresh' off for the staging queries keeps refresh order deterministic; background refresh is a race you do not need at this size.

Refresh All, and what it promises

Data > Refresh All (Ctrl+Alt+F5) reruns every query against its source, in dependency order, then recalculates the workbook. That is the monthly close reduced to: drop three files in C:\Reports\Exports, open the workbook, press Refresh All, wait a breath.

What it does not promise: correctness. Refresh moves data; it cannot notice that Ledgerly sent fourteen invoices this month or that a service code has no mapping. That is what the report's checks are for -- and why the report layer is formulas instead of one more grouped query. A formula that shows its work next to its answer is auditable in a way a folded query result is not.

=SUMIFS('Invoice Facts'!$I$5:$I$1004,'Invoice Facts'!$C$5:$C$1004,">="&Settings!$C$6,'Invoice Facts'!$C$5:$C$1004,"<="&Settings!$C$7)

Invoiced in period: sum the invoice amounts whose invoice date falls between the period start (Settings!$C$6, Aug 1, 2026) and the close date (Settings!$C$7, Aug 31, 2026), both inclusive. The date bounds are glued on with & so one formula serves any month the Settings cells describe. Eight invoices qualify and the answer is 16,820.00.

=SUMIF('Invoice Facts'!$M$5:$M$1004,"Overdue",'Invoice Facts'!$L$5:$L$1004)

Overdue receivables: sum the open balances (column L) wherever the status (column M) equals Overdue. One row qualifies -- INV-2404 -- so the result is 930.00. This is the report reading a status the query computed in lesson 7.

The formula layer reads, the query layer writes

The Monthly Report holds fifteen metrics, every one a classic formula over the loaded tables: SUMIFS for period amounts, SUMIF for status slices, COUNTA for row counts, AVERAGE for the invoice profile, IFERROR around anything that divides. The ranges are pinned to rows 5 through 1004 so the formulas do not care whether the table holds seven rows or seven hundred.

Two metrics protect the boundary: Collected in period (10,500.00) reads Paid Dates, so INV-2408 -- paid Sep 3, 2026 -- is excluded even though its status is Paid; and Invoiced in period excludes INV-2413, dated Sep 2. Status describes the ledger; period filters describe the window. A report that mixes those two up answers a question nobody asked.

💡 Tips:

Guard every division with IFERROR and every count-based denominator with COUNTA > 0 logic. An empty table after a failed refresh should show zeros, not #DIV/0!.

Practice

The exercise opens on the Monthly Report. Cell C5 is blank -- you retype the period metric that anchors the whole sheet. Nothing is saved in the browser.

Read the report top to bottom — Fifteen metrics in three blocks: cash and receivables (rows 5-8), pipeline (rows 9-12), delivery (rows 13-16), then profile and controls (rows 17-19). Every value cell is pale green: the whole sheet is formulas over the loaded tables.
Retype the anchor metric — Cell C5 is empty. Type =SUMIFS('Invoice Facts'!$I$5:$I$1004,'Invoice Facts'!$C$5:$C$1004,">="&Settings!$C$6,'Invoice Facts'!$C$5:$C$1004,"<="&Settings!$C$7) and press Enter: 16,820.00 -- the eight invoices dated inside August 2026.
Reconcile the cash block — C6 shows 10,500.00 collected (the four payments dated in August), C7 shows 10,415.00 open across seven invoices, and C8 shows 930.00 overdue from INV-2404 alone. Cross-foot in your head: 16,820.00 invoiced in August, 10,500.00 of all-time cash landed in August, and the open balance belongs to invoices from both months.
Check the boundary behavior — Note which numbers INV-2408 and INV-2413 appear in: both are counted in open receivables (C7), neither in invoiced-in-period (C5) or collected-in-period (C6). Then read rows 18 and 19: 12 raw CRM rows staged, 2 removed by cleaning -- the report auditing its own inputs.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

☐ Retyped the SUMIFS into C5 and got 16,820.00 invoiced in period
☐ Verified the cash block: 10,500.00 collected, 10,415.00 open, 930.00 overdue
☐ Confirmed the boundary rows INV-2408 and INV-2413 are excluded from both period metrics but included in open receivables

⚡ Lesson 10 · Load, refresh, and the report