How to Create a Monthly Sales Report in Excel

Creating a monthly sales report in Excel doesn’t have to feel like wrestling a monster spreadsheet every few weeks. With the right approach, you can turn raw sales data into a clear, insightful report that helps you track progress and make smarter decisions. This guide will walk you through the entire process, step-by-step, from organizing your data to building an interactive dashboard.

First, What Should Go In Your Sales Report?

Before you start building, it’s important to know what you want to measure. A great sales report isn't just a data dump, it tells a story about your performance. While every business is different, a solid monthly report often answers these questions:

• How much revenue did we generate?

• Which products or services are selling best?

• How many new customers did we acquire?

• Which sales reps are top performers?

• What is our average deal size?

• Are we trending up or down compared to last month?

To answer these, you'll want to include key metrics like total sales revenue, units sold, sales growth, sales by region, and performance by individual representatives. Pick the metrics that align with your team's goals for the month or quarter.

Step 1: Gather and Structure Your Sales Data

Your report is only as good as the data you put into it. The first step is to get all of your raw monthly sales data into a single, clean table in Excel. This data might come from your CRM, an e-commerce platform like Shopify, or even a simple order log.

The key is to structure it as a clean, flat table where each row represents a single sale or line item. Each column should represent an attribute of that sale. A typical sales data table might look like this:

• Order ID: A unique identifier for the transaction.

• Date: The date the sale occurred.

• Sales Rep: The salesperson credited with the sale.

• Customer Name: The name of the client.

• Product/Service: What was sold.

• Units Sold: The quantity.

• Unit Price: The price per unit.

• Total Sale: Calculated as Units Sold * Unit Price.

Pro Tip: Format as an Excel Table

This is one of the most useful and underutilized features in Excel. Once your data is organized, convert it into an official Excel Table.

1. Click anywhere inside your data range.

2. Go to the Insert tab and click Table (or use the shortcut Ctrl + T).

3. Ensure the "My table has headers" box is checked, and click OK.

Your data range will now have banded rows and filtering options. But the real power is that tables automatically expand to include new rows you add, which makes refreshing your report later incredibly simple.

Step 2: Add Helper Columns with Formulas

Sometimes your raw data doesn't include everything you need for analysis. We can add a few extra "helper columns" to our table to make reporting much easier, especially when we get to PivotTables. For a monthly report, the most useful helper column is one that isolates the month.

Create a "Month" Column

Let’s say your 'Date' column is column B. In the first empty column of your table, add a header called "Month." In the first cell below the header, enter this formula:

Because you're using an Excel Table, this formula should automatically fill down for the entire column. Now you have a clean column with just the month name ("January," "February," etc.), which will be perfect for summarizing your monthly data.

You can use this technique to calculate other metrics too, like a commission amount or cost of goods sold, if needed.

Step 3: Analyze Performance with PivotTables

PivotTables are the engine of Excel reporting. They let you instantly summarize and rearrange thousands of rows of data to find trends and insights without writing a single complex formula. They sound intimidating, but they are surprisingly easy to use once you know the basics.

Here’s how to create one:

1. Click anywhere inside your official Excel Table.

2. Go to the Insert tab and click PivotTable.

3. Excel will automatically select your entire table. Just click OK to place the new PivotTable on a new worksheet.

You'll now see a blank PivotTable on the left and a "PivotTable Fields" pane on the right. This pane is your control center. It lists all the columns from your data table and has four areas at the bottom: Filters, Columns, Rows, and Values.

You just drag the fields you want to analyze into these areas. Let's create a few useful summaries.

Summary 1: Total Sales by Product

To see which products are driving the most revenue:

• Drag the Product/Service field into the Rows area.

• Drag the Total Sale field into the Values area.

Instantly, Excel will create a report showing the sum of sales for each product. Voila! You have your top-selling items.

Summary 2: Performance by Sales Representative

To see how your sales reps are performing against each other:

• Drag the Sales Repr field into the Rows area.

• Drag the Total Sale field into the Values area.

• Optional: Drag Units Sold into the Values area as well to see both revenue and volume.

This creates a simple leaderboard showing each rep's total contribution.

Using the "Month" helper column we created earlier, you can now drag it into the Filters area to quickly filter the entire report for a specific month.

Step 4: Visualize Your Data with Charts and a Dashboard

Numbers are great, but visuals are better for quick understanding and sharing with others. Creating an Excel dashboard is just about arranging your key charts and figures on a single sheet so you can see the big picture at a glance.

Create a new, blank worksheet and name it something like "Sales Dashboard." This is where we'll assemble our final report.

Create Key Charts

You can create charts directly from your PivotTables. Click inside your "Sales by Product" PivotTable, go to the PivotTable Analyze tab, and click PivotChart.

• For Sales by Product/Rep: A Bar Chart is ideal. Bar charts are perfect for comparing quantities across different categories.

• For Monthly Sales Trends: A Line Chart is best. Line charts beautifully illustrate performance over time. To get this data, create a new PivotTable with the 'Month' field in the Rows area and 'Total Sale' in the Values area.

Build Your Interactive Dashboard

Now, copy and paste your newly created charts onto your "Sales Dashboard" sheet. Arrange them in a logical way. But here's how to make your dashboard truly powerful and interactive: add Slicers.

Slicers are basically stylish filter buttons. Instead of using the clunky filter dropdowns, you can click on a Slicer to filter all of your charts at once.

1. Click on any of your PivotCharts.

2. Go to the PivotChart Analyze tab and click Insert Slicer.

3. A dialog box will appear. Check the boxes for the fields you want to filter by, like "Sales Repr" and "Month." Click OK.

You'll see slicer boxes appear on your sheet. Now, by default, a slicer only controls the chart it was created from. To make it control all your dashboard charts, right-click the slicer, choose Report Connections, and check the boxes for all the PivotTables you want it to control.

Now, when you click an employee's name in the "Sales Repr" slicer, all the charts will instantly update to show data only for that person. It’s an easy way to build a professional-looking, interactive report.

Step 5: Maintaining and Refreshing Your Report Next Month

The best part about this setup? It's reusable.

When the next month's sales data is available, simply paste it into the empty rows at the bottom of your data table. Because you used an Excel Table, the table will automatically expand to include it.

Then, just go to the Data tab and click Refresh All. Every single one of your PivotTables and charts will update automatically with the new data. Your once-dreaded monthly reporting task is now a one-click process.

Final Thoughts

Building a monthly sales report in Excel boils down to a simple, repeatable process: structure your data in a clean table, summarize it with PivotTables, and visualize the key insights on a dashboard with charts and slicers. This method transforms a pile of raw numbers into a clear story about your business performance, making your data work for you.

Of course, this workflow still relies on manually exporting data, cleaning spreadsheets, and making sure everything refreshes correctly. At Graphed, we built a tool to get you straight to the insights. By connecting directly to your data sources like Salesforce, Shopify, or Google Analytics, our AI can build real-time dashboards for you instantly - just by asking for what you want in plain English. No more downloading CSVs or building PivotTables, just answers.