How Do You Build a Monthly Report in Excel on Mac?
Quick Answer: Pull last month's raw data into a clean Table, summarize it with a PivotTable, build 2–3 supporting charts, drop key numbers into a summary sheet with a simple layout, and save a copy as your report template so next month takes 15 minutes instead of 2 hours. Mac Excel Shortcuts app also adds a keyboard shortcut for Remove Duplicates, a step this workflow leans on that otherwise has no default shortcut on either platform.
Goal & sample dataset
Most finance, ops, and analyst roles have some version of "the monthly report." The goal of this workflow is a repeatable process: raw export in, polished one-page summary out, in roughly the same amount of time every month.
Sample dataset: a monthly sales export with columns Date, Sales Rep, Region, Product, Units, Revenue, covering ~2,000 transactions.
Estimated time
First build: 45–60 minutes. Once the template exists, subsequent months: 15–20 minutes.
Skills required
Comfort with PivotTables
Basic charting (column/line charts)
Simple formulas: SUM, SUMIFS, AVERAGE, percentage change
Step-by-step solution
Step 1: Set up your workbook structure
Create three tabs: Raw Data (the unmodified export, converted to a Table with Command-T), Analysis (PivotTables and calculations), and Report (the presentation-ready summary).
Step 2: Import and clean the raw data
Check for duplicates, fix text-as-numbers, confirm dates parsed correctly. A single duplicated row can silently inflate your headline revenue number.
Step 3: Build the core PivotTable
Click inside your Raw Data table.
Insert > PivotTable, place it on the Analysis tab.
Drag Region to Rows, Revenue and Units to Values.
Add Date to Filters, grouped by month.
Step 4: Calculate month-over-month change
Add =(ThisMonthTotal - LastMonthTotal) / LastMonthTotal, format as a percentage, and use conditional formatting for visible trends.
Step 5: Build 2–3 supporting charts
Use Insert > Chart > Clustered Column for regional comparison, and a line chart from a Date-grouped PivotTable for trends over time.
Step 6: Assemble the Report tab
Paste charts as linked pictures (Edit > Paste Special > Paste Link) so refreshing the Analysis tab updates the Report tab automatically. Add 3–5 headline numbers using GETPIVOTDATA.
Step 7: Save as a reusable template
Save a copy as Monthly Report Template.xlsx with the Raw Data tab cleared out. Next month, paste in the new export and refresh (Data > Refresh All, or Command-Option-F5).
Faster keyboard-only workflow
Command-T — convert the new export to a Table.
Command-Option-F5 — refresh all PivotTables and data connections.
Shift-Command-Down Arrow then Command-D — extend and fill helper formulas.
Control-Page Down / Control-Page Up — navigate between tabs.
Checks to run before you send it
A fast report is only useful if the numbers are right:
Reconcile the totals. Compare the pivot's grand total with a plain SUM of the source column. They should match exactly.
Confirm the date range. Make sure the filter covers the full month, not a partial period.
Compare with last month. Large swings usually mean a data problem, not a business change.
Scan for odd values. Look for negatives, blanks and very large numbers in the Raw Data table.
Check labels and units. Titles, currency symbols and headers should be the same as last month.
Refresh everything once more. Use Data > Refresh All before you copy anything out.
Sending the report
As Excel: copy the Report tab, then paste values only with ⌃⌘V so the recipient does not see links to your raw data.
As PDF: set the print area, then use ⌘P and the PDF menu, as in Save and Save As shortcuts.
In an email body: paste the key table as a picture so formatting survives.
Keeping the template healthy
Clear last month's rows but keep the Table headings so formulas stay intact.
Keep a changelog note on the Analysis tab, listing any assumption you changed.
Review the template every quarter and remove charts nobody looks at.
Productivity tips
Lock the Raw Data tab's structure with Review > Protect Sheet once cleaned.
Use named ranges for key totals like
TotalRevenue.For Windows migrators, Mac Excel Shortcuts app lets you use your old Windows shortcut muscle memory directly for navigation and selection shortcuts that don't map over cleanly.
FAQs
Should headline numbers reference the PivotTable directly or a separate formula?
Referencing via GETPIVOTDATA is more robust — it still finds the right value even if the pivot reshuffles rows.
What's the fastest way to update the report each month without breaking formatting?
Never delete the Raw Data table structure — paste new rows into the same Table range and let it auto-expand, then refresh.
How do I handle a new column in next month's export?
Add it to the Table, then refresh the pivot and add the new field. Do not paste over the headings.
Why does the pivot not include my new rows?
The new data is outside the Table range. Paste it directly under the last row so the Table expands, then refresh.
Can I automate the whole process?
Power Query, available in Excel for Mac, can import and clean a file the same way each month. For smaller jobs, the template approach here is simpler. See where Power Query lives in Excel for Mac.
What is the quickest way to remove duplicate rows?
Use Data > Remove Duplicates on the Raw Data Table, and check the row count afterwards.
