How to Create a Sales Report in Excel

Creating a sales report in Excel may sound like a classic office chore, but it's one of the most powerful ways to understand what's driving your business. We're going to skip the complex theory and build a practical, powerful, and easy-to-update report from scratch. This guide will walk you through preparing your data, using key formulas, summarizing with PivotTables, and creating an interactive dashboard to visualize your sales performance.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

First Things First: Prepare Your Sales Data

Before you can build any report, you need a solid foundation of clean, organized data. This is arguably the most important step, if your source data is messy, your report will be inaccurate and unreliable. Think of it as "garbage in, garbage out."

1. Gather and Organize Your Raw Data

Your sales information likely lives in a CRM like Salesforce or HubSpot, an ecommerce platform like Shopify, or even just a simple transaction log. The first step is to export this data as a CSV or Excel file and get it into a single worksheet.

Structure your data in a simple, tabular format. Each row should represent a single transaction or sale, and each column should represent a specific attribute of that sale. A good structure looks something like this:

• Order ID: A unique identifier for the sale.
• Order Date: The date the sale occurred.
• Sales Rep: The name of the salesperson responsible.
• Region: The geographical area of the sale (e.g., North, South, East, West).
• Product: The name of the item sold.
• Category: The product's category (e.g., Software, Hardware).
• Units Sold: The quantity of the product sold.
• Unit Price: The price of a single unit.

2. Format Your Data as an Excel Table

Once your data is organized, immediately format it as an Excel Table. This isn't just for looks, it makes your data dynamic and a lot easier to work with. To do this:

1. Click anywhere inside your dataset.
2. Go to the Insert tab on the Ribbon and click Table, or just use the shortcut Ctrl + T (or Cmd + T for Mac).
3. Make sure the "My table has headers" box is checked. Click OK.

Your data will now have alternating colored rows. The key benefits of using an Excel Table are:

• Auto-Expansion: When you add new rows of sales data, the table automatically expands to include them, ensuring your report always uses the latest information.
• Easy Formatting: Easily change the look and feel from the Table Design tab.
• Fuss-Free Formulas: Formulas are easier to read and automatically fill down the entire column.

Now is also a good time to add a column for "Revenue" or "Total Sale Amount". In the cell next to your first row of data (for example, in cell I2), create a simple formula:

=[@[Units Sold]]*[@[Unit Price]]

Because you're using an Excel Table, this formula will automatically apply to every row in the column. You can see the formula uses the column headers instead of cell references (like G2*H2), making it much easier to understand.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

3. Clean Up Inconsistencies

Look for common data entry errors. A salesperson might have their name entered as "Jon Smith," "jon smith," and "John Smith." Excel sees these as three different people. Look for inconsistencies in regions, product names, and other text fields. Use Excel’s Find and Replace functionality (Ctrl + H) to standardize them.

Creating Your Sales Report: Summarize and Visualize

With clean data in a proper Excel Table, you're ready for the fun part: turning that raw data into meaningful insights. We’ll primarily use PivotTables, Excel's most powerful reporting feature.

Step 1: Summarize Sales Data with a PivotTable

A PivotTable is an interactive tool that lets you summarize large amounts of data without writing a single formula. It's the engine of our sales report.

Let's create our first summary: Total Revenue by Region.

1. Click any cell inside your sales data Table.
2. Go to the Insert tab and click PivotTable.
3. A dialog box will appear. Since you're using a Table, the range will be correct. Choose "New Worksheet" to keep your report clean and click OK.

You'll now have a blank PivotTable layout on a new sheet and a PivotTable Fields pane on the right. This pane is where you control the report. It has four areas:

• Rows: Fields you drag here will appear as row labels.
• Columns: Fields you drag here will appear as column labels.
• Values: Fields here are what the PivotTable will calculate (sum, count, average, etc.). This is typically a number field like Revenue or Units Sold.
• Filters: Fields here let you filter the entire report.

To see Total Revenue by Region, simply:

• Drag the "Region" field to the Rows area.
• Drag the "Revenue" field to the Values area.

Instantly, Excel generates a summary table showing the total sales revenue for each region. You can format the numbers as currency for better readability by right-clicking one of the revenue numbers, selecting "Number Format," and choosing "Currency."

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 2: Add More Summaries to Your Report

Now, let's create a few more PivotTables to get a more complete picture of our sales performance. The process is the same for each - just drag and drop different fields.

Copy and paste your first PivotTable to create a new one quickly on the same sheet. Now you can modify the new one without starting over.

Example 1: Sales Performance by Rep

• In a new PivotTable, drag "Sales Rep" to the Rows area.
• Drag "Revenue" to the Values area.
• Tip: Click the small arrow on the "Sales Rep" field in the PivotTable and select "Sort" > "Largest to Smallest" to easily see your top performers.

Example 2: Monthly Sales Trend

• In another PivotTable, drag "Order Date" to the Rows area. Excel will automatically group the dates into Years, Quarters, and Months. You can collapse or expand these as needed. If it doesn’t group them automatically, right-click any date and choose "Group."
• Drag "Revenue" to the Values area.

Now you have a clear, month-by-month view of your sales performance.

Step 3: Visualize Your Data with Charts

Numbers in tables are great, but charts make trends and comparisons instantly obvious. A PivotChart is linked directly to a PivotTable, meaning it will update automatically when the data does.

Let's create a chart for each of our summaries.

1. Create a Bar Chart for Top Sales Reps

1. Click inside your "Sales Performance by Rep" PivotTable.
2. With the PivotTable selected, go to the PivotTable Analyze tab on the Ribbon.
3. Click PivotChart.
4. Choose a Bar Chart or Column Chart and click OK. Bar charts are excellent for comparing totals between different categories, like salespeople.

2. Create a Line Chart for Monthly Trends

1. Click inside your "Monthly Sales Trend" PivotTable.
2. Go to PivotTable Analyze > PivotChart.
3. Choose a Line Chart. This is the perfect chart type for showing data over a period of time. Click OK.

Feel free to clean up your charts by giving them clear titles ("Total Revenue by Sales Rep"), removing clutter like field buttons (right-click a button and choose "Hide All Field Buttons on Chart"), and adding data labels for clarity.

Putting It All Together: An Interactive Dashboard

A sales dashboard brings all your key charts and tables onto one screen, giving you a scannable, at-a-glance overview of performance. The best part? You can make it interactive with Slicers.

1. Arrange Your Charts on a New Sheet

Create a new worksheet in your workbook and name it "Dashboard." Then, copy and paste your PivotCharts (and even key PivotTables) onto this sheet. Arrange them in a logical way that's easy to read.

2. Add Slicers for Interactive Filtering

A Slicer is essentially a user-friendly filter button. Instead of digging into menus, you can just click a button to filter your report.

1. Click on any of your PivotCharts on the dashboard.
2. Go to the PivotTable Analyze tab and click Insert Slicer.
3. In the dialog that appears, check the boxes for the fields you want to filter by - "Region" and "Product Category" are great options. Click OK.

You’ll now have two floating Slicer menus. Try clicking a region in the Region slicer - you’ll see the chart you based it on update. But what about the other charts?

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

3. Connect a Slicer to Multiple Charts

To make the whole dashboard interactive, you need to connect each slicer to all the charts.

1. Right-click on the Region slicer.
2. Select Report Connections.
3. In the dialog box, check the boxes for all the PivotTables in your workbook that you want this slicer to control. Click OK.

Repeat this process for your "Product Category" slicer. Now, when you click on the "North" region, all connected charts will instantly filter to show data just for that region. This allows you to slice and dice your sales data in countless ways, asking and answering questions on the fly.

4. Keeping Your Report Updated

Here's the beauty of this setup. When next month's sales data comes in, all you have to do is:

1. Paste the new rows of data at the bottom of your source data Excel Table. The table will automatically expand.
2. Go to your dashboard.
3. On the Ribbon, go to the Data tab and click Refresh All.

That's it. Every PivotTable and PivotChart in your workbook will update instantly with the new information. Your weekly or monthly reporting task just went from a few hours of work to a few seconds.

Final Thoughts

Building a sales report in Excel boils down to a few key steps: organize your data into an Excel Table, summarize it with PivotTables, visualize it with PivotCharts, and make it interactive with Slicers. While it requires some upfront setup, the result is a powerful and automated report that saves you countless hours down the line.

That initial step - exporting CSVs from different platforms, cleaning them, and formatting them - can still be a weekly manual chore for many teams. We built Graphed to automate that entire process. By securely connecting directly to your tools like HubSpot, Salesforce, or Shopify, we get your sales data into one place and let you build comprehensive, real-time dashboards just by asking questions in plain English. It's the perfect way to spend less time wrangling spreadsheets and more time acting on valuable insights.