analyzing customer support tickets in Excel: real tutorial
most solopreneurs and small business owners stare at a Helpscout, Zendesk, or Intercom export and feel paralyzed. there are 400 rows, 12 columns, the dates are inconsistent, half the tickets are tagged and half are not, and somewhere in there is the answer to “what is breaking the product.” they open it in Excel, scroll, sigh, and close it.
the gap is not skill. it is workflow. analyzing support tickets is one of the highest-leverage analyses any business can run, because every ticket is a customer telling you exactly where the product is failing them. but you only get the value if you turn the export into structured signal. that means a few standard columns, a category column you fill in once, two pivot tables, and a couple of conditional rules.
this tutorial walks you through the entire process using a realistic 18-row sample dataset. by the end, you will know which problems are most frequent, which take the longest to resolve, which customer segments suffer the most, and what to ship next. no SQL, no Python, just Excel.
the sample dataset
below is the dataset we will use throughout this tutorial. copy it into Excel, save as support-tickets.xlsx, and follow along.
| ticket_id | created_date | resolved_date | customer_plan | category | priority | first_response_min | resolution_hours | csat |
|---|---|---|---|---|---|---|---|---|
| 1001 | 2026-04-01 | 2026-04-01 | Pro | login_bug | high | 12 | 3 | 5 |
| 1002 | 2026-04-01 | 2026-04-03 | Free | feature_request | low | 240 | 48 | 3 |
| 1003 | 2026-04-02 | 2026-04-02 | Pro | login_bug | high | 8 | 2 | 5 |
| 1004 | 2026-04-02 | 2026-04-04 | Starter | billing | high | 30 | 38 | 2 |
| 1005 | 2026-04-03 | 2026-04-05 | Free | how_to | low | 360 | 50 | 4 |
| 1006 | 2026-04-04 | 2026-04-04 | Pro | login_bug | high | 5 | 1 | 5 |
| 1007 | 2026-04-05 | 2026-04-08 | Starter | billing | medium | 90 | 65 | 2 |
| 1008 | 2026-04-06 | 2026-04-06 | Pro | export_bug | medium | 25 | 6 | 4 |
| 1009 | 2026-04-07 | 2026-04-09 | Free | how_to | low | 480 | 52 | 3 |
| 1010 | 2026-04-08 | 2026-04-08 | Pro | login_bug | high | 7 | 2 | 5 |
| 1011 | 2026-04-09 | 2026-04-13 | Starter | billing | high | 45 | 92 | 1 |
| 1012 | 2026-04-10 | 2026-04-10 | Pro | export_bug | medium | 20 | 5 | 4 |
| 1013 | 2026-04-11 | 2026-04-12 | Free | feature_request | low | 300 | 28 | 3 |
| 1014 | 2026-04-12 | 2026-04-12 | Pro | login_bug | high | 6 | 1 | 5 |
| 1015 | 2026-04-13 | 2026-04-15 | Starter | billing | high | 60 | 50 | 2 |
| 1016 | 2026-04-14 | 2026-04-14 | Pro | export_bug | medium | 22 | 4 | 4 |
| 1017 | 2026-04-15 | 2026-04-17 | Free | how_to | low | 420 | 49 | 4 |
| 1018 | 2026-04-16 | 2026-04-16 | Pro | login_bug | high | 9 | 2 | 5 |
support ticket analysis turns a flat export into structured business intelligence by categorizing each ticket, deriving response and resolution time, and pivoting by category, plan, and priority. the standard solopreneur workflow in Excel is: clean dates, fill a category column, build a pivot of volume by category, build a second pivot of resolution time, then surface the top friction point. this typically takes 30 minutes for a month of tickets and consistently outranks gut feel as a roadmap input.
step 1: clean and structure the data
open the file. the first thing to verify is that every column has a single, predictable type. dates should be real dates, not text. numbers should be numbers. category should be a closed list.
check date columns
select column B (created_date) → Home → Number Format → Short Date. if the dates align right, they are real dates. if they align left, they are text. for text dates, run:
=DATEVALUE(B2) in a helper column.
expected output: a numeric value like 46118, formatted as a date.
add derived columns
add three helper columns:
wait_minutes==IFERROR(F2, "")(already there asfirst_response_min)resolution_hours==IFERROR(G2, "")(already there)is_resolved==IF(C2="", "open", "closed")
expected output: every row marked closed for our sample data.
lock the category list
select column E → Data → Data Validation → List → enter:
login_bug,export_bug,billing,how_to,feature_request,other
this prevents future tickets from being typed as “Login Bug” or “login_BUG”, which would split your pivots into noise.
common mistake: most solopreneurs skip the data validation step. three months later, the same category appears as four different strings in the pivot table and the chart is unusable. fix this on day one.
step 2: build the volume-by-category pivot
select the entire data range (A1:I19) → Insert → PivotTable → New Worksheet.
configure the pivot
drag fields:
- Rows:
category - Values:
ticket_id(count)
expected output:
| category | count of ticket_id |
|---|---|
| billing | 4 |
| export_bug | 3 |
| feature_request | 2 |
| how_to | 3 |
| login_bug | 6 |
| Grand Total | 18 |
login_bug is the largest category at 33% of total volume. that is your headline finding before you do anything else.
add a percentage column
right-click any value cell → Show Values As → % of Grand Total. you now see relative share, which is what matters when you brief a developer on what to fix next.
step 3: build the resolution-time pivot
create a second pivot from the same data.
- Rows:
category - Values:
resolution_hours(Average)
expected output:
| category | avg resolution_hours |
|---|---|
| billing | 61.3 |
| export_bug | 5.0 |
| feature_request | 38.0 |
| how_to | 50.3 |
| login_bug | 1.8 |
reading this against the volume pivot reveals the structural insight. login_bug is the most frequent ticket but resolves in under 2 hours, which means it is well-templated and likely has a known cause. billing is half the volume but takes 30x longer per ticket, which makes it the single biggest drain on your support hours.
if you cut billing tickets in half, you save more support time than eliminating login bugs entirely.
step 4: cross-tab category × plan
build a third pivot.
- Rows:
category - Columns:
customer_plan - Values:
ticket_id(count)
expected output:
| category | Free | Pro | Starter | Total |
|---|---|---|---|---|
| billing | 0 | 0 | 4 | 4 |
| export_bug | 0 | 3 | 0 | 3 |
| feature_request | 2 | 0 | 0 | 2 |
| how_to | 3 | 0 | 0 | 3 |
| login_bug | 0 | 6 | 0 | 6 |
| Grand Total | 5 | 9 | 4 | 18 |
now the pattern is obvious. Free users complain about how-to and features. Pro users hit login bugs and export bugs. Starter users have a billing problem. these are three different products, not one.
the fix for each segment is structurally different. shipping a “support improvement” without splitting by plan is exactly how solopreneurs end up improving the wrong thing.
step 5: derive csat by category
build the fourth pivot.
- Rows:
category - Values:
csat(Average)
expected output:
| category | avg csat |
|---|---|
| billing | 1.75 |
| export_bug | 4.00 |
| feature_request | 3.00 |
| how_to | 3.67 |
| login_bug | 5.00 |
billing tickets resolve slowly AND get the worst satisfaction scores. login bugs resolve fast AND get perfect scores. this is the empirical version of “what frustrates customers most” and it is impossible to argue with.
step 6: build the priority matrix
create a final summary table that combines the four signals: volume, resolution time, plan exposure, and csat. you can do this with a manual table or a =GETPIVOTDATA() formula referencing each pivot.
manual version:
| category | volume | avg hours | worst-hit plan | avg csat | priority score |
|---|---|---|---|---|---|
| billing | 4 | 61.3 | Starter | 1.75 | 1 |
| login_bug | 6 | 1.8 | Pro | 5.00 | 4 |
| how_to | 3 | 50.3 | Free | 3.67 | 3 |
| export_bug | 3 | 5.0 | Pro | 4.00 | 5 |
| feature_request | 2 | 38.0 | Free | 3.00 | 2 |
priority score combines volume, resolution time, and inverse csat into a single number you can sort by. the simplest version is:
priority_score = (volume_share * 0.4) + (avg_hours_share * 0.3) + ((6 - avg_csat) / 5 * 0.3)
billing wins. fix billing first.
step 7: chart the result
select the first pivot table → Insert → Bar Chart. sort descending. add data labels. change the title to “Tickets by category, april 2026.”
repeat for the resolution-time pivot.
side-by-side, the two charts tell the entire story to anyone who sees them, including future-you in three months when you forget what you decided.
the metric every solopreneur should track monthly
beyond category breakdowns, the single most useful trend metric is “average resolution hours, weekly.” plot it. the trendline tells you whether your product is getting better, worse, or holding steady at supporting itself. our google sheets pivot table tutorial covers the broader pivot pattern this analysis depends on, and our building a sales tracker in Google Sheets shows the same structural workflow applied to revenue data.
advanced: time-of-day and day-of-week patterns
add helper columns:
hour_created==HOUR(B2)weekday_created==TEXT(B2, "ddd")
build a pivot with weekday_created as rows and category as columns. you will often find that billing tickets cluster on Mondays (renewal day for many SaaS products) and how-to tickets cluster on Fridays (people clearing their inbox). knowing this lets you staff response time better.
frequently asked questions
how many tickets do I need before this is meaningful?
at least 30 in any single category before you draw conclusions. with our 18-row sample, billing has only 4 rows, which is directional only. for a real decision you would want 30+ billing tickets across multiple weeks.
should I categorize tickets myself or use AI?
for under 200 tickets, do it yourself. it takes an hour and you learn the product better. above 200, ChatGPT or Claude with a prompt like “categorize these support tickets into login_bug, export_bug, billing, how_to, feature_request, other” works well. our chatgpt code interpreter tutorial covers the workflow. always spot-check 10-20% of AI categorizations.
how do I import tickets from Helpscout, Zendesk, Intercom?
each tool has a CSV export. the columns differ slightly. map them to the schema in this tutorial (ticket_id, created_date, resolved_date, customer_plan, category, priority, response time, resolution time, csat). a one-time mapping spreadsheet saves hours later.
what about the open tickets?
filter them out for category-level analysis (resolution time is unknown), but keep a separate count of “open tickets older than 48 hours” as a watch metric. that is your SLA breach indicator.
how often should I run this analysis?
monthly for steady-state, weekly during a release. the work after the first setup is 10 minutes. the value is high enough that this is one of the highest-ROI 10 minutes in solopreneur ops.
conclusion: turn this into a monthly habit
ticket analysis is one of those workflows where the second time is 5x faster than the first. once you have the validation, the pivot templates, and the priority matrix, refreshing the analysis is a 10-minute job. that means you can run it monthly without breaking momentum on actual product work.
start this week. take last month’s tickets, paste them into the schema above, build the four pivots, write three sentences in a Slack message or doc: which category is the biggest drain, which plan is suffering most, what you will fix next. that is the entire deliverable. it will out-perform a $200-a-month support analytics tool, because you actually read it.
if you want broader context on building dashboards, our how to build a business dashboard guide walks the visualization layer, and our companion marketing funnel analysis tutorial applies the same Excel pattern to acquisition data.