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 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.
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 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.