How to Create a Website Dashboard in Excel with ChatGPT

Building a custom website dashboard from scratch in Excel can feel intimidating, but using ChatGPT completely changes the game. By having an AI assistant to handle the complex formulas and logic for you, you can focus on building visualizations that answer your most important questions. This article will walk you through, step-by-step, how to export your website data, use ChatGPT to process it, and create a dynamic, interactive dashboard in Excel.

Graphed

Still Building Reports Manually?

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

Watch the Demo

First, Why Even Bother Using Excel?

In a world of powerful business intelligence tools, using Excel for a dashboard might seem old-fashioned. However, it has some undeniable advantages, especially for small teams, entrepreneurs, or marketers who need a quick and custom solution without buying new software.

• Accessibility: Nearly every business has access to Microsoft Excel. There are no new platforms to learn or subscriptions to buy.
• Familiarity: Most professionals have at least a basic understanding of spreadsheets, making the barrier to entry much lower than with a dedicated BI tool like Power BI or Tableau.
• Total Customization: You have complete control over the layout, design, and calculations. You're not locked into a template and can build exactly what you need to see.

The main drawback, however, is that the process is manual. The data isn't live, it's a snapshot from the moment you downloaded it. This means you have to repeat the process of downloading and pasting data to refresh your dashboard, which can be time-consuming.

Step 1: Get Your Website Data Out of Google Analytics

Before you can build anything, you need raw materials. For most websites, your primary source of user data will be Google Analytics. You'll want to export reports that give you a good overview of user behavior.

Key Metrics to Export:

To get a holistic view, you should export data that covers traffic acquisition, user engagement, and visitor demographics. A good starting point includes:

• Core Metrics: Sessions, Users, New Users, Pageviews
• Engagement Metrics: Engagement rate, Average engagement time
• Dimension Breakdowns:

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Export Data as a CSV from GA4:

Let's export a simple traffic acquisition report. You can follow this same process for any other standard report in GA4.

1. Log into your Google Analytics account and navigate to your GA4 property.
2. On the left-hand menu, go to Reports > Acquisition > Traffic acquisition.
3. In the top-right corner, select the date range you want to analyze (e.g., "Last 30 days").
4. In the top right again, find the "Share this report" icon (it looks like a box with an arrow pointing up). Click it.
5. Select "Download File" and then choose "Download CSV."

This will download a CSV file with your traffic data. Repeat this for any other reports you need, like an engagement report broken down by page (Reports > Engagement > Pages and screens). For this tutorial, one detailed traffic acquisition export will be enough to get started.

Step 2: Structure Your Data in Excel

Raw CSVs downloaded from analytics platforms are rarely a good foundation for a dashboard. They often contain summary rows or extra information you don't need. Your first job is to clean and structure this data.

1. Open your downloaded CSV file in Excel.
2. Delete any extra informational rows at the top so that the first row contains only your headers (e.g., "Session default channel group," "Sessions," "Users," etc.). Delete summary rows at the bottom, too.
3. Create a new, blank Excel workbook. Save it with a name like "Website Dashboard."
4. Create two tabs. Rename the first one "Dashboard" and the second one "Raw Data."
5. Copy the clean data from your CSV and paste it into the "Raw Data" tab of your new workbook.
6. Select any cell within your data and format it as a table by pressing Ctrl + T (or Cmd + T on Mac). In the dialog box that appears, make sure "My table has headers" is checked and click OK.
7. Excel will automatically name your table (e.g., "Table1"). Give it a more descriptive name. Click anywhere in the table, go to the "Table Design" tab that appears in the top ribbon, and change the "Table Name" in the top-left corner to something memorable, like "GA_Data." This makes writing formulas much easier.

Your workbook is now properly set up. The "Dashboard" tab will be your canvas, and all your underlying data lives in the "Raw Data" tab in a cleanly formatted table.

Step 3: Use ChatGPT to Generate Your Excel Formulas

This is where the process becomes much simpler. Instead of trying to recall complex functions like SUMIFS, VLOOKUP, or INDEX(MATCH), you can just ask ChatGPT to write them for you. The key is to be descriptive in your prompt.

On your "Dashboard" tab, start mapping out the sections you want. Let's create a section for top-level KPIs and another for data breakdowns.

Example 1: Calculating Your Main KPIs

Let's add cards for Total Sessions, Total Users, and Total Engaged Sessions.

Your prompt to ChatGPT:

I have an Excel workbook with a tab called "Raw Data." 
On this tab, there is a table named "GA_Data." This table has columns named "Sessions," "Total Users," and "Engaged sessions."

Give me the standalone Excel formulas to calculate the sum of each of these columns.

ChatGPT's likely response (and the formulas you'll use):

• For Total Sessions: =SUM(GA_Data[Sessions])
• For Total Users: =SUM(GA_Data[Total Users])
• For Total Engaged Sessions: =SUM(GA_Data[Engaged sessions])

Go to your "Dashboard" tab, create three labels for these metrics, and paste these formulas into the adjacent cells. Now you have your main KPI numbers.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Example 2: Creating a Summary Table for a Chart

Now, let's build a chart showing sessions by traffic source (like Paid Search, Organic Search, etc.). Charts require a summary table of data, not raw data. We need to create a small table that lists each unique traffic source and the sum of its sessions. ChatGPT can tell you exactly how to do this.

Your prompt to ChatGPT:

In my "GA_Data" table, I have a column called "Session default channel group" and a column called "Sessions." 

I want to create a two-column summary table on my "Dashboard" sheet. The first column should list every unique channel group, and the second column should show the total sessions for that group. How can I do this with formulas?

ChatGPT's likely advice (explaining the UNIQUE and SUMIFS combo):

1. For the list of unique channels: It will probably suggest the UNIQUE function. In a cell on your dashboard (say, A10), you'd enter: =UNIQUE(GA_Data[Session default channel group]) This will automatically spill a list of all unique channels from your data.
2. For the corresponding sessions sum: In the cell next to the first unique channel (B10), you'll use SUMIFS. The formula would look like this: =SUMIFS(GA_Data[Sessions], GA_Data[Session default channel group], A10) This formula says: "Sum up the 'Sessions' column, but only for the rows where the 'Session default channel group' matches the value in cell A10." You can then drag this formula down, and it will calculate the sum for each channel.

Now you have a perfect little summary table, ready for visualization.

Step 4: Build Your Dashboard Visualizations

With your calculation work done, you can now build the fun part - the charts and graphs. Excel offers a wide range of chart types to help you tell a story with your data.

Building a Bar Chart for Traffic Sources

1. Select the summary table you just created (the unique channels and their session counts).
2. Go to the Insert tab in the Excel ribbon.
3. Under the Charts section, select "Recommended Charts" or go straight to the bar chart icon and choose a "Clustered Bar Chart."
4. Excel will instantly generate a chart. You can customize it by removing the legend, adding data labels, and giving it a clear title like "Sessions by Channel."

Creating a Pie Chart for Device Breakdown

You can repeat the process from Example 2 above to create a summary table for device sessions.

• Your ChatGPT prompt would be: "In my GA_Data table, I have columns for 'Device category' and 'Sessions'. Tell me how to create a summary table for total sessions per device."
• Use the formulas it provides (UNIQUE and SUMIFS) to build your summary table.
• Select that new summary table, go to Insert > Charts, and choose a Pie or Doughnut chart.
• Style the chart to show percentages on each slice for readability. Give it a title like "Device Category Breakdown."

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 5: Add Interactivity with Slicers

A static dashboard is good, but an interactive one is better. Slicers are user-friendly buttons that allow you (or your team) to filter your data without needing to touch any formulas.

To use slicers effectively, it's best to base your charts on PivotTables instead of formula-based summary tables. Let's create one for our traffic sources.

1. Go to your "Raw Data" tab and click anywhere inside your GA_Data table.
2. Go to Insert > PivotTable. Excel should automatically select your entire table. Choose to place the PivotTable on a new worksheet or an existing one (like your "Dashboard" tab, but maybe off to the side).
3. In the PivotTable Fields pane, drag "Session default channel group" to the Rows area and "Sessions" to the Values area. You've now made a PivotTable that summarizes your data.
4. Build a bar chart from this PivotTable just like you did before.
5. Now, click on your new chart. Go to the "PivotChart Analyze" ribbon that appears and click "Insert Slicer."
6. A dialog box will appear showing all columns from your data. Check the box for "Device category" and click OK.

A slicer menu with buttons for "Desktop," "Mobile," and "Tablet" will now appear on your dashboard. Clicking any of these buttons will instantly filter your bar chart to show data for only that device. You can connect this slicer to multiple PivotCharts to filter your entire dashboard at once.

Final Thoughts

By pairing the universal accessibility of Excel with the conversational power of ChatGPT, you can build a highly customized website dashboard without writing a single complex formula from memory. This process transforms a once-technical task into a creative exercise, allowing you to focus on surfacing the insights that matter most for growing your site's performance.

While this manual Excel method is powerful for one-off analyses, keeping it updated requires you to regularly download and paste new data. For teams who need to make fast decisions based on the freshest data, this can become a significant bottleneck. This is where we designed Graphed to help. We automate the entire process by connecting directly to sources like Google Analytics, so your reports are always live. Instead of building tables and charts by hand, you just ask questions in plain English - like "show me my top 10 landing pages by sessions on a bar chart" - and the system builds a real-time, shareable dashboard for you in seconds.