How to Create a Sales Dashboard in Excel with ChatGPT

Building a sales dashboard in Excel is a great way to track performance, but the process can be slow and tedious, especially if you’re not a spreadsheet wizard. By using ChatGPT, you can streamline the entire workflow, from generating formulas to creating complex charts with simple, conversational language. This guide will walk you through exactly how to combine the power of Excel with the intelligence of ChatGPT to build a dynamic and insightful sales dashboard from scratch.

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 Use ChatGPT with Excel for Dashboards?

You might be wondering why you should add another tool to your workflow. The reality is that creating an effective dashboard in Excel often involves repetitive tasks, complex formulas, and design choices that can be time-consuming. ChatGPT acts as your data-analyst assistant, helping you bridge the gap between your raw data and a finished report.

Here’s how it helps:

• Saves Time: Instead of searching through help forums for the right formula syntax, you can just ask ChatGPT to write it for you. This applies to basic calculations like SUMIFs and more advanced functions for data analysis.
• Lowers the Technical Barrier: You don’t need to be an Excel expert to build a professional-looking dashboard. If you can describe what you want to see - like “a chart showing sales by region” - ChatGPT can give you the step-by-step instructions to create it.
• Automates Tedious Work: ChatGPT can quickly generate instructions for creating pivot tables, charts, and interactive elements like slicers, removing much of the manual clicking and configuration.
• Sparks New Ideas: Stuck on how to visualize your data? You can ask ChatGPT for recommendations on the best chart types to showcase specific sales metrics, helping you uncover insights you might have missed.

Getting Started: Your Dashboard Toolkit

To follow along with this tutorial, you’ll only need two things:

1. Microsoft Excel: Any recent version with support for PivotTables and Slicers will work perfectly.
2. A ChatGPT Account: The free version (GPT-3.5) is sufficient, but GPT-4 will often provide more accurate and detailed responses.

Step-by-Step: Building Your Sales Dashboard with ChatGPT

We'll break down the process into four manageable steps, from gathering your data to assembling the final dashboard. Let's get started.

Step 1: Get (or Generate) Your Sales Data

Every dashboard needs data. You can use your own sales data from a CSV export from your CRM (like Salesforce or HubSpot) or e-commerce platform (like Shopify). For this guide, we’ll use a simple, clean dataset that’s easy to work with.

If you don’t have data handy or just want to practice, you can ask ChatGPT to create some for you. This is a great way to build a template you can use later with your real information.

Try a prompt like this:

Act as a data analyst. Generate 25 rows of sample sales data in a CSV format. The columns should be: OrderDate, SalesRep, Region, Product, UnitsSold, and Revenue.

ChatGPT will output a clean, formatted table that you can copy and paste directly into an empty Excel sheet. It should look something like this:

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: Prepare Your Data by Formatting as an Excel Table

This is arguably the most important step for creating a dynamic dashboard. Before you do anything else, you need to format your raw data as an official Excel Table. This makes formulas easier to write and ensures your charts and pivot tables update automatically when you add new data.

1. Click on any cell within your data range.
2. Go to the Insert tab on the Ribbon and click Table (or press the shortcut Ctrl + T).
3. Ensure the My table has headers box is checked, and click OK.

Your data will now be formatted with alternating colors. Click on the Table Design tab that appears, and in the far-left corner, give your table a memorable name like "SalesData." This makes it much easier to reference in your prompts and formulas.

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 3: Ask ChatGPT to Build Your Dashboard Components

Now for the fun part. We will use ChatGPT to generate the instructions and formulas for each building block of our dashboard: Key Performance Indicators (KPIs), PivotTables, charts, and interactive filters.

Calculating KPIs

Your dashboard should highlight your most important sales metrics at a glance. Let’s start by creating a section for high-level KPIs like Total Revenue, Total Units Sold, and Average Deal Size.

Go to ChatGPT and use this prompt:

I have an Excel table named "SalesData". Write the Excel formulas for the following KPIs: 1. Total Revenue (from the "Revenue" column) 2. Total Units Sold (from the "UnitsSold" column) 3. Average Deal Size (Total Revenue / number of orders)

ChatGPT will provide the exact formulas you need:

• For Total Revenue: =SUM(SalesData[Revenue])
• For Total Units Sold: =SUM(SalesData[UnitsSold])
• For Average Deal Size: =AVERAGE(SalesData[Revenue]) or =SUM(SalesData[Revenue])/COUNT(SalesData[Revenue])

Simply copy these formulas into separate cells on a new, blank sheet in Excel that you’ll name “Dashboard.” Label them clearly so you know what each number represents.

Creating PivotTables and Charts

PivotTables are the engine of any great Excel dashboard, allowing you to quickly summarize large datasets. We'll use them to analyze revenue by region and by sales rep.

Let’s start with revenue by region. Use the following prompt:

Using my Excel table named "SalesData," provide step-by-step instructions to create a pivot table showing the total revenue for each region. Then, suggest the best chart type to visualize this and explain how to create it from the pivot table.

ChatGPT will return a clear set of instructions, similar to this:

To Create the Pivot Table:

1. Select any cell in your "SalesData" table.
2. Go to the Insert tab and click PivotTable.
3. Choose New Worksheet and click OK.
4. In the PivotTable Fields pane, drag the Region field to the "Rows" area.
5. Drag the Revenue field to the "Values" area. It should default to "Sum of Revenue."

Follow the steps and you'll have a summary table. Now for the chart.

To Create the Chart:

1. Click anywhere inside your newly created pivot table.
2. Go to the Insert tab and click Recommended Charts.
3. Excel will likely suggest a Clustered Column or Bar chart, which is perfect for comparing categories. Select one and click OK.

Now you have your first chart! Cut and paste this chart onto your "Dashboard" sheet. Repeat this process for other visualizations you want to see. For example, use this prompt to visualize sales performance:

Now give me instructions to create a pivot table and a bar chart showing the total revenue generated by each Sales Rep.

Follow the same process again, dragging "SalesRep" to Rows and "Revenue" to Values, and create a bar chart for it. Move that chart to your dashboard sheet as well.

Bringing It to Life with Slicers

Slicers are interactive buttons that filter your pivot tables (and the charts connected to them). They make your dashboard dynamic, allowing you or your team to drill down into the data without having to touch the underlying setup.

Let's add a slicer to filter by "Product".

Ask ChatGPT:

How do I add a slicer to my pivot table to filter by the "Product" field? Also, explain how I can connect this slicer to both of my pivot charts so it controls everything at once.

The instructions will be straightforward:

1. Click on one of your pivot charts to select it.
2. Go to the PivotChart Analyze tab and click Insert Slicer.
3. In the pop-up window, check the box next to Product and click OK.
4. The slicer will appear. Right-click the slicer and select Report Connections.
5. In the next window, check the boxes for all the pivot tables you want this slicer to control. Click OK.

Now, when you click a product name on the slicer, both of your charts will update automatically to show data only for that product. Move the slicer onto your dashboard sheet and arrange it next to your 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

Step 4: Design and Organize Your Dashboard Layout

The final step is to arrange the components on your "Dashboard" sheet to create a clean, easy-to-read layout. Here are some simple design tips:

• Give it a clean background: Go to the View tab and uncheck "Gridlines" to get a clear blank canvas.
• Organize logically: Place your high-level KPIs at the top, put your most important charts in the upper-left, and group related elements together.
• Customize your charts: Remove any unnecessary chart elements like gridlines for a cleaner look.

Common Troubleshooting Tips

Using ChatGPT for Excel is powerful, but it’s not foolproof. Here are a couple of things to watch out for:

• Double-check prompts and outputs: ChatGPT is a great tool, but its suggestions aren't always perfect. Always validate its formulas and process steps.
• Manually refresh your data: The dashboards you create are static, meaning you’ll need to refresh them manually or periodically update them with new data.

Final Thoughts

As you've seen, combining ChatGPT with Excel simplifies complex tasks into a straightforward, guided process. By handling everything from formula creation to chart-building instructions, this approach significantly cuts down the time and expertise needed to create an effective sales dashboard. It's a fantastic method for anyone looking to build custom reports without getting bogged down in spreadsheet technicalities.

While this method is powerful, it still involves manual steps like exporting CSVs, building pivot tables, and organizing the layout. The dashboard is also static - you'll need to manually refresh it with new data periodically. For a more automated solution, we built Graphed to take this process to the next level. We connect directly to your data sources like Salesforce, HubSpot, and Shopify, and let you build live, real-time dashboards just by describing what you want in plain English. Instead of giving you instructions, Graphed does the work for you, instantly turning your questions into interactive visualizations that are always up to date.