How to make a monthly sales report in Excel (step by step)

What you’ll build

A one-page report with a summary block (revenue, orders and average order value against last month), a breakdown by region and product, a trend chart, and a short commentary. Once it’s set up, next month’s report is a paste, a refresh and a few sentences.

Step 1: Start with clean, flat data

You need one row per order (or order line) with at least a date, an amount and the categories you want to break down by, such as product, region or channel.

Before reporting, fix the things that silently distort totals: duplicate orders, amounts stored as text, dates in mixed formats and region names spelled several ways. The data cleaning checklist covers each one. Then click inside the data, press Ctrl+T and name the table Sales (Table Design → Table Name).

Step 2: Add a month column

Grouping by month is easiest with a column that holds the first day of each order’s month. Add a column called Month to the table:

EOMONTH returns the last day of the previous month; adding 1 gives the first day of the order’s month. Format the column as mmm yyyy. Because it’s a real date, it sorts correctly, unlike text such as “September”.

Step 3: Build the summary block

On a new sheet called Report, type the first day of the reporting month in B1, for example 2026-09-01. Then build the headline numbers with SUMIFS and COUNTIFS:

Format D3 as a percentage. Change B1 next month and the whole block updates. For a year-on-year comparison, use EDATE(B1, -12) and EDATE(B1, -11) as the date bounds.

Step 4: Break it down with a PivotTable

1. Click inside the Sales table and choose Insert → PivotTable → New Worksheet.
2. Drag Region to Rows, Month to Columns and Revenue to Values.
3. Right-click a revenue value and choose Show Values As → % of Row Total if you want each region’s mix, or keep the sums.
4. Add a Slicer for Product or Channel (PivotTable Analyze → Insert Slicer) so readers can filter.

For a fixed “top five products this month” list in Excel 365, a single formula does it:

Step 5: Chart the trend

Select the PivotTable with months as columns, or a small table of month and revenue, and choose Insert → Line Chart (or a column chart for fewer than 12 months). Keep it readable:

• One measure per chart. Don’t put revenue and order count on two axes.
• Label the latest value and remove the legend if there’s only one series.
• Use a bar chart for comparing regions or products, sorted largest first.

Step 6: Write the commentary

Numbers without explanation leave readers guessing. Two or three sentences is enough:

1. What happened: “Revenue was $107,848 in September, up 2% on August, from 234 orders.”
2. Why: “Growth came from the East (up 33%) and South (up 18%), which more than offset a 23% drop in the West.”
3. What next: “Find out what changed in the West and contact customers there who haven’t reordered.”

Check every figure you quote against the summary block. If something looks odd — a huge jump, a month with no orders — look at the underlying rows before publishing; it’s often a duplicate or a typo.

Step 7: Make next month a five-minute job

• Paste next month’s rows at the bottom of the Sales table; it expands automatically.
• Change the month in B1.
• Choose Data → Refresh All to update the PivotTables.
• If the data arrives as a file, import it with Power Query (Data → Get Data → From File) so the refresh brings it in too.

The fast way

In MindySheets, open the export and choose Report → Monthly trends or Executive summary. Mindy runs the same calculations — totals, month-on-month change, breakdowns, top products — over every row, adds charts, writes the commentary and lets you save it as PDF or Word. Every number links back to the calculation that produced it, so you can check it before it goes to your manager.

Frequently asked questions