How to Create a Company Dashboard in Excel with ChatGPT

Creating a useful company dashboard in Excel often feels like a task reserved for spreadsheet wizards who dream in VLOOKUPs. But what if you could build a powerful, visual report just by describing what you need in plain English? With ChatGPT, you can. This article will walk you through, step-by-step, how to use ChatGPT as your personal data assistant to design and build an effective company dashboard in Excel, no advanced formulas required.

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, What Exactly is a Company Dashboard?

Think of a company dashboard as the instrument panel for your business. It’s a single-screen, visual display of your most important metrics and KPIs (Key Performance Indicators) grouped together. The goal isn’t to show all your data, but to highlight the critical information you need to make quick, informed decisions.

While there are many dedicated business intelligence tools, millions of businesses still run on Excel. It’s familiar, flexible, and powerful enough for most needs. The main challenge has always been the technical barrier - knowing the right formulas, choosing the best chart types, and formatting everything to be clear and readable. That’s where ChatGPT changes the game.

How ChatGPT Can Help You Build Excel Dashboards

ChatGPT can’t directly access your Excel files, but it serves as an incredibly intelligent co-pilot. It can guide you through every stage of the dashboard-building process by:

• Recommending KPIs: Unsure what to track? Describe your business, and ChatGPT can suggest relevant metrics.
• Writing Complex Formulas: Ditch the trial-and-error. Instead of Googling for hours, just ask for the exact SUMIFS, COUNTIFS, or INDEX(MATCH) formula you need.
• Structuring Your Data: Proper data setup is 90% of the battle. ChatGPT can show you how to organize your raw data for easy analysis.
• Suggesting Visualizations: Ditch the default pie chart. Ask for the best chart type to communicate your specific insight, whether it’s a line chart for trends, a bar chart for comparisons, or a scatter plot for correlations.
• Generating VBA Code: For more advanced needs, it can write simple VBA macros to automate repetitive tasks, like refreshing your data or formatting your dashboard.

Step-by-Step Guide: Building an E-commerce Dashboard with ChatGPT & Excel

Let's build a simple sales dashboard for a fictional e-commerce store. We'll follow the process from idea to finished product, using ChatGPT at each step.

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 1: Define Your Goals and Find Your KPIs

Before you touch your data, you need to know what questions you want to answer. What information helps you understand if your business is healthy? If you're not sure where to start, ask ChatGPT.

First, create a dedicated worksheet in a new Excel file named "Data". This is where your raw, unedited data will live. It’s a critical best practice to keep your raw data separate from your dashboard presentation.

Example Prompt for ChatGPT:

“I run a small e-commerce business on Shopify. I want to build a simple sales dashboard in Excel. What are the 5-7 most important KPIs I should track to understand daily and monthly performance?”

Possible ChatGPT Response:

That's a great goal! For a Shopify store, here are some essential KPIs you should track:

1. Total Revenue: The overall top-line sales number.
2. Average Order Value (AOV): Total Revenue / Total Number of Orders. Shows if customers are buying more per transaction.
3. Total Orders: The raw count of transactions.
4. Conversion Rate: (Total Orders / Total Website Visitors) * 100. Measures how effectively your site turns visitors into customers.
5. Top 5 Selling Products (by Revenue): Tells you which products are driving the most value.
6. Revenue by Traffic Source: Shows which marketing channels (e.g., Google Ads, Facebook, Organic Search) are bringing in paying customers.

Step 2: Collect and Structure Your Raw Data

Your dashboard is useless without clean, well-organized data. The golden rule is to use a "tidy" format: each row is a single record (like one sale or one item within a sale), and each column is a specific attribute of that record (like date, product name, or price).

Again, you can ask ChatGPT for advice on how to structure this data in Excel.

Example Prompt for ChatGPT:

“Okay, I'm going to export my order data from Shopify. To track the KPIs you mentioned, what columns should I have in my Excel table? Please give me a simple table structure.”

Possible ChatGPT Response:

Absolutely. For your raw data sheet in Excel, structure your data in a table with the following columns:

• OrderID: The unique ID for each transaction.
• OrderDate: The date the order was placed.
• ProductName: The name of the product sold.
• Quantity: The number of units sold for each item.
• UnitPrice: The price per unit of the item.
• TrafficSource: The marketing channel that brought the customer (e.g., 'Facebook Ads', 'Google Ads', 'Organic').

Populate your "Data" worksheet with this information. To make formulas and charts easier, convert your data range into an official Excel Table. Do this by selecting a cell in your data and pressing Ctrl + T (or Cmd + T on a Mac). In the Table Design tab that appears, name your table something memorable, like SalesData.

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 3: Create a Calculation Sheet for Your KPIs

Next, create a new worksheet named "Dashboard". This is where your charts and summaries will go. The visuals on your dashboard shouldn't be linked directly to your thousands of rows of raw data. Instead, they’ll pull from a small set of summary calculations.

This is where ChatGPT's formula-writing power shines. Let's create cells for our main KPIs.

Example Prompt for ChatGPT:

“I have an Excel table named SalesData with columns Quantity and UnitPrice. How do I calculate Total Revenue?”

Possible ChatGPT Response:

To calculate Total Revenue, you'll need a "helper" column in your SalesData table called LineTotal. The formula in that column would be: =[@Quantity]*[@UnitPrice] Then, to get your overall Total Revenue on your Dashboard sheet, you would use this formula: =SUM(SalesData[LineTotal])

Follow a similar process for other KPIs. For example, to calculate sales from a specific channel:

Example Prompt for ChatGPT:

“Using my SalesData table, give me the Excel formula to calculate the total revenue that came just from 'Google Ads'. The revenue is in the LineTotal column and the source is in the TrafficSource column.”

Possible ChatGPT Response:

You can use the SUMIFS function for this. Here's the formula: =SUMIFS(SalesData[LineTotal], SalesData[TrafficSource], "Google Ads")

Use this method to build a small summary table that will feed your charts. For instance, create a table calculating total revenue for each traffic source or revenue by product.

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: Visualize Your Data with Charts

With your formulas in place, it’s time to create the visuals. Selecting the right chart is half the battle, and ChatGPT can act as your personal design consultant.

Example Prompt for ChatGPT:

“I have a summary table in Excel showing total sales per day for the last 30 days. What is the best chart type to show this trend over time?”

Possible ChatGPT Response:

For showing a trend over time, a line chart is the perfect choice. It clearly visualizes the ups and downs of your daily sales and makes it easy to spot patterns.

Now, let's make a chart for our traffic sources.

Example Prompt for ChatGPT:

“I have a table showing revenue broken down by TrafficSource (Facebook, Google, Organic). What's a good way to show the contribution of each channel to the total revenue?”

Possible ChatGPT Response:

A bar chart is excellent for comparing categories side-by-side. If you want to show parts of a whole, a donut chart or pie chart can work, but a bar chart is often easier to read accurately when you have more than two or three categories.

To create a chart in Excel, simply select the data in your summary table, go to the Insert tab, and choose the recommended chart type. Then, start cleaning it up for your dashboard: give it a clear title, remove unnecessary gridlines, and use color intentionally.

Step 5: Assemble and Design Your Dashboard

This is where you bring everything together into a logical, easy-to-read layout.

1. On your "Dashboard" sheet, create a grid. A common practice is to change the background color of the entire sheet to a neutral gray and remove gridlines (View > uncheck Gridlines).
2. Create "KPI cards" at the top for your most important numbers (e.g., Total Revenue, AOV). These are simply cells formatted with a large font size, and they link directly to your calculation cells.
3. Copy and paste your charts from the previous step onto the dashboard sheet. Arrange them in a way that tells a story, perhaps with high-level summaries at the top and more detailed breakdowns below.
4. Use shapes and text boxes to create titles and separate sections to guide the viewer’s eye.

The key is simplicity. A crowded dashboard is an ineffective one. Stick to the essential charts that answer your most pressing business questions.

Final Thoughts

Building a company dashboard in Excel doesn't have to be a technical headache anymore. By leveraging ChatGPT as your interactive guide, you can confidently turn raw data into a clear visual report that helps you make smarter decisions. You can now focus on the "what" and the "why" of your data, and let your AI assistant handle the "how."

While using Excel and ChatGPT is a fantastic way to build custom reports, it still requires manual steps like exporting data and setting up files. At Graphed , we’ve streamlined this process to be even faster. We allow you to connect your data sources directly (like Shopify, Google Analytics, and Hubspot) and create live dashboards simply by describing what you want to see. Your dashboards update automatically in real time, removing the need to ever download a CSV file again. It's the full experience of a data analyst, available in seconds.