How to Make a Monthly Sales Report in Excel, With a Layout to Copy
Build a monthly sales report in Excel from an order export: the layout, SUMIFS formulas for revenue and orders, month and year comparisons and charts.
- Excel
- Sales Reporting
- Reporting Workflows

On this page
- What goes in a monthly sales report?
- How do you prepare the order export?
- How do you calculate revenue and orders for the month?
- How do you compare with last month and last year?
- How do you show the trend, top products and channels?
- How do you reuse the report next month?
- Where does Glow Reports fit?
- Common questions
A monthly sales report answers four questions: how much you sold, how many orders it took, how that compares with last month and last year, and what drove it. In Excel you can build it from one order export with a table, a few SUMIFS formulas and two pivot tables. Below is the layout, the formulas to copy, the charts to add and the checks that keep the numbers right when next month's export arrives.
What goes in a monthly sales report?
A monthly sales report holds revenue, number of orders, average order value, the change against last month and the same month last year, then the split by product and by channel or sales rep. Six figures and two breakdowns cover what most readers ask.
Here is a layout you can rebuild on one worksheet. Each row is a block of the report.
| Block | What it shows | How you build it |
|---|---|---|
| Summary | Revenue, orders, average order value | SUMIFS and COUNTIFS |
| Change | Against last month and the same month a year ago | The same formulas, shifted |
| Trend | Revenue for the last 12 months | Pivot table and column chart |
| Top products | The 10 products with the most revenue | Pivot table, Top 10 filter |
| By channel or rep | Revenue and orders per channel or sales rep | Pivot table and bar chart |
| Notes | Two or three sentences on what changed and why | Written by you |
Keep the definitions next to the figures. "Revenue" can mean before or after refunds, with or without tax and shipping. Write the one you use under the summary, so the reader doesn't have to guess.
How do you prepare the order export?
Put the export in its own sheet, turn it into an Excel table and check that dates are real dates and amounts are real numbers. A table grows with the rows you paste later, so every formula and pivot table built on it picks up next month's data.
- Paste or open the export in a sheet named . One row per order line, one header per column.
- Click inside the data and select InsertTable. Microsoft describes how a table expands as you add rows Microsoft Support.
- Name it in Table DesignTable Name. The formulas below use that name.
- Check the date column with in a helper column. FALSE marks a date stored as text.
The examples assume the columns , , , and . Rename them in the formulas to match your export. Text dates, extra spaces and numbers stored as text are covered in how to clean data in Excel before a report. When the month comes in several files, combine them first with Power Query.
How do you calculate revenue and orders for the month?
Type the first day of the month in a cell, say , then sum the amounts between that date and the last day of the month with SUMIFS. COUNTIFS counts the rows the same way. Every figure in the summary reads , so changing one date updates the whole report.
=SUMIFS(Orders[Amount], Orders[OrderDate], ">=" & B1, Orders[OrderDate], "<" & EOMONTH(B1, 0) + 1)returns the last day of the month Microsoft Support. Adding 1 and using instead of keeps orders placed on the last day after midnight, which a timestamp such as 31/10 14:32 would otherwise drop. SUMIFS needs ranges of the same size, which a table column guarantees Microsoft Support.
The number of orders depends on the shape of the export. When each row is one order, count the rows:
=COUNTIFS(Orders[OrderDate], ">=" & B1, Orders[OrderDate], "<" & EOMONTH(B1, 0) + 1)Many shop exports have one row per product line, so an order with three products takes three rows. Shopify's order export works this way, and leaves many fields blank on the extra lines Shopify Help Center. Counting rows then overstates orders, and an order total repeated or left blank on those lines skews revenue. Sum the line amounts, or the total from the first line only. In Microsoft 365, count distinct order IDs:
=ROWS(UNIQUE(FILTER(Orders[OrderID], (Orders[OrderDate] >= B1) * (Orders[OrderDate] < EOMONTH(B1, 0) + 1))))Average order value is revenue divided by orders. The details, including refunds and orders at zero, are in how to calculate average order value.
How do you compare with last month and last year?
Reuse the revenue formula with a shifted start date: for last month and for the same month last year. Then divide the difference by the older figure. The year-on-year line matters most for seasonal sales, where October against September says little.
Put last month's start date in with and last year's in with . Copy the revenue and order formulas next to the current month, pointing at and . The change is then:
=IF(C5 = 0, "", (B5 - C5) / C5)Format the cell as a percentage. The leaves the cell empty when last month had no sales, instead of a #DIV/0! error. Write "vs last month" and "vs same month last year" in the headers, so nobody reads a monthly change as an annual one.
How do you show the trend, top products and channels?
Build pivot tables on the table: one with months in rows for the trend, one with products in rows sorted by revenue, one with channels or reps in rows. Add a chart to the trend and to the channel split, and a slicer to filter all of them at once.
- Select InsertPivotTable with as the source Microsoft Support. Drag to Rows and to Values. Group the dates by Months and Years, as shown in how to group a pivot table by month.
- For top products, put in Rows and in Values. Open the Product filter arrow and select Value FiltersTop 10.
- For channels, put in Rows. Add and to Values.
- Click a pivot table and select InsertSlicer, then pick . A slicer filters tables and pivot tables with one click and shows which filter is on Microsoft Support.
For charts, a column chart suits revenue by month and a horizontal bar chart suits products or channels, where labels are long. Types of charts explains which chart fits each figure. Limit the trend to 12 or 13 months so the same month last year stays visible.
How do you reuse the report next month?
Paste the new rows under the table, change the date in , then select DataRefresh All. The formulas move to the new month, and the pivot tables and charts pick up the new rows. The notes are the one part you rewrite each time.
Before you send it, run three checks:
- The revenue in the summary equals the month's total in the trend pivot table. A gap means a filter is on or some dates are text.
- The helper column shows no FALSE on the new rows.
- The order count matches the shop or CRM back office for the same month, within refunds and test orders.
The same build works in Google Sheets. InsertPivot table creates the pivot tables Google Docs Editors Help, and SUMIFS takes the same arguments Google Docs Editors Help. Replace the table names with column ranges such as .
Where does Glow Reports fit?
The workbook above serves you well once it's built. The cost comes back every month: pasting the export, checking dates, rewriting notes, exporting the PDF. When that routine takes most of a morning, read how to replace manual Excel reports.
Glow Reports starts from the same export. You upload the CSV or Excel file and ask for the monthly sales report in plain language, with your definition of revenue. The AI writes the SQL, builds the charts and drafts the text. The SQL sits under each figure, so you can check how orders were counted and edit the query when a definition is wrong. The report exports to PDF and stays in the workspace. The free plan gives 300 credits once, and a report costs 100 credits.
Common questions
Is there a free monthly sales report template for Excel?
Microsoft's template gallery has sales report templates. They help with the look, but you still have to map your export onto them. The layout in this article starts from the export, so the formulas fit your columns.
Should revenue include tax and shipping?
Decide once and write it under the summary. Many teams report revenue after refunds and before tax, with shipping shown apart. What matters is that every month uses the same rule.
Why doesn't my SUMIFS match the pivot table?
Usually because of dates stored as text, which SUMIFS skips, or a filter left on in the pivot table. Check the helper column for FALSE and clear the pivot filters.
How do I make a daily sales report instead?
Use the same table. Replace the month boundaries with one date: .
I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.
More from El Mehdi BenaneUpload your CSV or Excel export to Glow Reports and get the sales report with the SQL behind each figure.
Keep reading
Ad Hoc Reporting Requests: How to Cut the Queue So Business Teams Answer Their Own QuestionsEl Mehdi Benane, 4 min read
How to Calculate Days Between Two Dates in Excel, and Fix DATEDIF #NUM!El Mehdi Benane, 4 min read
XLOOKUP vs VLOOKUP vs INDEX MATCH: Which One to Use on Your ExportsEl Mehdi Benane, 4 min read