How to Create a Customer Experience Dashboard in Excel with ChatGPT

Building a customer experience dashboard doesn't have to involve complex business intelligence tools or a data science degree. If you have customer data and access to Excel and ChatGPT, you have everything you need to transform raw feedback into actionable insights. This guide will walk you through a step-by-step process for creating a powerful CX dashboard, using ChatGPT as your personal data assistant to handle the technical heavy lifting.

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

What Exactly is a Customer Experience (CX) Dashboard?

A customer experience (CX) dashboard is a visual report that brings together key metrics about how customers feel about your company, product, or service. Unlike a standard sales or marketing report that tracks revenue or leads, a CX dashboard focuses on sentiment, satisfaction, and loyalty. It translates feelings and feedback into numbers and charts you can use to make smarter business decisions.

For most businesses, a great CX dashboard tracks a few core metrics:

• Net Promoter Score (NPS): Measures customer loyalty by asking how likely they are to recommend your business on a scale of 0-10.
• Customer Satisfaction (CSAT): Typically measures satisfaction with a specific interaction (like a support call or a purchase) on a scale of 1-5.
• Customer Effort Score (CES): Asks how easy it was for a customer to get their issue resolved, which is a strong indicator of future loyalty.

While dedicated BI tools are powerful, building your first dashboard in Excel is a fantastic starting point. It’s familiar, accessible, and with ChatGPT helping out, it’s more capable than ever.

Step 1: Gather Your CX Data

Before you can build anything, you need raw material. Your customer experience data might be scattered across a few different places. The goal is to gather it and get it into a simple spreadsheet format, like a CSV or XLSX file.

Look for data from sources like:

• Survey Tools: If you use tools like SurveyMonkey, Typeform, or even simple Google Forms to collect NPS or CSAT feedback, you can almost always export the results as a CSV file.
• CRM or Help Desk Platforms: Systems like HubSpot, Zendesk, or Intercom often store CSAT scores and feedback related to support tickets. Look for an export option in their reporting section.
• E-commerce Platforms: Shopify or WooCommerce sometimes have apps or built-in features for collecting post-purchase feedback.
• Manual Spreadsheets: Maybe you’ve just been collecting feedback in a simple Google Sheet. That works perfectly! Just download it.

For this tutorial, let’s imagine we’ve exported data from a simple feedback survey. It might look something like this in its raw form:

date of feedback,nps_rating,comments,csat score (out of 5) 10/1/2023,10,"Great service!",5 october 2 2023,4,The app was confusing and slow. I couldn't find what I needed.,2 03-Oct-23,"I like the product, but shipping was delayed",3 "Oct 04, '23",9,all good," "

As you can see, the data is messy. Dates are in different formats, there are extra quotation marks, some scores are missing, and the comments column is unstructured. This is where ChatGPT becomes incredibly useful.

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 2: Use ChatGPT to Clean and Structure Your Data

Data cleaning is usually the most time-consuming part of reporting, but ChatGPT can turn hours of manual work into a single copy-paste prompt. Your goal is to give ChatGPT a sample of your messy data and tell it precisely how you want it organized.

First, open a blank Excel sheet and paste your raw data into a tab you can name “Raw Data." Then, copy the first 5-10 rows of your data, including the headers. Go to ChatGPT and use a prompt like this:

"I'm creating a customer experience dashboard in Excel. I have some raw customer feedback data that needs to be cleaned and structured. My goal is to make it uniform so I can analyze it easily. Here is a sample of the data: date of feedback,nps_rating,comments,csat score (out of 5) 10/1/2023,10,"Great service!",5 october 2 2023,4,The app was confusing and slow. I couldn't find what I needed.,2 03-Oct-23,"I like the product, but shipping was delayed",3 "Oct 04, '23",9,all good," " Could you clean this for me? I need you to do the following: Please provide the output in a table format I can copy back into Excel."

ChatGPT will process your request and give you back a perfectly clean table that’s ready for analysis. You can then copy this clean data into a new tab in your Excel file called "Cleaned Data."

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: Ask ChatGPT for Excel Formulas and Calculations

Now that your data is clean, you can start summarizing it. The core of any dashboard is a summary table that performs all the key calculations. But you don’t need to remember complex Excel formulas to do this. Just describe what you need to ChatGPT.

In your Excel sheet, create a new tab called “Dashboard.” This is where your charts will eventually live. On this sheet, set up a small area for your summary calculations.

Let's start with the overall Net Promoter Score (NPS). Calculating NPS involves finding the percentage of Promoters and subtracting the percentage of Detractors. It can be a tricky formula to write on your own.

Use this prompt in ChatGPT:

"I have my cleaned data in an Excel sheet named 'Cleaned Data'. There's a column called **'NPS Category'** (Column D) that contains the words "Promoter," "Passive," and "Detractor" from rows 2 to 200. Can you give me the Excel formula to calculate my overall Net Promoter Score?"

ChatGPT will likely respond with a formula you can paste directly into a cell on your "Dashboard" tab:

``= ((COUNTIF('Cleaned Data'!D2:D200, "Promoter") - COUNTIF('Cleaned Data'!D2:D200, "Detractor")) / COUNTA('Cleaned Data'!D2:D200)) * 100``

You can do the same for other key metrics:

• For Average CSAT Score: “Give me an Excel formula to calculate the average of all numbers in the 'CSAT Score' column (Column E) of my 'Cleaned Data' sheet.”
• For Total Responses: "Give me a simple formula to count the total number of survey responses in the 'Cleaned Data' sheet."

In just a few minutes, you’ll have a summary block with your most important top-line metrics calculated and ready to go.

Step 4: Build Your Dashboard Visuals

With your formulas in place, it’s time for the fun part: creating the charts and graphs that make your dashboard easy to understand at a glance. We’re going to create three essential visuals for our CX dashboard.

Visual 1: The NPS Breakdown Doughnut Chart

A doughnut chart is perfect for showing the proportion of Promoters, Passives, and Detractors. First, you’ll need a small summary table to power the chart.

On your “Dashboard” tab, create a small table like this:

• Cell A5: Promoter
• Cell A6: Passive
• Cell A7: Detractor

In the adjacent cells (B5, B6, B7), use simple =COUNTIF formulas to count each category from your "Cleaned Data" tab. If you’re unsure about the formula syntax, just ask ChatGPT!

Promoter =COUNTIF('Cleaned Data'!D:D, "Promoter") Passive =COUNTIF('Cleaned Data'!D:D, "Passive") Detractor =COUNTIF('Cleaned Data'!D:D, "Detractor")

Now, highlight this small table (A5:B7), go to the Insert tab in Excel, and choose Pie > Doughnut Chart. Customize the colors (green for promoters, yellow for passives, red for detractors) and give it a clear title like "NPS Breakdown."

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

Visual 2: CSAT Score Over Time Line Chart

Seeing how customer satisfaction changes over time is incredibly valuable. To create this chart, we'll need a pivot table.

1. Go to your “Cleaned Data” sheet.
2. Click anywhere inside your data range.
3. Go to Insert > PivotTable. Choose to place it in a new worksheet.
4. In the PivotTable Fields pane, drag the Date field into the "Rows" area.
5. Drag the CSAT Score field into the "Values" area. Make sure it’s set to "Average of CSAT Score," not "Sum."
6. Excel should automatically group the dates by month. If it doesn't, right-click a date and select Group > By Months.

Once your pivot table is ready, click on it, go to Insert > Recommended Charts, and choose the Line Chart. Move this chart to your "Dashboard" tab and position it neatly.

Visual 3: Key Performance Indicator (KPI) Cards

Your main summary numbers (like the overall NPS and average CSAT you calculated earlier) deserve to stand out. Instead of leaving them in small cells, create simple "KPI cards."

1. On your “Dashboard” tab, go to Insert > Text > Text Box.
2. Draw a small box on your dashboard.
3. With the text box selected, click into the formula bar at the top of Excel.
4. Type the "equals" sign (=) and then click on the cell containing your calculated NPS score. Press Enter.
5. The text box is now dynamically linked to your calculation! Style the box with a larger font size, a bold number, and add a label underneath it like "Overall NPS." Repeat this process for your average CSAT score.

Arrange your charts and KPI cards on your "Dashboard" tab to create a clean, organized, and easy-to-read layout. You can now update your "Raw Data" tab with new information each month, and your entire dashboard will refresh automatically.

Final Thoughts

You’ve seen how pairing the powerful calculation engine of Excel with the language-based intelligence of ChatGPT can help you build a professional customer experience dashboard. This approach lets you bypass the need to be an Excel expert and focus on the insights in your data, turning confusing spreadsheets into clear, impactful stories.

While using Excel and ChatGPT is a huge leap forward from manual reporting, the need to regularly export and clean data can still take time. This is a core problem we wanted to solve with Graphed. We connect directly to your various tools - like SurveyMonkey, HubSpot, or Zendesk - and pipe the data in automatically. Instead of telling ChatGPT to write formulas, you simply describe the dashboard you want in plain English, and our tool builds interactive, real-time visuals for you in seconds, saving you from any spreadsheet-wrangling at all.