How to Create a Simple Dashboard in Excel with ChatGPT

Building a dashboard in Excel can feel like you need a degree in pivot tables and VLOOKUP. But what if you could have an expert analyst guide you through every formula and chart idea? This article will show you how to use ChatGPT as your personal Excel assistant to create a simple, effective dashboard from scratch, transforming raw data into clear, visual insights.

First, Why Even Use ChatGPT for an Excel Dashboard?

Before jumping into the "how," let's quickly cover the "why." Manually wrangling data and remembering complex formulas in Excel can bring any project to a grinding halt. Using ChatGPT as a creative partner for this process solves several common headaches:

• It eliminates "formula phobia." Can't remember the exact syntax for a SUMIFS or an INDEX(MATCH)? Just describe what you need in plain English (e.g., "how do I sum sales from the West region for Q1?") and ChatGPT will write the formula for you.

• It speeds up your workflow. Instead of searching through forums and tutorials, you can get instant answers tailored to your specific data. This shifts your time from tedious setup to actual analysis.

• It helps you brainstorm. Not sure what kind of chart best represents your data? Ask ChatGPT. You can describe your dataset and your goal, and it will suggest appropriate visualizations like bar charts, line graphs, or pie charts to tell a clear story.

Think of it not as an automatic dashboard builder, but as a super-intelligent co-pilot who handles the technical grunt work, leaving you free to focus on the insights.

Step 1: Get Your Data Ready for Analysis

This is the most important step. ChatGPT is powerful, but it relies on an organized dataset to give you good advice. The principle of "garbage in, garbage out" is especially true here. Before you start building, make sure your data is clean and structured correctly.

Best Practices for Data Preparation:

• Use Proper Column Headers: Give each column a unique and descriptive name (e.g., "Sale Date," "Sales Rep," "Region," "Revenue"). Avoid vague headers.

• Keep Your Data in a Single Table: Don't scatter your information across multiple sections of a worksheet. All related data should be in one contiguous block.

• No Merged Cells or Blank Rows/Columns: Merged cells and empty rows/columns interrupt data flow and make functions and charting features behave unpredictably.

• Use Excel's "Format as Table" Feature: This is a game-changer. Select your data range and click Home > Format as Table. This not only makes your data look neat but also turns it into a dynamic object. Formulas will automatically update as you add new rows, saving you a ton of manual work.

Here’s an example of a good, clean dataset we’ll use for our dashboard. It’s a simple sales report.

Example Sales Data:

Once your data looks like this, save the file and get ready to ask ChatGPT for help.

Step 2: Define Your Dashboard's Purpose

A good dashboard answers specific questions at a glance. What do you need to know? For our sales example, we might want to answer:

• What is our total revenue?

• Who are our top-performing sales reps?

• Which region generates the most revenue?

• What is the monthly sales trend?

Having clear questions will help you formulate better prompts for ChatGPT and ensure you’re building something useful, not just a random collection of charts.

Step 3: Build Your Dashboard with ChatGPT's Help

This is where the fun begins. We’ll build our dashboard piece-by-piece using simple, conversational prompts. Create a new sheet in your Excel workbook and name it "Dashboard." This is where all your KPIs and charts will live.

Part 1: Calculating Key Metrics (KPIs)

KPIs are the big, essential numbers that give you a high-level view of performance. We'll start by calculating Total Revenue, Total Units Sold, and Average Sale Value.

Go to ChatGPT and describe your setup. A good prompt provides context.

Example Prompt:

"I'm creating a sales dashboard in Excel. My data is on a sheet named 'SalesData' in a table named 'Sales_Table'. The table has columns for 'Revenue' and 'Units Sold'. Can you give me the Excel formulas for: 1. Total Revenue (sum of the 'Revenue' column) 2. Total Units Sold (sum of the 'Units Sold' column) 3. Average Revenue per Sale"

ChatGPT will likely respond with something like this:

"Of course! Here are the formulas you need: 1. For Total Revenue: =SUM(Sales_Table[Revenue]) 2. For Total Units Sold: =SUM(Sales_Table[Units Sold]) 3. For Average Revenue per Sale: =AVERAGE(Sales_Table[Revenue])"

Now, go to your "Dashboard" sheet in Excel, create labels for these KPIs, and paste the formulas into the adjacent cells. In just a minute, you have your core metrics ready to go.

Part 2: Creating Summary Tables for Your Charts

Charts in Excel need summary data to work. Instead of visualizing thousands of individual rows, you first need to aggregate the data. For example, to visualize sales by rep, you need a summary table that lists each rep and their total revenue.

Let's ask ChatGPT how to create this.

Example Prompt:

"Using the same 'Sales_Table', I want to create a bar chart showing total revenue for each sales rep. The reps are in the 'Sales Rep' column. What's the best way to create a summary table for this in Excel?"

ChatGPT might suggest two great options: using a PivotTable or the UNIQUE and SUMIF functions. Let’s focus on the formula-based approach for flexibility.

ChatGPT’s expected guidance:

"You can create a dynamic summary table using formulas. First, get a unique list of your sales reps. In cell A1 of a new sheet (or a blank area), use this formula: =UNIQUE(Sales_Table[Sales Rep]) This will spill a list of all unique reps. Then, in cell B1 next to the first name, use this formula and drag it down: =SUMIF(Sales_Table[Sales Repp], A1, Sales_Table[Revenue])"

Follow these instructions on your Dashboard sheet to create a small table for "Revenue by Sales Rep."

Part 3: Visualizing the Data

With your summary data in place, creating the charts is simple. You don't need ChatGPT for this part, just Excel's built-in tools.

Chart 1: Bar Chart for Revenue by Sales Rep

1. Highlight the summary table you just created (the one with sales reps and their total revenue).

2. Go to the Insert tab in Excel.

3. Choose Recommended Charts and select a Bar Chart or Column Chart.

4. Click OK. Excel will generate the chart for you.

Chart 2: Pie Chart for Revenue by Region Now repeat the process for region. Ask ChatGPT for help with the summary data.

Example Prompt:

"Great, that worked perfectly. Now I need to do the same for 'Region'. Give me the formulas to create a summary of total revenue by region."

ChatGPT will provide similar UNIQUE and SUMIF formulas, just adjusted for the "Region" column. Build that summary table, then use Excel's Insert menu to create a Pie Chart.

Step 4: Assembling Your Dashboard

Now all the pieces are ready. The final step is to arrange them neatly on your "Dashboard" sheet.

• Position your KPI cards at the top for a quick overview.

• Place your two charts side-by-side below the KPIs.

• Give your dashboard a clear title, like "Quarterly Sales Performance."

• Use Excel's formatting options to tweak colors and fonts to match your company's branding if you'd like.

Tips for Better ChatGPT Prompts

The quality of your output depends on the quality of your input. Here’s how to ask better questions:

• Be Specific: Always mention sheet names, table names, and exact column headers. Example: "In 'Sheet1', the 'Amount' column..." is better than "in my sales data."

• State Your Goal: Explain what you are trying to achieve. Example: "I need to calculate month-over-month growth" gives more context than just "how do I subtract two numbers?"

• Keep It Simple and Iterate: Don't ask one massive prompt to build the entire dashboard. Build it piece-by-piece like we did above. Ask for the KPIs first, then for the first summary table, and so on.

• Protect Sensitive Data: Avoid pasting actual confidential business data into a public AI tool. Use dummy data or just describe the structure of your data, like we did in our prompts.

Understanding the Limitations

While an amazing helper, ChatGPT is just that - a helper. It can’t replace your own judgment. Keep these limitations in mind:

• It's a "Code Writer," Not a "Doer": ChatGPT provides text - formulas, code, and instructions. It cannot click buttons, format cells, or directly manipulate your Excel file for you.

• Double-Check the Output: AI models can occasionally make mistakes ("hallucinate"). Always test the formulas and logic ChatGPT provides to ensure they're accurate for your needs.

• Data Security: Again, be cautious about sharing sensitive data. The conversations you have can be used for training, so never upload your company's private financial or customer information.

Final Thoughts

This process shows that creating an Excel dashboard doesn't have to be a mountain to climb. By teaming up with ChatGPT, you can quickly move past the intimidating technical hurdles of formulas and focus on bringing your data's story to life through clear, actionable visualizations.

While using ChatGPT to assist with Excel is a huge step up from navigating on your own, it still requires manual work copy-pasting formulas and setting up charts. With Graphed , we designed a tool to skip these steps entirely. You can connect data sources like Salesforce, HubSpot, or Shopify directly and then just ask for the complete dashboard in plain English, and we build it for you with live, real-time data - no formulas needed.