How to Create an E-commerce Dashboard in Excel with ChatGPT

Building a powerful ecommerce dashboard in Excel is a lot easier than you think, especially when you have an AI assistant to help with the heavy lifting. Instead of getting tangled up in complex formulas and PivotTable configurations, you can use ChatGPT to guide you through the entire process. This article provides a step-by-step guide to exporting your ecommerce data, cleaning it up, and using simple prompts to have ChatGPT help you create a meaningful dashboard from scratch.

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

While your ecommerce platform (like Shopify, BigCommerce, or WooCommerce) has its own analytics, a custom Excel dashboard centralizes the key metrics you care about most. It allows you to track performance against your specific goals, spot trends over time, and visualize your data in a way that makes sense for your business, all in a familiar environment.

Think of it as your business's command center. Instead of clicking through five different reports to get a complete picture, a dashboard gives you an at-a-glance view of your store’s health, from daily sales to marketing channel performance.

Step 1: Get Your Ecommerce Data Ready

The saying “garbage in, garbage out” is especially true here. Before you can build anything useful, you need clean, well-structured data. This is often the most time-consuming step - the one where most people get stuck - but it’s an absolute prerequisite for success.

Export Your Sales Data

First, you need to pull your data out of your ecommerce platform. You're typically looking for a transactional or order-level report. Log in to your platform's admin panel (Shopify, WooCommerce, etc.) and find the option to export your orders or sales data as a CSV file.

For your initial build, export the last 6-12 months of data to have enough information to spot meaningful trends. At a minimum, your export should include:

• Order ID
• Order Date
• Customer Name/ID
• Product Name or SKU
• Quantity Sold
• Price per Item
• Total Sale Amount
• Discount Amount (if any)
• Customer City/State/Country
• Traffic Source or Marketing Channel (if available)

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

Clean and Structure the Data

Once you open your CSV file in Excel, you’ll likely need to do some cleaning. This is the manual work that can take hours but is critical for accuracy. ChatGPT can’t analyze messy data effectively.

Here’s a simple checklist for cleaning your data:

• Convert to a Table: Click anywhere in your data, go to the Insert tab, and click Table. This makes your data much easier to manage and reference in formulas. Give your table a descriptive name (e.g., SalesData) in the Table Design tab.
• Fix Date Formats: Ensure your 'Order Date' column is formatted as a date, not text. Select the column, go to the Home tab, and choose 'Short Date' or 'Long Date' from the format dropdown.
• Check for Consistency: Look for typos or variations in product names (e.g., “T-shirt” vs. “tshirt”). Use Find and Replace (Ctrl + H) to standardize them.
• Fill Blank Cells: Make sure there are no empty cells where there should be a value (like a zero in the 'Discount Amount' column).
• Format Numbers: Format your currency columns (Price, Total Sale) as a currency for readability.

Once your data is clean and organized in a proper Excel Table, you're ready to start building.

Step 2: Plan Your Dashboard with ChatGPT

Before writing a single formula, it’s helpful to outline what your dashboard will include. This is where ChatGPT can act as your data analyst brainstorming partner. Start a new chat and explain your goal.

A good dashboard focuses on a handful of Key Performance Indicators (KPIs) that tell you if you're winning or losing. You can ask ChatGPT for suggestions.

Example ChatGPT Prompt:

"I'm building an ecommerce sales dashboard in Excel for my online store. My dataset is an Excel Table named 'SalesData' with the following columns: [List your column headers like OrderID, OrderDate, CustomerID, ProductName, Quantity, Price, TotalSale, Discount, State, TrafficSource]. What are the most important KPIs I should track on my main dashboard? Suggest 5-7 key metrics I can calculate from this data."

ChatGPT will likely suggest essential ecommerce KPIs like:

• Total Revenue: The total sales over a period.
• Total Orders: The number of orders received.
• Average Order Value (AOV): The average amount spent per order.
• Number of Unique Customers: How many individual customers made a purchase.
• Top 5 Selling Products (by Revenue): The products bringing in the most money.
• Sales by Traffic Source: Which channels are driving the most revenue.

This gives you a clear and focused plan for what to build.

Step 3: Build Your Dashboard Elements using ChatGPT Formulas

Now, let's create the components of your dashboard. A best practice in Excel is to keep your data, calculations, and dashboard separate. Create three tabs in your workbook: RawData, Calculations, and Dashboard.

Place your clean SalesData table in the RawData sheet. All your formulas and PivotTables will live in the Calculations sheet, keeping things tidy. The Dashboard sheet will be your final visual presentation.

Generating Formulas for KPIs

Let's start calculating the KPIs you identified. You’ll ask ChatGPT for the specific Excel formula needed for each one. The key to a good prompt is to be specific about your table name and column names.

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

Example ChatGPT Prompt for Total Revenue:

"I have an Excel Table named 'SalesData'. Write an Excel formula to calculate the sum of the 'TotalSale' column."

ChatGPT will respond with:

=SUM(SalesData[TotalSale])

In your Calculations sheet, create a label in one cell (e.g., "Total Revenue") and paste this formula in the cell next to it.

Example ChatGPT Prompt for AOV:

"Using my 'SalesData' table, give me an Excel formula to calculate the Average Order Value (AOV). This should be the total revenue divided by the unique number of orders. The order IDs are in the 'OrderID' column and sales are in the 'TotalSale' column."

ChatGPT might give you a slightly more complex formula:

=SUM(SalesData[TotalSale])/ROWS(UNIQUE(SalesData[OrderID]))

Repeat this process for all your main KPIs, neatly organizing them in your Calculations sheet. You are essentially using ChatGPT as a formula generator, saving you from having to look up syntax and functions.

Creating Charts with PivotTables

For visual charts - like sales over time or top-selling products - PivotTables are your best friend. They can seem intimidating, but ChatGPT can walk you through creating them.

Example ChatGPT Prompt for a Sales Trend Chart:

"I want to create a line chart in Excel that shows monthly sales trends. Using my 'SalesData' table, what are the exact steps to create a PivotTable and a PivotChart that groups the 'OrderDate' by month and sums the 'TotalSale' for each month?"

ChatGPT will break down the process step-by-step:

1. Click anywhere inside your SalesData table.
2. Go to the Insert tab and click PivotTable. Choose to place it in your Calculations worksheet.
3. In the PivotTable Fields pane, drag OrderDate to the Rows area.
4. Drag TotalSale to the Values area.
5. Right-click on any date in the PivotTable, select Group, and choose Months and Years.
6. With the PivotTable selected, go to the PivotTable Analyze tab and click PivotChart. Choose a line chart.

Follow these instructions for each chart you want. For example, to find top products, drag ProductName to Rows and TotalSale to Values, then filter for the Top 5. You can create charts for sales by state, traffic source, or product category using this same method.

Step 4: Design a Clean and Interactive Dashboard

Now it's time to assemble everything onto your Dashboard sheet. This final step is about presentation and usability.

Linking Your KPIs

For each KPI on your Dashboard sheet, you'll simply link to the calculation you already made.

For example, create a text box that says "Total Revenue." Then, in a large, bold-font cell next to it, type = and navigate to your Calculations sheet and click on the cell containing the Total Revenue formula. This links the value, so when your data updates, your dashboard does too.

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

Adding Your Charts

Cut and paste the PivotCharts you created from the Calculations sheet onto your Dashboard sheet. Arrange them in a logical grid. A good layout might have your main KPIs across the top, a big sales trend chart in the middle, and smaller charts for products or traffic sources beneath it.

Make it Interactive with Slicers

Slicers are user-friendly filters that make your dashboard dynamic. To add one, click on any of your PivotCharts, go to the PivotTable Analyze tab, and click Insert Slicer. You could add a slicer for Year or TrafficSource.

Once you've added a slicer, you need to connect it to all your charts. Right-click the slicer, choose Report Connections, and check the boxes for all the PivotTables you want it to control. Now, when a user clicks a year or a channel in the slicer, all the connected charts and tables on your dashboard will update instantly.

Best Practices and Common Pitfalls

Combining ChatGPT and Excel is powerful, but it’s not magic. Keep these tips in mind to avoid frustration.

• Always Verify Formulas: ChatGPT is an excellent starting point, but it can occasionally make mistakes or generate inefficient formulas. Test its output to make sure the numbers look right.
• Be Hyper-Specific: The more context you provide in your prompt - table names, column headers, desired outcome - the better the result will be. Vague questions lead to vague answers.
• Don't Overcomplicate It: A great dashboard isn't one with 50 charts. It's one that clearly communicates the 5-10 most important things. Focus on metrics that lead to action.
• Remember, It's a Static Snapshot: This dashboard is only as current as your last data export. To update it, you have to download a new CSV and replace the data in your RawData sheet. This manual weekly routine is one of the biggest drawbacks of the pure Excel method.

Final Thoughts

You’ve seen how you can combine the flexibility of Excel with the conversational power of ChatGPT to build a custom ecommerce dashboard. This process empowers anyone, regardless of their technical skill, to turn raw sales data into actionable business information without spending weeks learning complex software.

While this manual Excel process is a fantastic start, updating it weekly can become a chore involving dreaded CSV downloads and data cleaning. That’s why we built Graphed to take over. We automate the entire process by connecting directly to your sources like Shopify and Google Analytics. Instead of exporting static files, your dashboards are always live and update in real-time. You can just ask questions in plain English - like "create a dashboard comparing sales by traffic source for the last 90 days" - and have a live, shareable dashboard built for you in seconds, freeing you to focus on strategy instead of report-building.