How to Create a Metrics Dashboard in Excel with ChatGPT

Turning a spreadsheet of raw data into a clear, insightful metrics dashboard can feel like a daunting task. While Excel is incredibly powerful, building dynamic charts, writing complex formulas, and making it all look professional takes time and expertise. This is where ChatGPT can step in as a surprisingly effective data analysis assistant, helping you generate formulas, structure charts, and build a dashboard faster than you ever thought possible. This article will walk you through exactly how to do it.

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 for an Excel Dashboard?

At first, it might seem odd. ChatGPT is a language model, not a spreadsheet program. It can't directly manipulate your Excel file. However, you can think of it as a creative partner or an on-demand tutor that accelerates your workflow. Instead of searching forums for the right formula or trying to remember how to create a pivot chart, you can simply ask.

Here’s how it helps:

• Formula Generation: This is its superpower. You can describe what you want to calculate in plain English, and ChatGPT will write the exact Excel formula for you. This is a massive time-saver for functions like SUMIFS, VLOOKUP, INDEX/MATCH, or complex nested formulas.
• Visualization Recommendations: Not sure if your data works best as a line chart, a bar chart, or a pie chart? Describe your data and your goal, and ChatGPT can suggest the most effective visualization type and explain why.
• Step-by-Step Instructions: Ask "How do I add a slicer to my pivot table?" and you’ll get clear, sequential steps you can follow directly in Excel.
• Data Cleaning & Structuring Ideas: If your data is messy, you can paste a small sample and ask for advice on how to clean it up or structure it properly for analysis.

Before You Start: Preparing Your Data in Excel

The success of any dashboard hinges on the quality and structure of your source data. The principle of "garbage in, garbage out" is especially true here. Before you even open ChatGPT, take a few minutes to get your Excel sheet in order.

1. Keep it Clean and Tidy

Ensure your data is in a simple, tabular format. This means:

• Each column has a single, clear header in the first row (e.g., "Date," "Campaign," "Ad Spend," "Conversions").
• Each row represents a single record or entry.
• There are no merged cells or empty rows disrupting the data block.

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

2. Use Excel Tables

This is arguably the most important tip. Select your data range and format it as a Table (you can do this by clicking anywhere in your data and pressing Ctrl + T or going to Insert > Table). Formatting your data as a Table gives you several advantages:

• Dynamic Ranges: As you add new rows of data, the table automatically expands. This means any charts or pivot tables based on it will update automatically, without you needing to manually adjust the data source range.
• Easy to Read Formulas: Tables use structured references (like =SUM(CampaignData[Ad Spend])) instead of cryptic cell ranges (like =SUM(C2:C597)), making your formulas much easier to understand.
• Built-in Filtering: Tables come with filter and sort buttons by default, making it easy to quickly inspect your data.

3. Define Your Key Performance Indicators (KPIs)

Decide what you want to measure before you start building. Are you a marketer tracking campaign performance, a sales leader monitoring team quotas, or a founder watching top-line revenue? Clarity about which questions you're trying to answer will help guide your dashboard creation journey.

A few common examples include:

• Total Revenue
• Conversion Rate (Conversions / Clicks)
• Cost Per Acquisition (Ad Spend / Conversions)
• Total Leads by Source

Step-by-Step: Building Your Dashboard with ChatGPT's Help

Let’s use a simple marketing campaign dataset as our example. Imagine we have a table named CampaignData with columns: Date, Channel, Campaign, Clicks, Ad_Spend, and Conversions.

Our goal is to build a dashboard that shows total spend and conversions, conversion rate by channel, and a trend of clicks over time.

Step 1: Create a Calculation Sheet and Ask ChatGPT for Formulas

It's a best practice to keep your raw data, your calculations, and your dashboard on separate sheets. Create a new sheet named "Calculations." This is where we'll summarize the key metrics from our CampaignData table.

Now, let's turn to ChatGPT.

Example Prompt for a KPI Summary:

I have an Excel table named 'CampaignData'. It has columns named 'Ad_Spend', 'Clicks', and 'Conversions'. I need the Excel formula to calculate the following in separate cells:

1. Total Ad Spend
2. Total Clicks
3. Total Conversions
4. Overall Cost Per Conversion (Ad Spend / Conversions)

ChatGPT's Likely Response:

Certainly! Here are the formulas you can use based on your table CampaignData:

• For Total Ad Spend: =SUM(CampaignData[Ad_Spend])
• For Total Clicks: =SUM(CampaignData[Clicks])
• For Total Conversions: =SUM(CampaignData[Conversions])
• For Overall Cost Per Conversion: =SUM(CampaignData[Ad_Spend])/SUM(CampaignData[Conversions])

Simply copy these formulas from ChatGPT and paste them into your "Calculations" sheet in Excel. You now have your main KPIs ready to go.

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: Get Advice on Creating Charts

ChatGPT can't create the chart for you, but it can tell you the best way to do it. Let’s say we want to visualize our conversion rate by channel.

First, we’ll need to create a small summary table in our "Calculations" sheet. Ask ChatGPT for help with this, too.

Example Prompt for Pivot Table & Chart Advice:

Using my 'CampaignData' table in Excel, I want to show the 'Conversion' rate (which is SUM of Conversions / SUM of Clicks) for each unique 'Channel'. What is the best way to do this, and what kind of chart should I use to visualize it?

ChatGPT will guide you:

It will likely recommend creating a PivotTable. It might give you these steps:

1. Go to Insert > PivotTable and select CampaignData as your source.
2. Drag Channel to the Rows area.
3. Drag Clicks and Conversions to the Values area (they should default to Sum).
4. Now, create a Calculated Field to find the Conversion Rate. Go to the "PivotTable Analyze" tab, click Fields, Items, & Sets > Calculated Field. Name it "Conversion Rate" and use the formula: ='Conversions' / 'Clicks'.
5. Once your PivotTable is created, a bar chart is a great way to compare the performance of different channels. Select your PivotTable, go to Insert > Bar Chart, and choose a clustered bar chart.

By following these AI-generated instructions, you can easily create a professional-looking chart without fumbling through menus.

Step 3: Make Your Dashboard Interactive with Slicers

A static dashboard is good, but an interactive one is even better. Slicers are user-friendly filter buttons that allow you to "slice and dice" your data. Let's add one to filter by Channel.

Example Prompt for Slicers:

I have a PivotChart in Excel based on a PivotTable. How do I add a 'Slicer' so I can filter the chart by the 'Channel' field?

ChatGPT will provide a quick how-to:

1. Click on your PivotChart or PivotTable to activate the PivotTable Analyze tab.
2. In that tab, click Insert Slicer.
3. A dialog box will appear with all your fields. Check the box next to Channel and click OK.
4. A slicer for Channel will appear. You can now connect it to multiple charts. Right-click the slicer, select "Report Connections" and check all of the PivotTables/PivotCharts you want a single slicer to be connected to.

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: Putting It All Together

The final step is the design. Create a third sheet and call it "Dashboard." This is your canvas.

• Copy and paste your charts from the "Calculations" sheet onto this new "Dashboard" sheet.
• Use "Cards" to display your primary KPIs (Total Spend, Total Conversions). You can do this by inserting a text box (Insert > Text Box) and then in the formula bar, type = and click the cell in your "Calculations" sheet that contains the metric. This links the text box to the cell value.
• Arrange your charts, KPIs, and slicers in a logical, clean layout. Remove gridlines for a more polished look (View > Uncheck Gridlines).

Limitations of Using Excel and ChatGPT

While this method is powerful, it’s important to understand its limitations:

• Data is Static: Your dashboard only reflects the data currently in your spreadsheet. To get updated numbers, you need to manually add new data to your CampaignData table. There are no live data connections.
• Potential for Errors: Always double-check the formulas ChatGPT provides. While usually accurate, it can make mistakes, especially with highly complex or poorly described requests.
• Manual Work: Even with ChatGPT’s help, this is still a very manual process. You have to shuttle between two applications and do all of the final clicks yourself.

Final Thoughts

Creating an Excel dashboard with ChatGPT as your co-pilot is an excellent way to combine the power of AI with the flexibility of a spreadsheet. By letting AI handle the heavy lifting of formula creation and complex instructions, you can focus on arranging the insights and telling a compelling story with your data. It significantly lowers the barrier to creating sophisticated, interactive reports, especially for those who aren't Excel power users.

While this method speeds up manual reporting significantly, it still relies on static data exports and a lot of copying and pasting. At Graphed we decided to solve this problem by eliminating the manual steps entirely. Instead of exporting data to Excel, we connect your data sources (like Google Analytics, Shopify, Facebook Ads) directly to your dashboards. There's no need to export a CSV, and you can use languages like "create a dashboard from my AdWords data" to automatically generate dashboards that refresh in real-time. It's a faster way to get insights that are always up-to-date, eliminating the need for data wrangling.