A monthly summary you build once and refresh each month

Once a month, someone exports a file from the sales system, the help desk, the donations platform or the school register and rebuilds the same summary by hand: filter, count, add up, redo the chart. However long that takes you this month, it will take as long again next month.

A pivot table ends the rebuilding. You set it up once, with a chart, a few headline formulas and a check, and from then on the monthly summary is a paste and a refresh. The assistant helps with the setup only: from your column headers, it turns your questions into a layout and writes the formulas. Your figures stay in your spreadsheet.

By the end of this you’ll have your own monthly summary working: a Summary tab with a pivot table, a chart, the numbers you report every month and a check that the totals match, plus a short update note already tested on a second month of data. Allow about an hour.

What you’ll be able to do

• Finish last month’s summary today, on a Summary tab you never rebuild: pivot table, chart, headline numbers and a check against the export.
• Update it next month with a paste and a refresh, following a note you have already tested.
• Turn your questions into a pivot table layout and formulas, with an assistant that sees only your column headers, and catch the usual problems before they reach your report.

What you need

• The exports for the last two complete months from the system you summarize (a CRM, help desk, accounting or donations platform, ad account or registration system), as CSV or spreadsheet files. You build on the older one and test the update with the newer one. If your data comes as PDFs, From a stack of PDFs to a checked spreadsheet gets it into rows first.
• Excel (desktop or web) or Google Sheets, and a chat assistant such as Claude, ChatGPT, Gemini or Copilot.
• If you’d rather learn on made-up data first, prompt 1 below creates two months of it, and the examples use it. Either way, the assistant sees only your headers and three rows you invent; before you ever paste real records into a chat, read Before you upload a client document.

Steps

1. Write the questions

Write down the three to five questions the summary must answer every month. For example, a sales manager might ask how much each region sold and how many orders were over 1,000; a support lead, how many tickets came in by category and how many are still open. If other people read your summary, add the questions they asked about last month’s.

2. Put the older month on a Data tab

In a new workbook, name the first tab Data and paste the older export at A1: headers in row 1, one row per record, no blank rows, no title or total lines.

If you’re practicing, send prompt 1, copy the result with the code block’s copy button and paste it at A1. It lands in column A: select that column and choose Data > Split text to columns (Google Sheets) or Data > Text to Columns with Comma as the delimiter (Excel).

In Excel, click a cell in the data and choose Insert > Table (Ctrl+T on Windows), with My table has headers ticked. A pivot table built on a table picks up pasted rows when it refreshes. Google Sheets uses whole columns instead (step 4).

3. Ask the assistant for the layout

In a new conversation, send prompt 2 with your questions, your header row and three invented rows under it, in the export’s formats (the same date style, the same decimals). That’s all the assistant needs to see.

The answer sorts your questions: breakdowns, such as sales by region, go in the pivot table; single numbers, such as open tickets, become headline formulas. If a question can’t be answered from your columns, change the question or add the column to the export. If the answer mentions a column you don’t have, say so and ask again.

4. Insert the pivot table

These menu paths were checked in October 2026.

• Excel: click a cell in the table, choose Insert > PivotTable, pick New Worksheet (New sheet in Excel for the web) and confirm.
• Google Sheets: select whole columns by clicking the column letters, from A to your last column, then choose Insert > Pivot table and create it on a new sheet. The data range should show column letters only, such as Data!A:G; if it shows row numbers, delete them. With whole columns, next month’s extra rows are included automatically.

Rename the new tab Summary.

5. Build the layout and add a chart

• Excel: in the PivotTable Fields pane, drag each field into Rows, Columns or Values, as the assistant suggested. Numbers in Values are summed and text is counted; click the field in Values to change that.
• Google Sheets: in the side panel, click Add next to Rows, Columns and Values, choose the fields and pick SUM or COUNTA under Summarize by. If a row with no label appears at the bottom (the empty rows of the whole columns), click Add next to Filters, choose the field you put in Rows and, under Filter by condition, keep only cells that aren’t empty.

Then add the chart the assistant suggested. In Excel for Windows, click inside the pivot table and choose Insert > PivotChart; on a Mac or the web, click inside it and pick a chart from the Insert tab. In Google Sheets, select the pivot table without its Grand Total row or column and choose Insert > Chart.

6. Add the headline numbers and the check

Leave three or four empty columns to the right of the pivot table, which grows when a new category appears, then put each label next to the assistant’s formula. For example, with the practice data (Region in column C, Amount in G):

Sales, North         =SUMIFS(Data!G:G, Data!C:C, "North")
Orders over 1,000    =COUNTIFS(Data!G:G, ">1000")
Rows in Data         =COUNTA(Data!A:A)-1
Total in Data        =SUM(Data!G:G)

The last two are your check:

• Rows in Data must equal the records exported: the count the system reports, or the export’s last row number minus one.
• The grand total of the pivot table must equal Total in Data, and both must match the system’s own total for the month. If the first pair differs, the pivot table is missing rows; if the second does, the export is incomplete, often because of a wrong date range or filter.
• A figure shown in both places, such as North’s sales, must agree, which catches a formula pointing at the wrong column.

Last, add a History tab with the same labels in column A, and paste the headline numbers into column B as values only (Paste special, in both apps), with the month on top. Each month gets a column that stays put when the Data tab changes.

7. Write the update note and test it on the newer month

Add a tab called How to update with template 3, filled in for your system. Then follow it line by line with the newer export, as if you had never seen the file.

If you’re practicing, send the second part of prompt 1 in the same conversation, paste October’s rows into a new workbook and split them into columns as in step 2: that’s your export. It has a new category and two misspelled rows on purpose, so you’ll recognize both problems when they turn up in real data.

You’re done when the newer month’s checks match, any new category appears in the pivot table and the chart, and History holds both months side by side. If a line of the note made you hesitate, rewrite it now. Then copy the headline numbers into this month’s report: that summary is finished, and next month’s starts from the note.

Three templates to copy

Prompt 1, practice data (optional: the first part for step 2, the second for step 7):

Create 200 rows of made-up sales data for a fictional office supplies distributor, for September 2026.

Columns: Order ID, Date, Region, Salesperson, Product category, Units, Amount
- Region: North, South, East or West
- Product category: Paper, Furniture, Printing, Technology or Cleaning
- Date: YYYY-MM-DD, within September 2026
- Amount: a whole number, no currency symbol, no thousands separator
- No commas inside any value

Output only the data as comma-separated values in one code block, header row first. If you can't fit 200 rows, stop after a complete row and tell me, and I'll ask you to continue.

Now create the data for October 2026 in the same format, with 230 rows, a new product category called Packaging, and two rows where Furniture is misspelled as Furnture.

Prompt 2, the layout:

I'm building a monthly summary in [Excel for Windows / Mac / the web, or Google Sheets], with the app in [English]. Each month I'll paste new data into a tab called Data and refresh a pivot table on a tab called Summary. Headers and three invented rows:

[headers and rows]

The summary must answer:
1. [question]
2. [question]
3. [question]

Please:
1. Say which questions the pivot table answers and which need a single headline number.
2. Give one pivot table layout: the fields for Rows, Columns, Values and Filters, and whether each value is a sum or a count. Use as few fields as possible.
3. Write each headline formula with SUM, SUMIFS or COUNTIFS, using whole-column references on Data (such as Data!G:G) and the function names and separator my app uses in my language.
4. Add two check formulas: the number of data rows and the total of the main amount column.
5. Suggest one chart for the most important result.

If a question can't be answered from these columns, say so and name the column it would need. Don't invent columns.

Template 3, the update note:

How to update this summary (written [date], tested on [month])

1. Export: in [system], open [report], set the dates to all of last month, export as CSV.
2. Clear: on Data, select every row below the headers and delete the rows (Excel: right-click > Delete > Table Rows).
3. Paste: copy the export without its header row into cell A2 of Data.
4. Refresh: Excel, right-click the pivot table > Refresh. Google Sheets updates on its own.
5. Check: Rows in Data = [records exported]. Grand total = Total in Data = [total in the system].
6. Keep: copy the headline numbers into the next column of History, as values only, with the month on top.

What can go wrong and how to check

The pivot table or the chart misses new rows

Rows in Data matches, but the grand total is lower than Total in Data: the pivot table reads a fixed range, say A1:G201, and this month has 230 rows. In Excel, build the pivot table on a table (step 2). In Google Sheets, click Edit under the pivot table, choose Select data range and change it to whole columns, such as Data!A:G. A Sheets chart covers only the cells you selected when you made it, so if a new category is missing, double-click the chart and widen the Data range on the Setup tab.

Dates or amounts stored as text

Total in Data shows 0 or too little, or dates sort oddly: the export wrote them as text, often because of a currency symbol, a stray space or a decimal comma. Type =ISNUMBER(G2) in an empty cell, pointing at an amount and then at a date: FALSE means text. Re-export with plain numbers if you can, or ask the assistant for a formula that converts them (Describe the formula you need in plain words shows how).

One category split in two

“Furniture” and “Furnture”, or “North” and “North “ with a trailing space, appear as separate rows. Fix it at the source if you can, for example with a drop-down list instead of free typing. Until then, Clean up a messy contact list with formulas the assistant writes for you shows a cleanup method that works on categories too.

Excel still shows last month

Excel pivot tables generally change only when you refresh them; in October 2026, Microsoft’s documentation lists automatic refresh for Microsoft 365 Insider participants only. The check catches a forgotten refresh: Total in Data changes and the grand total doesn’t.

Next month, and what comes after

When the next export is due, follow the note and time it, from opening the system to the last check. Write that next to what the hand-built summary used to take you, and you have your own before and after.

The summary gives you the figures; someone still writes the paragraph under them. Let the spreadsheet do the math and the assistant write the words picks up from here, and your History tab already holds the two months its commentary table starts from. If the figures are going to a meeting, Turn a long report into a ten-minute presentation takes them into a few slides. And if your export has a column of free-text comments, Hundreds of open comments sorted into themes gives each one a theme your pivot table can count.