Build a monthly report with a repeatable import and a reconciliation

Take eight clean orders through import, month-and-region totals, validation, a chart and a controlled refresh.

Before you start

Windows desktop Excel 2016, 2019, 2021, 2024 and Microsoft 365. The report formulas do not require dynamic arrays. The local CSV query steps assume Windows; Mac and web connector differences require adapting the import before reproducing the workbook there.

Useful first: Build a PivotTable and prove that its totals are right · Create a chart that answers one clear question · Highlight rows with a rule you can explain · Append orders, merge customer names, and check the row count

By the end, you can…

Create a report whose totals trace back to eight source records.
Distinguish source checks from proof of complete real-world data.
Refresh after a known correction and explain the resulting change.

YOUR PRACTICE FILE

Small data. A result you can check.

Eight synthetic records in arbitrary currency units. Import month as Text and amount as Whole Number.

Download practice data

View the raw practice data

order_id,month,region,amount
O001,2026-01,North,120
O002,2026-01,South,80
O003,2026-01,North,40
O004,2026-01,South,60
O005,2026-02,North,90
O006,2026-02,South,110
O007,2026-02,North,70
O008,2026-02,South,50

01Import a defined reporting input

Save the sample CSV as UTF-8. In a new workbook create Raw and Report sheets. Import through Data > From Text/CSV, choose comma and no automatic type detection, then Transform Data. Remove any automatic Changed Type step before assigning order_id, month and region to Text; assign amount to Whole Number. Keep one header row and all eight records.

Load to Raw!A1 and name the Excel table ReportData. Month values are text keys. Inspect O001 through O008 once each. This clean sample needs no joins, deduplication or exclusions.

02Build the month-and-region matrix

In Report!A1:D1 enter Month, North, South, Total. Format A2:A3 as Text before entering 2026-01 and 2026-02. Enter the formula below in B2. In C2 use =SUMIFS(ReportData[amount],ReportData[month],$A2,ReportData[region],"South"). Copy B2:C2 down to B3:C3, not sideways; the separate formulas keep their region criteria explicit.

Enter =SUM(B2:C2) in D2 and copy down to D3. Put Total in A4, =SUM(B2:B3) in B4, =SUM(C2:C3) in C4 and =SUM(D2:D3) in D4. Compare every result with this matrix.

Month	North	South	Total
2026-01	160	140	300
2026-02	160	160	320
Total	320	300	620

=SUMIFS(ReportData[amount],ReportData[month],$A2,ReportData[region],"North")

B2 must return 160. English function names and commas are shown; use your locale's argument separator where necessary.

03Reconcile before drawing the chart

On Report enter =ROWS(ReportData[order_id]) in G2, =COUNT(ReportData[amount]) in G3, =SUM(ReportData[amount]) in G4 and =D4-G4 in G5. Expected results are 8, 8, 620 and 0. These checks establish agreement with this input; they do not prove that a supplier sent every real order. Counts alone also cannot prove ID uniqueness.

Select Report!A1:C3 and insert a two-dimensional clustered column chart. Exclude the totals row and Total column. Title it Monthly sales by region (sample units). The initial statement is: February totals 320 versus January's 300; North stays at 160 and South increases by 20. Keep the numbers beside the chart.

04Make the report repeatable without hiding its limits

Save as XLSX, retaining the original CSV separately. Correct source records, refresh the query, wait for completion, then review the checks and narrative. Do not type corrections into the loaded output. These two summary rows cover only January and February: a new month needs an additional summary row and updated totals and chart ranges.

If you additionally build a PivotTable from ReportData, refresh it after the query finishes. Its cache is a separate checkpoint; do not assume a still-running Refresh All has already updated every report.

PUT IT INTO PRACTICE

Process a documented correction to O008.

In the original CSV, change only O008's amount from 50 to 70 and save at the same path.
Refresh the loaded query table and wait. With workbook calculation set to Automatic, review the matrix and checks.
Update the narrative and save the workbook with a note that O008 increased by 20.

Show the worked answer

January remains 300. February/North stays 160, February/South becomes 180 and February totals 340.

Regional totals become North 320 and South 320; the overall total is 640. G2=8, G3=8, G4=640 and G5=0.

Check your work

The chart must show February South at 180.
If row counts change, more than the intended amount correction occurred.

CHECK YOUR UNDERSTANDING

One question before you move on.

G5 is zero. What can you safely conclude?The supplier sent every real-world order.All IDs are necessarily unique.The summary total agrees with the imported amount total.

Ready for the next step?

Mark this lesson when you can explain the idea and reproduce the practice result.

Your checklist stays on this browser.

Keep the skill close.

FIELD GUIDEExcel SUMIFS Date Range: Include the Whole End Date, Even with Times FIELD GUIDEExcel SUM Returns Zero or Misses Values: Convert Numbers Stored as Text FIELD GUIDECSV vs XLSX: what you lose when saving a spreadsheet as CSV WORKSPACE TOOLCSV import checker WORKSPACE TOOLCompare two lists

Reference notes

These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.

Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.