How to Analyze Trustpilot Reviews in Excel

An exported review file is only the starting point. The real value appears when you can answer practical questions: Which problems occur most often? Are ratings improving? What do five-star reviewers value? Which negative reviews have not received a reply?

You can answer all of these in Excel without building a complex analytics system. This tutorial presents a repeatable workflow for cleaning, classifying, summarizing, and visualizing Trustpilot review data.

Before You Begin

First, create an XLSX export with Trustpilot Reviews Scraper. If you have not exported the data yet, follow our step-by-step guide on how to export Trustpilot reviews.

The exported workbook includes structured columns for rating, title, review content, reply, language, reviewer details, company information, experience date, publication date, review link, and more.

Keep the original download unchanged. Save a working copy for cleaning and analysis so you can always return to the source export.

Step 1: Convert the Rows into an Excel Table

Open the workbook, click anywhere inside the data, and choose Insert → Table. Confirm that the table has headers.

An Excel table gives you:

• Filter and sort controls on every column
• Formulas that automatically fill new rows
• Easier pivot-table creation
• Readable column references instead of cell coordinates

Give the table a descriptive name such as Reviews. If you later append additional exports, the name will make formulas easier to understand.

Step 2: Normalize Dates

Review exports contain both Experienced Date and Published Date:

• Experienced Date describes when the customer says the experience occurred.
• Published Date describes when the review was posted.

Choose the date that matches your question. Use Published Date for reputation-reporting volume and response workflows. Use Experienced Date when investigating operational events, launches, outages, or delivery periods.

If Excel treats an imported date as text, add a helper column and convert it to a date value. After conversion, create another column named Published Month:

=TEXT([@[Published Date]],"yyyy-mm")

Grouping by yyyy-mm keeps months in chronological order and makes recurring reports easier to update.

Step 3: Build a Rating Summary

Create a PivotTable from the Reviews table:

1. Put Rating in Rows.
2. Put Review Id in Values and summarize it by Count.
3. Add the same count again and display it as % of Grand Total.

This creates a rating-distribution table instead of relying only on an overall average. Two businesses can have the same mean rating while having very different mixes of delighted, neutral, and unhappy customers.

For a time-based view, put Published Month in Rows, Rating in Columns, and the review count in Values. This shows whether changes in overall performance come from more positive reviews, fewer negative reviews, or simply a change in review volume.

Step 4: Add Consistent Theme Tags

Ratings tell you how customers feel, but review text tells you why. Add a new column named Primary Theme and define a short list that reflects your business, for example:

• Product quality
• Delivery
• Returns and refunds
• Customer support
• Billing
• Website or checkout
• Pricing
• Communication

Read the review title and content together, then assign the most important theme. If a review covers several topics, add a Secondary Theme column rather than putting an inconsistent comma-separated list in one cell.

The goal is not to create a perfect taxonomy on the first pass. It is to use the same label for the same issue. Decide, for example, whether “shipping,” “courier,” and “late parcel” all belong under Delivery, then apply that decision consistently.

Step 5: Separate Evidence from Interpretation

Add columns for the decisions your team makes during analysis, but keep the exported source columns intact. A useful structure is:

Do not replace the customer's words with your summary. The original Review Content and Review Link should remain available so anyone reading the report can check the evidence behind a classification.

Step 6: Analyze Public Replies

The Reply field is empty when no reply is available in the exported review. Add a Reply Status helper column:

=IF(LEN(TRIM([@Reply]))>0,"Replied","No reply")

Then build a PivotTable with Rating in Rows, Reply Status in Columns, and a review count in Values.

This analysis can reveal gaps such as unanswered one-star reviews or a response process that focuses on negative feedback but ignores useful questions in otherwise positive reviews.

Response coverage alone is not a quality measure. Read a sample of replies and assess whether they acknowledge the specific issue, provide a clear next step, avoid exposing personal information, and use a respectful tone.

Step 7: Create a Small Review Dashboard

A useful dashboard does not need dozens of charts. Start with four elements:

1. Total reviews in the selected period
2. Rating distribution as percentages
3. Review volume by month
4. Top themes, split by rating group if helpful

Add slicers for Rating, Published Month, Language, and Primary Theme. A reviewer-country slicer can also be useful, but do not assume country alone explains a customer's experience.

Choose chart types that preserve meaning. A column chart works well for rating distribution, a line chart for monthly volume, and a horizontal bar chart for ranked themes. Avoid 3D charts because they make values harder to compare.

Step 8: Read the Reviews Behind Every Metric

Counts point you toward a pattern; they do not explain it by themselves. For each high-volume theme:

1. Filter to the relevant rating and theme.
2. Read a balanced sample across the reporting period.
3. Follow the Review Link back to the source when context is unclear.
4. Record representative findings in neutral language.
5. Separate what the data shows from what you infer.

For example, “27 reviews mention late delivery” is an observation. “The warehouse is understaffed” is a possible explanation that requires separate operational evidence.

A Repeatable Monthly Workflow

Once your workbook is ready, use the same process each month:

• Export the new period's reviews.
• Preserve the original file.
• Append rows to the analysis table and remove exact duplicates by Review Id.
• Check date formats and blank fields.
• Apply the existing theme definitions.
• Refresh PivotTables and charts.
• Read the reviews behind material changes.
• Record actions, owners, and follow-up dates outside the raw-data columns.

If you combine exports from several businesses, add a Source Business column before appending them. This prevents accidental comparisons or totals that mix different companies.

Common Analysis Mistakes

Treating the average rating as the whole story

An average hides distribution, volume, and text. Always review the rating mix and the reasons customers give.

Letting theme names drift

If “support,” “customer service,” and “help desk” are used as separate labels without a clear reason, the resulting counts will understate the real pattern. Maintain a short theme dictionary in a separate worksheet.

Confusing publication date with experience date

A review published today may describe an experience from weeks earlier. Select the appropriate date and clearly label charts so readers know which one you used.

Presenting correlation as cause

A spike in negative reviews after a product change is worth investigating, but the review dataset alone may not prove that the change caused the complaints.

Ignoring data governance

Review exports can contain reviewer names, profile identifiers, avatars, and country codes. Restrict access to the workbook, retain only what the analysis requires, and follow applicable policies and laws.

Turn Reviews into Decisions

The best review analysis is not the workbook with the most formulas. It is the one that preserves the source evidence, uses consistent classifications, and leads to a clear operational decision.

Start with a clean export, build a small set of reliable summaries, and then read the underlying reviews before making recommendations.

Export Trustpilot reviews for your Excel analysis →

Trustpilot Reviews Scraper is an independent tool from ExtensionsGod and is not affiliated with or endorsed by Trustpilot. Use exported data in accordance with applicable laws, Trustpilot's Terms of Service, and your organization's policies.