How to Create a Tracking Dashboard in Excel with ChatGPT

Building a dashboard in Excel used to mean spending hours wrestling with complex formulas or searching for YouTube tutorials. Now, you can use ChatGPT as your personal data assistant to speed up the entire process. This article will show you exactly how to structure your data, prompt ChatGPT for the right formulas and chart ideas, and assemble a professional tracking dashboard in Excel 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

First, Plan Your Dashboard's Purpose

Before you write a single formula, you need to know what story you want your dashboard to tell. A great dashboard answers important questions at a glance, so starting with a clear goal is the most critical step.

Define Your Key Metrics (KPIs)

What are the 3-5 numbers that matter most? Don't try to track everything. A cluttered dashboard is an ignored dashboard. Focus on the Key Performance Indicators (KPIs) that directly reflect your goals.

For example:

• A sales team might track: Total Revenue, Deals Won, Average Deal Size, and Sales Cycle Length.
• A marketing team might track: Website Visitors, Lead Conversion Rate, Cost Per Lead, and Return on Ad Spend (ROAS).
• An e-commerce store owner might track: Total Sales, Average Order Value (AOV), Conversion Rate, and Top Selling Products.

Choose the metrics that give you a true pulse-check on performance. Everything in your dashboard should serve the purpose of clarifying these numbers.

Sketch a Simple Layout

You don't need design software. Grab a pen and paper or open a simple drawing tool and sketch out a rough layout. Where will your most important metrics go? How will you group related charts? This simple act organizes your thoughts and gives you a clear blueprint to follow. A common layout places high-level KPIs at the top, trend charts in the middle, and more detailed breakdowns in tables at the bottom.

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

Prepare Your Data for Analysis

ChatGPT is impressive, but it can’t fix messy data. Your dashboard's accuracy depends entirely on the quality of your source data. This is where the old saying "garbage in, garbage out" really applies.

Structure Your Data in a Table

The best way to organize your data for an Excel dashboard is in a simple, structured table. Here are the rules:

• One Header Row: Each column should have a unique, descriptive header (e.g., "Date," "Product Category," "Units Sold," "Sale Price"). Don't merge cells in your header row.
• One Record Per Row: Each row should contain a single transaction or record. For sales data, a row would be one line item from a sale.
• No Blank Rows or Columns: Keep your data contiguous. Blanks within the data are fine, but empty rows or columns can break formulas.

Once your data is formatted this way, select any cell within it and press Ctrl + T (or Cmd + T on a Mac) to turn it into an official Excel Table. Give it a descriptive name (like "SalesData") in the "Table Design" tab. This makes your formulas much easier to read and write, as they'll automatically update when you add new data.

Use ChatGPT as Your Excel AI Assistant

This is where the magic happens. Instead of googling for formulas or digging through Excel menus, you can simply ask ChatGPT what you need in plain English. The key is to be specific in your prompts.

Always provide context in your prompts by including:

• The goal: What are you trying to calculate?
• The source: Reference your Excel Table and column names.
• The format: Ask for the specific Excel 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

Generating Complex Formulas on Demand

Let's say we have our "SalesData" table with columns for "Date," "Product Category," and "Total Revenue." Here are some example prompts and the formulas ChatGPT can generate.

Example 1: A Simple Conditional Sum

You want to find the total revenue for a specific product category.

Your Prompt:

"I have an Excel Table named 'SalesData'. It has a column called 'Product Category' and another called 'Total Revenue'. Write me an Excel formula to calculate the total revenue for the 'Electronics' category."

ChatGPT's Likely Response:

=SUMIF(SalesData[Product Category], "Electronics", SalesData[Total Revenue])

This formula is clean, readable, and perfectly structured because you referenced your table by its name.

Example 2: Calculating Month-over-Month Growth

This is an essential business metric that often involves messy formulas. Let's build a summary table to calculate it.

Your Prompt:

"I have a summary table for my dashboard. Cell B5 has the total revenue for this past month, and cell C5 has the total revenue for the month before that. Give me an Excel formula to calculate the month-over-month percentage growth."

ChatGPT's Likely Response:

=(B5-C5)/C5

You can then format that cell as a percentage to see your growth rate clearly.

Getting Chart Recommendations and Guidance

ChatGPT can’t directly create a chart in your Excel file, but it's an excellent advisor for choosing the right visualization and configuring it.

Your Prompt:

"In my Excel sheet, I have monthly revenue data from January to December. What is the best chart type to visualize this trend, and what are the steps to create it? Also, suggest a title for the chart."

ChatGPT would correctly suggest a line chart to show trends over time. It would then provide step-by-step instructions:

1. Select your monthly data (two columns: Month and Revenue).
2. Go to the "Insert" tab on the Excel ribbon.
3. In the "Charts" group, click the "Line Chart" icon and choose a 2-D Line chart.
4. It might even suggest a title like "Monthly Revenue Trend - 2024."

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

Writing VBA Macros to Automate Your Dashboard

If you're feeling adventurous, you can ask ChatGPT to write VBA code — small scripts that automate tasks in Excel. A common use case for a dashboard is a refresh button.

Your Prompt:

"Write an Excel VBA macro that refreshes all the data connections and pivot tables in the active workbook. Name the Macro 'RefreshDashboard'."

ChatGPT's Likely Response:

Sub RefreshDashboard()
    ' This macro will refresh all data connections and PivotTables in the workbook.
    ThisWorkbook.RefreshAll
    MsgBox "Dashboard has been refreshed successfully!"
End Sub

You can then copy this code, open the VBA Editor in Excel (Alt + F11), insert a new module, and paste it in. Then, just insert a shape or button on your dashboard and assign this macro to it for a one-click refresh.

Putting It All Together: Build Your Dashboard

With your plan, data, and formulas in hand, it's time to build the dashboard. The best practice is to structure your workbook into three separate tabs:

1. Data: This sheet holds your master data table (e.g., "SalesData"). You only ever add or update data here.
2. Calculations: This "behind-the-scenes" sheet pulls from the data tab to create small summary tables that power your charts. Use the formulas from ChatGPT here. For example, you’d have a small table for total revenue, another for sales by category, and another for monthly trends.
3. Dashboard: This is your presentation layer. It should contain no raw data — only your charts and KPI displays that are linked to the Calculations sheet. Hiding the complexity makes your dashboard cleaner and faster.

On your Dashboard tab:

• Insert your charts: Create the line charts, bar charts, and pie charts you planned, but make sure they're linked to your summary tables on the Calculations sheet, not the raw data.
• Display Your KPIs: A great way to feature your primary KPIs is with large, dynamic text cards. Simply insert a Text Box, then in the formula bar, type = and click on the cell in your "Calculations" sheet that contains the metric (e.g., =Calculations!B2). The text box will now display that value and update automatically.
• Arrange Everything: Now, organize your charts and KPIs according to the sketch you made at the beginning. Use the grid and alignment tools to make everything look neat and professional.
• Add Interactivity with Slicers: Select any of your charts that are based on an Excel Table or Pivot Table, and go to the "Insert" tab and click "Slicer." Choose a field like "Product Category" or "Region." This creates an interactive button menu that allows you or your team to filter the entire dashboard with a single click.

Final Thoughts

By pairing your knowledge of Excel with the instant assistance of ChatGPT, you can shift your focus from remembering complicated formulas to effectively telling stories with your data. This combination empowers anyone to build professional-looking, insightful dashboards without needing to be a spreadsheet guru.

As you get comfortable with this workflow, you might find the manual process of exporting, cleaning, and updating data becomes the next bottleneck. At that point, you're ready for a more connected solution. Instead of using conversational AI just for formula suggestions, we built Graphed to let you use natural language to create the entire dashboard. We connect directly to your live data from sources like Shopify, Google Analytics, and Salesforce, so there are no CSVs to download. Just describe the dashboard you want to see, and it appears in seconds, always up-to-date and ready to share.