How to Create a Monthly Sales Report in Excel with ChatGPT

Tired of losing your Monday morning to the same old monthly sales report in Excel? The hours spent wrangling CSVs, fixing formulas, and updating charts are a frustrating routine for many. This guide will show you how to use ChatGPT as a powerful assistant to dramatically speed up your Excel reporting workflow. You'll learn how to get AI-powered help with everything from generating sample data and writing complex formulas to creating charts and even automating the whole process with VBA.

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

Why Bring ChatGPT into Your Excel Workflow?

Integrating ChatGPT into your reporting process isn't about replacing Excel, it's about upgrading your skills without spending months learning complex functions. Think of it as a brilliant co-pilot who knows every Excel trick in the book. Instead of hunting through forums for the right formula, you can just ask for it in plain English.

Here are the key benefits:

• Effortless Formula Generation: Forget trying to remember the difference between VLOOKUP and INDEX(MATCH) or the complex syntax of SUMIFS. You can describe your goal, like "show me total sales for the 'West' region," and ChatGPT will provide the exact formula you need.
• Instant Data Cleaning Ideas: If your data is messy - full of extra spaces, inconsistent formatting, or mixed text/number formats - ChatGPT can provide formulas and steps to clean it up in minutes.
• Quick Dummy Data: Need to build a report template before the real data is available? Ask ChatGPT to generate a realistic sample dataset with specific columns, saving you the time of making it up yourself.
• Step-by-Step Guidance: Beyond formulas, you can ask for guidance on best practices for structuring reports, choosing the right charts, and organizing your workbook for clarity and efficiency.
• Beginner-Friendly Automation: For those repetitive updates, ChatGPT can even write simple VBA (Visual Basic for Applications) macros to automate tasks like fetching new data or formatting your report with one click.

Step 1: Get Your Raw Sales Data Ready

Before you can write a single formula or build a chart, your foundation has to be solid. The most common point of failure in any report is poorly structured source data. Garbage in, garbage out.

The Ideal Data Structure

Your raw sales data should live on its own dedicated sheet in your workbook, often named "Data" or "RawData." It needs to be in a simple, tabular format where:

• Each column represents a specific attribute (e.g., Date, Product, Region).
• Each row represents a single transaction or record.
• There are no merged cells or empty rows disrupting the table.

Here’s what an ideal table structure looks like:

Pro Tip: Once your data is in Excel, immediately convert it to an official Excel Table by selecting any cell within your data and pressing Ctrl + T. This makes your data dynamic, meaning formulas and charts that reference it will automatically include new rows as you add them.

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

Step 2: Generate Sample Data with ChatGPT (Optional)

If you're building a new report from scratch, you might want to create a template before the official data arrives. ChatGPT is perfect for quickly generating realistic demo data.

You can use a detailed prompt to get exactly what you need. Open ChatGPT and send a prompt like this:

Act as a sales data analyst. Please generate 30 rows of sample sales data for November 2023 in a clean CSV format. Include the following columns:

ChatGPT will generate the data in a neat block you can copy. Simply select the generated text, copy it, and paste it into cell A1 of an empty Excel sheet. If the data pastes into a single column, go to the Data tab and use the Text to Columns feature, selecting "Comma" as the delimiter.

Step 3: Build Your Sales Report Dashboard

Now, create a new sheet in your workbook and name it "SalesReport" or "Dashboard." This is where your summary tables and charts will live. It will pull all its information from your "RawData" sheet, keeping your presentation layer separate from your data layer - a key best practice.

Calculating Key Performance Indicators (KPIs)

Your dashboard should feature high-level KPIs at the top for an at-a-glance view of performance. Let's ask ChatGPT to write the formulas for us. We'll assume your data is in an Excel Table named "SalesData."

Total Revenue

This is the most critical metric. Here’s how you’d ask for the formula.

“I am creating a monthly sales report in Excel. My data is in an Excel table named ‘SalesData’. I need the formula to calculate the sum of the column named ‘TotalRevenue’.”

Likely Response:

=SUM(SalesData[TotalRevenue])

Total Units Sold

“Now I need the formula to sum the ‘UnitsSold’ column in the same ‘SalesData’ table.”

Likely Response:

=SUM(SalesData[UnitsSold])

Average Order Value (AOV)

This metric is a bit trickier because it requires finding unique orders. It’s a great test of ChatGPT’s ability.

“I want to calculate the Average Order Value (AOV). I have a column for ‘TotalRevenue’ and another column named ‘OrderID’. The formula should be the total revenue divided by the count of unique order IDs. Can you provide the formula for Excel 365?”

Likely Response:

=SUM(SalesData[TotalRevenue])/COUNTA(UNIQUE(SalesData[OrderID]))

In your report, you can lay these out clearly with labels. One cell contains the label (e.g., "Total Revenue") and the cell next to it contains the formula.

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 4: Answer Deeper Questions with Detail Tables

KPIs give you the "what," but detail tables help you find the "why." These are summary tables that break down performance by category, region, product, or sales rep. Using ChatGPT, you can generate the formulas for these without having to build a single PivotTable.

Sales Breakdown by Region

“In my ‘SalesData’ table, I want to calculate the total revenue from the ‘TotalRevenue’ column for each region listed in the ‘Region’ column. In my report sheet, I have the region name "North" in cell A5. What formula do I use in cell B5 next to it?”

Likely Response:

=SUMIFS(SalesData[TotalRevenue], SalesData[Region], A5)

You can now list your regions ('North', 'South', 'East', 'West') in cells A5 through A8 and drag this formula down to automatically calculate the total revenue for each one.

Top 5 Selling Products

“I need a dynamic array formula for Excel 365 to show me my top 5 selling products by total revenue. My data is in the ‘SalesData’ table and contains the columns ‘ProductName’ and ‘TotalRevenue’.”

Likely Response:

=LET(
    uniqueProducts, UNIQUE(SalesData[ProductName]),
    sumRevenue, SUMIFS(SalesData[TotalRevenue], SalesData[ProductName], uniqueProducts),
    sortedData, SORT(HSTACK(uniqueProducts, sumRevenue), 2, -1),
    TAKE(sortedData, 5)
)

Pasting this single formula into one cell will create a two-column, five-row spill range showing your top products and their revenue without any extra work. This is the kind of task that used to take multiple helper columns and complex formulas, now reduced to a simple conversation with an AI.

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

Step 5: Visualize Your Data with Charts

While ChatGPT can't click the buttons in Excel for you, it can act as an expert consultant, recommending the best chart for your data and giving you the exact steps to create it.

“I have a two-column table showing total sales revenue broken down by each sales region. What is the best type of chart to visualize this in Excel and what are the steps?”

Likely Response:

"A Bar Chart or a Column Chart would be ideal for comparing revenue across different regions. Here are the steps to create one:

1. Select the data in your summary table, including the headers (e.g., Region and Total Revenue).
2. Go to the Insert tab on the Ribbon.
3. In the Charts group, click on the 'Insert Column or Bar Chart' icon.
4. Choose a Clustered Bar Chart or Clustered Column Chart from the options.
5. Finally, give your chart a clear title, like 'Monthly Revenue by Region'."

You can use this same approach to get advice on creating line charts for performance over time, pie charts for category breakdowns, and more.

Bonus: Ask ChatGPT to Write a VBA Macro

If you're creating the same report structure every month and only the raw data changes, you can automate the update process with VBA. Don't know how to code VBA? You don't need to.

Imagine you download a new CSV each month called new_sales.csv. You could ask ChatGPT to write a macro that automatically imports this data.

"Write an Excel VBA macro named 'ImportMonthlyData.' The macro should first clear all contents from the sheet named 'RawData'. Then, it should prompt the user to select a CSV file and import that file's data into cell A1 of the 'RawData' sheet."

Within seconds, ChatGPT will generate the VBA code. You'd then follow these steps in Excel:

1. Press ALT + F11 to open the Visual Basic Editor (VBE).
2. Go to Insert > Module to open a new code window.
3. Paste the code generated by ChatGPT into this module.
4. Close the VBE and save your workbook as a Macro-Enabled Workbook (.xlsm file type).
5. You can then run this macro each month from the Developer tab to completely automate your data import process.

Final Thoughts

This process transforms creating a monthly sales report from a chore into a creative problem-solving session. By offloading the tedious work of formula lookup and repetitive tasks to ChatGPT, you can focus on what actually matters: analyzing the data, understanding an organization’s performance, and sharing impactful insights your team can act on.

While using ChatGPT as your Excel copilot is a huge improvement, you still have to manage CSV files, update spreadsheets, and build the report manually. To solve this once and for all, we created Graphed. After connecting your data sources like Shopify, Salesforce, or Google Analytics one time, you can ask for a complete dashboard in plain English. Graphed automatically generates interactive, real-time reports that stay updated, so you never have to repeat the monthly reporting cycle again.