Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Create a Sales Report in Excel with AI

Cody Schneider••6 min read

Building a sales report in Excel no longer means spending hours fighting with pivot tables and VLOOKUPs. Thanks to a few powerful but often overlooked AI features, you can turn your raw sales data into insightful reports and dashboards in just a few minutes. This article will show you step-by-step how to use Excel’s built-in AI to handle the heavy lifting, so you can spend less time wrangling spreadsheets and more time acting on what your data is telling you.

GraphedGraphed

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.

Watch Graphed demo video

First, Get Your Sales Data Ready for Analysis

Before any AI magic can happen, you need to provide it with clean, organized data. Your sales numbers might live in a CRM like Salesforce or HubSpot, an ecommerce platform like Shopify, or even just a simple spreadsheet you update manually. The key is to bring it together into a single, structured format.

For a basic sales report, you'll want to include metrics like:

  • Date of Sale: Necessary for any trend analysis.
  • Product/Service Sold: To see what's popular.
  • Sales Representative: To track team performance.
  • Region/Territory: For geographical insights.
  • Units Sold: The quantity of products sold.
  • Price per Unit: The cost of a single item.
  • Total Revenue: The most important number, often calculated as Units Sold * Price per Unit.

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.

The Most Important Step: Format as a Table

Once you have your data in an Excel sheet, with each metric in its own column, you need to format it as an official Excel Table. This one step is what unlocks most of Excel's AI capabilities. It tells Excel that your data is a structured set, not just random cells.

Here’s how to do it:

  1. Click anywhere inside your data set.
  2. Go to the Home tab on the ribbon.
  3. Click on Format as Table.
  4. Choose a visual style you like (this is just cosmetic).
  5. A pop-up will confirm the data range. Make sure the checkbox for "My table has headers" is ticked if your columns have titles. Click OK.

Your data will now be neatly formatted in alternating colors, with filter buttons on each header. You're ready to start analyzing.

Using Excel’s ‘Analyze Data’ Feature for Instant Insights

The ‘Analyze Data’ feature is Excel’s core AI engine for ad-hoc analysis. It automatically scans your data table and suggests relevant charts, pivot tables, and summary statistics. Better yet, you can ask it questions in plain English, just like you would a data analyst.

You can find ‘Analyze Data’ on the Home tab, typically all the way to the right.

How to Use It:

  1. Click any cell inside your newly created Excel Table.
  2. Click the Analyze Data button on the Home tab.
  3. A new pane will open on the right side of your screen. Instantly, Excel will provide several suggested visualizations based on what it thinks is interesting in your data. You might see things like "Sales by Region" or "Sum of Revenue over time."

Asking Questions with Natural Language

This is where the real power lies. Instead of using the pre-built suggestions, you can type questions into the search bar at the top of the ‘Analyze Data’ pane. It’s surprisingly good at understanding what you mean.

Try asking questions like:

  • "Show me total revenue by sales rep as a bar chart"
  • "What were the monthly sales for last year?"
  • "Which product had the highest units sold?"
  • "What is the average revenue per sale in the West region?"

As you type, Excel will generate a corresponding chart or pivot table on the fly. If you like what it created, simply click the + Insert button to add it directly to your worksheet. In seconds, you can populate your workbook with insightful visuals that would have taken much longer to build manually.

GraphedGraphed

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.

Watch Graphed demo video

Projecting Future Performance with the Forecast Sheet

Beyond analyzing past performance, Excel's AI can also help you predict future trends based on your historical data. The Forecast Sheet feature uses a standard statistical model (Exponential Triple Smoothing) to create a forward-looking sales forecast, complete with best-case and worst-case scenarios.

Creating a Sales Forecast:

  1. Your data needs at least two corresponding data series: a timeline column (with dates or times) and a numeric column (like Total Revenue or Units Sold).
  2. Select these two columns of data.
  3. Go to the Data tab on the ribbon.
  4. In the "Forecast" group, click Forecast Sheet.
  5. A new window will appear showing you a line chart with your historical data and a projection for the future.
  6. You can adjust the Forecast End date to project further out. Under "Options," you can also adjust the confidence interval, which controls the width of the "best-case" and "worst-case" range. A 95% confidence interval is standard.
  7. Click Create. Excel will generate a new sheet containing both a table with the forecasted data and a professional-looking line chart visualization.

This is extremely useful for setting sales goals, managing inventory, and allocating resources for the upcoming months or quarters.

Building a Simple Sales Dashboard

Once you've used ‘Analyze Data’ and ‘Forecast Sheet’ to generate a half-dozen useful charts and tables, you can assemble them into a simple, interactive dashboard. This gives you a one-page overview of your sales performance that's easy to read and share.

  1. Create a new, blank worksheet in your Excel workbook and name it something like "Sales Dashboard".
  2. Go back to your other sheets and one-by-one, copy (Ctrl+C) the charts and pivot tables you created and paste (Ctrl+V) them onto your new dashboard sheet.
  3. Arrange them logically. You might put high-level KPIs at the top, trend charts in the middle, and breakdowns by rep or region at the bottom.
  4. Pro Tip: Add Slicers for Interactivity. Slicers are user-friendly buttons that let you filter your dashboard's data without touching a filter dropdown.

With just a little bit of arranging and a couple of slicers, you have transformed several static charts into a dynamic report that allows you and your team to explore the data in a much more intuitive way.

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.

Final Thoughts

Using Excel’s AI features like ‘Analyze Data’ and ‘Forecast Sheet’ automates much of the manual work of sales reporting, helping you uncover key trends and performance metrics far more quickly than traditional methods. By formatting your data properly and using natural language questions, you can generate clear, actionable insights without needing to be an expert in formulas or pivot tables.

While Excel is a massive step up from pure manual analysis, the process still often begins by manually downloading CSV files from your CRM, Shopify store, and ad platforms every week. We built Graphed to eliminate that last manual step. We provide direct, real-time connections to your sales and marketing platforms, so your dashboards are always up-to-date automatically. Instead of asking for one chart at a time, you can ask us to build an entire multi-platform dashboard with a simple prompt like, "Show me a dashboard comparing sales performance by rep from Salesforce and marketing spend from Facebook Ads for the last 60 days.”

Related Articles