Graphed LogoGraphed
Login

How to Create a Dashboard in Excel with ChatGPT

Cody Schneider•

You can turn a spreadsheet of raw data into a dynamic Excel dashboard by simply having a conversation. Using ChatGPT as your copilot, you can generate complex formulas, get instructions for building charts, and even write automation scripts without knowing a single line of code. This guide will walk you through exactly how to team up with ChatGPT to create insightful and tonal dashboards in Excel, step-by-step.

Why Use ChatGPT for Excel Dashboards?

Building a dashboard in Excel usually involves a steep learning curve. You need to know which formulas to use, how to manipulate pivot tables, and which chart type will best tell your story. This is where ChatGPT completely changes the game. It acts as an on-demand data analyst, ready to help you navigate Excel's complexities.

Here’s why it works so well:

  • It saves you time. Instead of searching through forums and help articles for the right formula, you can just ask for it. ChatGPT can generate VLOOKUP, SUMIFS, COUNTIFS, or complex nested formulas in seconds.

  • It democratizes advanced features. Want to add interactive buttons or dropdown menus to your dashboard? You’d typically need to know VBA (Visual Basic for Applications) to do that. With ChatGPT, you can simply describe the functionality you want, and it will write the code for you.

  • It acts as a brainstorming partner. Not sure what metrics to include or the best way to visualize your sales data? You can describe your dataset to ChatGPT and ask for suggestions on key performance indicators (KPIs) and appropriate chart types.

Think of it less as a tool that builds the dashboard for you and more as an expert assistant that guides you through every step, speeding up the entire process and empowering you to do things you might not have known how to do on your own.

First Things First: Prepare Your Data

Before you can build anything, you need a solid foundation. AI, like any analytics tool, works best with clean, well-structured data. Taking a few minutes to clean up your spreadsheet will save you a massive headache later on.

Here’s a quick checklist for getting your data ready:

  • Use a simple tabular format. Your data should be organized in columns and rows, with a single header row at the very top. Each column should represent a distinct field (e.g., Date, Product, Region, Sales Amount), and each row should represent a single record.

  • Use clear, simple headers. Use straightforward titles like "Revenue" or "Customer_Name". Avoid using merged cells in your headers.

  • Eliminate blank rows and columns. Make sure there are no entirely empty rows or columns breaking up your dataset.

  • Ensure consistent data types. The "Date" column should only contain dates, and numerical columns like "Sales" or "Quantity" should only contain numbers. Formatting inconsistencies here are the number one cause of formula errors.

  • Turn your data into an Excel Table. This is a simple but powerful step. Click anywhere in your data, go to the "Insert" tab in Excel, and click "Table". This makes your data much easier to manage, reference in formulas, and use for charts and pivot tables.

Once your data is clean and formatted as a table, you're ready to start building.

Step-by-Step: Creating Your Dashboard with ChatGPT

Let's walk through building a sales performance dashboard using a sample dataset. Our goal is to see monthly revenue, sales by region, and performance by product category.

Step 1: Define Your Dashboard's Goal and KPIs

First, figure out what story you want your dashboard to tell. A good dashboard answers specific questions. For our example, let's say we want to answer:

  • What is our total revenue over time?

  • Which region is performing the best?

  • Which product categories are our top sellers?

You can even use ChatGPT for this part. You could prompt it with: "I have sales data with columns for 'Date,' 'Region,' 'Product Category,' and 'Revenue.' What are some key questions I could answer with a dashboard?"

Step 2: Generate Formulas to Summarize Your Data

Dashboards rely on summary data, not raw rows of transactions. We'll create a new sheet in our workbook called "Dashboard" and use it to build our summary tables. Then, we can use ChatGPT to generate the formulas we need.

Let's say our data is in a table named "SalesData".

To summarize sales by region, we could prompt ChatGPT like this:

"Write an Excel formula to sum the 'Revenue' column from my 'SalesData' table, but only for the 'East' region found in the 'Region' column."

ChatGPT would likely return a SUMIFS formula:

=SUMIFS(SalesData[Revenue], SalesData[Region], "East")

You can create a small table on your Dashboard sheet listing each region and use this formula to calculate the total sales for each one. This summary table will be the source for our charts.

To summarize sales by month, we can do something similar:

=SUMIFS(SalesData[Revenue], SalesData[Date], ">=2023-01-01", SalesData[Date], "<=2023-01-31")

Again, you'd create a table with months and drop a version of this formula into a cell for each month.

Step 3: Ask for Chart Creation Instructions

While ChatGPT can't click the buttons in Excel for you, it can give you precise, easy-to-follow instructions. Once you have a summary table (e.g., Region and Total Sales), you can prompt ChatGPT.

Example Prompt:

"I have a summary table in Excel on my 'Dashboard' sheet. Column A has region names (A2:A5) and Column B has total sales (B2:B5). Give me step-by-step instructions to create a nice-looking bar chart from this data."

ChatGPT will guide you:

  1. Select the data range (A2:B5).

  2. Go to the 'Insert' tab on the Excel ribbon.

  3. In the 'Charts' group, click on 'Insert Column or Bar Chart.'

  4. Choose the '2-D Clustered Bar' chart.

  5. From there, it can even help you customize it, providing instructions to add data labels, chart titles, and remove gridlines.

Follow these steps for each of your summary tables. You might create a line chart for sales over time, a bar chart for sales by region, and another for product performance.

Step 4: Make It Interactive with Slicers

Slicers are user-friendly buttons that let you filter your dashboard charts. The easiest way to use them is by basing your charts on a PivotTable.

First, create a PivotTable from your main data source. Then, ask ChatGPT for instructions on connecting slicers.

Example Prompt:

"I created a PivotChart in Excel to show sales by region. How do I add slicers so I can filter the chart by 'Product Category' and 'Year'?"

ChatGPT will walk you through the process:

  1. Click on your PivotChart to bring up the 'PivotChart Analyze' tab.

  2. Click on 'Insert Slicer'.

  3. A dialog box pops up showing your available data fields. Check the boxes for 'Product Category' and 'Year'.

  4. Click 'OK', and two interactive slicer boxes will appear. You can now click buttons to filter your dashboard dynamically.

Step 5: Write VBA Code for a "Reset Filters" Button (Optional Advanced Step)

What if you have several slicers and want a single button that quickly clears them all? This kind of automation typically requires writing VBA code, a task that stops most people in their tracks. But not with ChatGPT.

Prompt Example:

"Write an Excel VBA macro that clears all the filters on all slicers on the active worksheet."

ChatGPT will generate the VBA code for you:

It can further instruct you on how to implement this: Go open your worksheet and click "Developer" on the Excel ribbon. Click on "Visual Basic," then insert a module and paste the code. On your worksheet, insert a shape, assign the macro to it, and now you have a button that resets all the filters with a single click.

Tips for Better ChatGPT Prompts

How well ChatGPT can help you relies entirely on how well you ask. Here are a few quick tips to get better, more accurate results:

  • Be specific and provide context. Instead of saying "it doesn't work," say, "I'm getting a #VALUE! error with this formula in cell B2." Also, tell it what version you're on, your data structure, and what you’ve already tried. Providing headers or table names is very effective ('My table is called 'SalesData').

  • One query at a time. Don't overload one prompt asking for a formula, chart type, and macros or scripts all at once. Start with your formulas and verify them first. Then ask for chart creation instructions.

  • Use iteration and refinement. Start with general prompts and then iterate by adding more details. If an answer isn’t correct, refine it by asking ChatGPT to rephrase or add more details.

  • Ask for explanations. After ChatGPT generates code or a formula, ask for an explanation. For example, "Explain this formula to me simply," or "What are the advantages and disadvantages of using a bar chart versus a pie chart for this data?" These prompts improve not just the immediate solution but your overall understanding.

Final Thoughts

Combining ChatGPT with Excel empowers you to move beyond basic spreadsheets and build powerful, interactive dashboards without needing to become a formula guru or VBA programmer. It effectively lowers the technical barrier, letting you focus on the insights hidden in your data rather than the mechanics of finding them.

While uploading CSVs to ChatGPT and asking for instructions is a big step up from the old manual methods, there’s an even faster path. We designed Graphed to remove these manual steps entirely. Instead of struggling with spreadsheets, we connect directly to your SaaS tools like Google Analytics, Shopify, and Salesforce. This lets you build real-time, interactive dashboards just by describing what you want in plain English, and they update automatically so you're always looking at fresh data.