How to Create an Analytics Dashboard in Excel with ChatGPT

Using ChatGPT to build an analytics dashboard in Excel can turn a process that used to take hours of lookups and Googling into a quick conversation. It's perfect for generating complex formulas, getting ideas for charts, and automating tedious tasks without needing to be an Excel wizard. This guide will walk you through preparing your data and using simple prompts to create a functional and insightful dashboard from scratch.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Use ChatGPT with Excel for Dashboards?

Traditional dashboard creation in Excel often involves a steep learning curve. You need to know which formulas to use (VLOOKUP, INDEX-MATCH, SUMIFS), how to structure Pivot Tables effectively, and which charts best represent your data. This process can be slow and frustrating, especially if you're not a full-time analyst.

ChatGPT acts as a co-pilot, bypassing much of that friction. Here’s how it helps:

• It saves time: Instead of searching for the right formula syntax, you can just describe what you need in plain English and get an answer instantly.
• It lowers the skill barrier: You don’t need decades of spreadsheet experience. If you can describe your goal, you can get the tools you need to achieve it.
• It helps you think like an analyst: Unsure of the best way to visualize your sales data? Ask ChatGPT for suggestions. It can recommend chart types and analysis techniques you might not have considered.

Essentially, you handle the strategy - what you want to measure - while ChatGPT handles the technical execution of writing the formulas and outlining the steps to get you there.

First, Prepare Your Data (The Most Important Step)

AI is powerful, but it's not a mind reader. For ChatGPT to give you accurate formulas and instructions, it needs to understand your data's structure. That means your data needs to be clean, organized, and in a simple tabular format.

Before you start prompting, make sure your Excel sheet follows these rules:

1. Use a simple table format. Your data should have one header row at the top, and each subsequent row should represent a single record. No merged cells in the headers or multiple header rows.
2. Give columns clear, simple names. Use names like "CampaignName" or "ConversionAmount" instead of vague titles. This makes it easier to refer to them in your prompts.
3. Remove empty rows and columns. Blank rows can break formulas and disrupt how Excel detects your full data range.
4. Ensure consistent data types. Make sure numbers are formatted as numbers and dates are formatted as dates. Inconsistent formats can lead to errors in calculations.

Throughout this tutorial, we’ll use a sample marketing dataset that looks like this:

Example: Marketing Campaign Data

Getting this part right saves you countless headaches later on. Clean data equals reliable analysis.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Create an Analytics Dashboard in Excel with ChatGPT: Step-by-Step

Once your data is clean and organized, you can start building the components of your dashboard. Think of it as creating individual building blocks - KPIs, tables, and charts - that you’ll assemble onto a single sheet at the end.

Step 1: Calculate Key Performance Indicators (KPIs)

Dashboards often start with high-level summary metrics at the top. You can use ChatGPT to generate the formulas for these KPIs quickly.

Let’s start with some basic totals. You can ask ChatGPT directly:

Prompt to ChatGPT:

"I have an Excel sheet with columns for Spend (column E) and Conversions (column F). Data runs from row 2 to 100.

ChatGPT's Response could be:

Now for something a little more complex, like Cost Per Conversion (CPC). Instead of trying to figure out the formula yourself, just ask.

Prompt to ChatGPT:

"Using the same sheet, give me the Excel formula to calculate the average Cost Per Conversion (Total Spend / Total Conversions)."

ChatGPT's Response:

You can calculate this with a single formula: `=SUM(E2:E100) / SUM(F2:F100)`

Simply copy these formulas, paste them into separate cells in your Excel sheet (typically near the top), and label them clearly (e.g., "Total Spend," "Total Conversions"). These are the first building blocks of your dashboard.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Generate Detailed Breakdowns with Pivot Tables

ChatGPT cannot directly create a Pivot Table in your Excel file, but it’s exceptionally good at giving you the exact, step-by-step instructions to create one yourself. Pivot tables are perfect for summarizing data by different categories.

Let's say you want to see how each campaign performed.

Prompt to ChatGPT:

"I want to create a Pivot Table in Excel. My data has columns named 'Campaign Name', 'Spend', and 'Conversions'. Walk me through creating a summary that shows the total spend and total conversions for each campaign."

ChatGPT's typical instructions would look like this:

1. Make sure your cursor is anywhere inside your data table.
2. Go to the Insert tab on the Ribbon and click on PivotTable.
3. Verify the data range and click OK. A new sheet will be created with the PivotTable builder.
4. In the "PivotTable Fields" pane:
5. Ensure the values are set to "Sum of Spend" and "Sum of Conversions." If not, click on the value and choose "Value Field Settings..." to set it to Sum.

Follow these instructions, and you'll have a summarized table in seconds without guesswork.

Step 3: Ask for Chart and Visualization Recommendations

What’s the best way to visualize your campaign performance? A pie chart? A bar chart? Instead of guessing, just ask ChatGPT for an expert recommendation.

Prompt to ChatGPT:

"I want to create a chart based on my Pivot Table, which shows 'Campaign Name' with 'Sum of Spend' and 'Sum of Conversions'. What is the best chart type to compare the performance of different campaigns side-by-side?"

ChatGPT will likely recommend a Column or Bar chart, as they're ideal for comparing categories. It might even suggest a combo chart to show spend and conversions on different axes.

Follow up with a prompt asking for instructions:

Prompt to ChatGPT:

"How do I create a column chart from that Pivot Table?"

ChatGPT's Response:

1. Click anywhere inside your Pivot Table.
2. Go to the PivotTable Analyze tab (or Analyze) in the Ribbon.
3. Click on PivotChart.
4. Select Column from the list and pick your preferred style. Then click OK.

Just like that, you have a dynamic chart linked to your Pivot Table. Any updates to your data will reflect in the chart after a refresh.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Putting It All Together in a Dashboard Layout

Now that you have your components—KPIs, tables, and charts—it's time to assemble them into an actual dashboard.

1. Create a new, blank sheet and name it "Dashboard."
2. Arrange your components:
3. Add Interactivity with Slicers:

Prompt to ChatGPT:

"How can I add a Slicer to my Pivot Table for the 'Date' column so I can filter the dashboard by month?"

Following GPT’s instructions—usually "Click PivotTable > Insert Slicer > Check 'Date'"—will add user-friendly buttons to your dashboard that filter your chart and table interactively.

Tips for Better Prompts and Acknowledging Limitations

While ChatGPT is a massive help, its output is only as good as your input. Here's a quick guide to improve your results:

How to Write Good Prompts

• Be Specific and Provide Context: Mention your column names, locations (e.g., "column A"), and overall goal ("I'm analyzing marketing spend").
• Break Down Complex Requests: Instead of asking "Build me a marketing dashboard," ask for each part: KPI formulas, Pivot Table steps, chart instructions.
• Iterate and Refine: If a formula shows errors or instructions are unclear, tell GPT: "That formula gave me an error, can you try a different approach?" for clarification.

What to Watch Out For (Limitations)

• It can’t read your file directly: Accurately describing your data’s structure is your responsibility. Keep your data clean and organized.
• Check the work: The AI might suggest inefficient or incorrect formulas. Always verify the results.
• It's a static system: To update your dashboard, manually refresh data sources, or re-export and clean new files. GPT does not automate live data updates.

Final Thoughts

ChatGPT is a fantastic assistant for creating Excel dashboards, turning a technical process into a conversational one. By clearly describing your data and goals, you can generate formulas, build Pivot Tables, and get visualization ideas efficiently.

While this workflow streamlines many tasks, it still requires manual data updates and maintenance. For a more automated, real-time solution, consider tools like Graphed. Instead of describing static spreadsheets, connect your data sources directly. Ask questions in plain English, and Graphed builds and updates dashboards automatically—no manual refreshes, just a live view of your business performance ready to share.