How to Create a Customer Experience Dashboard in Excel with AI
Building a customer experience (CX) dashboard is one of the best ways to get a complete picture of customer happiness, but the process can feel overwhelming. This tutorial shows you how to bring all your scattered feedback into a single, powerful CX dashboard in Excel, and how to use a little bit of AI to speed up the most tedious parts of the process.
First, Ask Better Questions to Get a Better Dashboard
Your CX dashboard should give you clear answers, not just a bunch of charts. Before you open a spreadsheet, you need a statement about what this report is supposed to do. Think of it like a recipe. If you don’t have one, it’s much more likely whatever you end up cooking won’t get eaten. You shouldn’t build a report if no one at your company can tell you how they’ll use it to make a decision.
Here’s an example goal to get you started:
“Our new customer experience dashboard will help our head of product identify our unhappiest users and use their support trends to flag product issues before they become widespread. It’ll do this by tracking negative CSAT and NPS comments and highlighting recurring keywords found in our support tickets. We would consider this a success if, as a result of the dashboard, our support team can share feedback patterns with our product managers every week so they can respond 10 days sooner to trending issues that are likely to drive churn if we don't fix them.”
Notice how that prompt includes the user of the dashboard (Product Manager), what the report's purpose is (monitor complaints before they escalate), and how success is supposed to look from the user's perspective (sharing feedback with Product 10 days faster).
Don't sweat all the details right away. It takes practice: a rough idea of what you plan to measure is better than none. And once you start tracking consistently, you might change your dashboard and KPIs entirely from the ones you started with.
Step 1: Gather and Organize Your CX Data
A dashboard is useless without clean, organized data. Your customer feedback likely lives in a half-dozen different apps - survey tools, support desks, review sites, and your CRM. The first step is to bring it all into one place.
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.
Where to Find Your CX Data
Start by exporting data from these common sources. Most tools let you download a CSV or Excel file:
- Surveys: Net Promoter Score (NPS), Customer Satisfaction (CSAT), and Customer Effort Score (CES) responses. Look in tools like SurveyMonkey, Delighted, or forms you run on your site.
- Support Tickets: Data from Zendesk, HubSpot Service Hub, Intercom, or whichever platform you use. Export ticket details like creation date, resolution time, and the customer's comments.
- Online Reviews: Feedback from sites like Google Reviews, Trustpilot, G2, or Capterra. You may need to copy and paste this data manually if an export isn’t available.
- CRM Data: Customer information from Salesforce, HubSpot, or your CRM. You can add details like customer lifetime value (LTV) or their subscription plan to see how CX metrics vary across different segments.
Structure Your Data in an Excel Sheet
Once you have your CSV files, create a new Excel workbook. Label your first tab "Raw Data". This is where you'll consolidate everything. Your goal is to create one master table with consistent columns. Aim for a structure like this:
- Date: The date the feedback was received.
- CustomerID: A unique ID for the customer.
- Source: Where the feedback came from (e.g., "NPS Survey," "Support Ticket," "Google Review").
- MetricType: The specific metric (e.g., "NPS," "CSAT").
- Score: The numerical rating (e.g., 9 for NPS, 4 for CSAT).
- FeedbackText: The raw, qualitative comment from the customer.
- SentimentScore: (we’ll fill this in with an AI add-in later) A sentiment score can be helpful so you can sort comments easily.
- Sentiment: (we'll create this with formulas in the next step)
- Category: (we'll create this from keywords)
Keeping this structure neat is the most important step. Make sure your dates are formatted the same way and your source names are consistent. A little cleanup now saves you hours of headaches later.
Step 2: Automate Feedback Sentiment With Third-Party AI Add-ins
Here are two of the most popular to start:
- In the Excel Toolbar, click on File, then Get Add-ins. The Office Add-ins store will appear.
- Search for 'ChatGPT' Excel add-ins and you'll find a wide selection of 'AI Assistant' tools.
We did some light testing among a handful - "AI Assistant Spreadsheet Editor" will do the trick if you need something without too many bells and whistles — just connect to an OpenAI API key. Or check out "GPT for Excel" which has a larger feature set that gives more flexibility like the ability to apply GPT functions (GPT_FILL, GPT_EXTRACT, GPT_CLASSIFY) directly into the cell. Follow its instructions to install and you can link it to your OpenAI account or use their self-hosted models for more privacy. If you’re unsure about paying for API credits, you can always switch over to a freemium app like Vertex AI. The easiest way to bring this into Excel is the traditional way: export your comments from your customer feedback software into a .csv, open Google Labs, and so on.
The trade-off for these convenient AI tools for Sheets and Excel is privacy concerns since they can have access to your private company data.
Sentiment Analysis with a Workaround: VLOOKUPS!
Here’s another way we’ve tried here on our team. You can use an Excel native feature - VLOOKUP. First, you'll use ChatGPT to quickly do some 'buzzword analysis', which in this case will generate a simple glossary that you'll use in your sheets. Here are the steps:
- Generate sentiment keyword tables in seconds:
- Paste it into a new sheet, save it as a .csv, and then, on your "Raw Content" sheet, write:
This is quite clever since you’ll see new suggestions immediately based on your prompts. There are many workarounds to try, and it could be a topic for its own tutorial.
Step 3: Analyze Your Raw Data to Create Insights
Create a second sheet called "Calculation". This is your engine room where you'll use formulas and Pivot Tables to process the data from your "Raw Data" sheet.
Calculating Net Promoter Score (NPS)
NPS is a popular metric for measuring customer loyalty. To calculate it, you’ll first need to categorize your respondents:
- Promoters: Customers who gave a score of 9 or 10.
- Passives: Customers who gave a score of 7 or 8.
- Detractors: Customers who gave a score of 0 to 6.
A Word to the Wise...
The following steps and charts can feel intimidating - but only you know what skills you're bringing (or eager to learn) here. Take the easiest approach for each chart until you want to challenge yourself with nested logic. The end product is really what this is all about. Don't feel badly if you copy and paste a few charts to get you there at first.
With COUNTIFS: Best for when you want simple, easy-to-remember counts...
Excel tables and COUNTIF are a very user-friendly pair for good reason. They update on their own and require just your existing know-how. (Don't let any "Excel Gurus" fool you. This will hold you over, just like Pivot Tables for 90% of what a marketing team actually ends up needing.)
In your Calculation sheet, create cells for each category and use COUNTIFS() formulas:
Create a header named something like "NPS_Category" and try out this formula:
=IF([@Score]>=9,"Promoter", IF([@Score] >=7, "Passive", "Detractor"))
Or use the full version for Promoters:
=COUNTIFS('Raw Data'!Source_Column, "NPS_SURVEY", 'RawData'!ScoreColumn, ">=9")
Now, calculate the NPS score with:
(Promoters - Detractors) / TotalRespondents
Multiply the result by 100 to get your NPS score. Format the cell to show no decimal places.
Calculating Customer Satisfaction (CSAT) Score
In a different column in "Raw Data", add this:
=COUNTIFS([your_csat_field], ">=5")
Then drag the formula to apply it to all data. Now, do the work to get the percentages or try SUMIF in another "Helper" column.
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.
Summarize Your Data By Month
When you're trying to figure out if you can hit your quarterly goals, break it down into smaller chunks. For example, track performance by month with a line chart. If your growth feels like a flat line, you can add trending arrows to visualize changes.
You can start tracking performance changes with trend lines or sparklines on PC.
Keep It Simple: Create a "Summary Sheet"
This is a common pattern for when your Excel dashboards seem to grow out of control. Many people use a single sheet, even experienced Excel wizards, to house complicated calculations in one section rather than all over the page.
Step 4: Putting It All Together on an Excel Dashboard
Create a dedicated tab - let's call it "CX_Dashboard". Add graphs for overall satisfaction or loyalty metrics like NPS. This will help monitor progress weekly.
The layout works well, separating your "Raw Data" from your "Calculation" sheet. Let's create a "Customer Summary" and just link the cells between sheets using "Paste-Special: Linked Values" so that changes to our data get passed on.
Final Thoughts
An Excel CX dashboard helps turn messy feedback into a clear story. By gathering your data, performing some key calculations, and visualizing the results, you can spot trends and fix issues before they become widespread problems. Maintaining these dashboards over time, along with good habits from your colleagues, will build your dashboard into one of your most valued tools instead of an extra time sink. It can take time, but they are a great opportunity to learn.
When weekly reporting still feels like a drag, a different approach may be needed. Since our AI is familiar with popular tools, you won’t even need API credits on our end. You can tell us in just the same chat-prompt method you know, and we'll have your graphs ready in seconds. We believe AI data analysis should fit into your day-to-day workflow so seamlessly that you'll find even more time to stay in your "flow" state. Try us at Graphed. We would love to see if we make a right match.
Related Articles
How to Create Facebook Ads API for Claude Code
Create a Meta Marketing API access token that never expires, using a system user, so Claude Code can pull Facebook ad reports and manage campaigns.
ClickHouse vs Postgres: When to Switch to Columnar
Comparing ClickHouse vs PostgreSQL means matching storage architecture to workload. Row storage wins for transactions. Columnar wins for scan-heavy analytics.
Google Ads for Orthodontists: Get More Booked Consults
Most orthodontic Google Ads campaigns waste money on low-intent searches. The problem is structure, not the platform.