How to Track Your 5-Star Review Rate and Measure Customer Satisfaction in Excel

How to Calculate 5-Star Review Rate % in Excel

1. Create a Review Flag Column

• Start with the service date and review rating fields from your dataset.
• Add a new helper column to identify whether a review was left.
• Use the IF function so each row returns:
  • 0 when the review rating is blank
  • 1 when a review exists
• This gives you a clean review flag that can be totaled later.

This helper column becomes the base for counting total reviews.

2. Create a Month Cohort Column

• Add another helper column to group every review into the correct month.
• Use the EOMONTH function with the service date so each row is assigned to the end of its month.
• This ensures all records from the same month are grouped together consistently.

Now your data is ready to be summarized by month.

3. Build a Unique List of Months

• Create a separate summary area for your monthly analysis.
• Use the UNIQUE function on the month cohort column to return one row per month.
• Format the results as dates.
• If needed, wrap the list with the SORT function so the months appear in chronological order.

This creates the monthly structure for the KPI table.

4. Calculate Total Reviews by Month

• Add a column for Total Reviews in your summary table.
• Use the SUMIFS function to total the review flag column for each month.
• Because the review flag only contains 1s and 0s, summing the column gives you the total number of reviews left in that month.
• Copy the formula down for every month in your summary.

This becomes the denominator for the review rate.

5. Count the 5-Star Reviews

• Add another column for 5-Star Reviews.
• Use the COUNTIFS function to count rows where:
  • the review rating equals 5
  • the month matches the month in your summary table
• Copy the formula down for all months.

This gives you the monthly numerator for the KPI.

6. Calculate Non-5-Star Reviews

• Add a column for Non-5-Star Reviews.
• Subtract the 5-star review count from total reviews.
• This creates the second review category needed for the chart.

Using this approach makes it easier to build a stacked column visual later.

7. Calculate the 5-Star Review Rate

• Add a new column for 5-Star Review Rate %.
• Divide the 5-star review count by total reviews.
• Format the results as percentages using Ctrl + Shift + 5.
• Copy the calculation down through the monthly summary.

Now you have the monthly KPI trend.

8. Create a Target Line

• Add a Target column beside the review rate.
• Enter your benchmark in the first row, such as 50%.
• In the rows below, reference the cell above so the target repeats dynamically for every month.

This creates a consistent benchmark line for the chart.

9. Select the Right Data for the Chart

• Highlight the Month column.
• Then, while holding Ctrl, highlight:
  • Non-5-Star Reviews
  • 5-Star Reviews
  • 5-Star Review Rate %
  • Target
• Do not include Total Reviews in the chart selection.

This keeps the visual focused on the KPI and supporting review counts.

10. Build a Combo Chart

• Go to Insert and choose Recommended Charts.
• Then switch to All Charts and choose Combo.
• Set:
  • Non-5-Star Reviews as a stacked column
  • 5-Star Reviews as a stacked column
  • 5-Star Review Rate % as a line
  • Target as a line
• Assign the axes so the review rate is emphasized properly on the chart.

This creates a visual that combines volume and performance in one place.

11. Adjust the Axes to Match the KPI Focus

• Move the percentage-based series so the 5-Star Review Rate % is the main focus on the y-axis.
• Place the review counts on the opposite axis.
• This makes the chart easier to read because the KPI is percentage-based, while the review counts are supporting context.

That layout makes the chart more aligned with the actual metric being tracked.

12. Format the Chart for Clarity

• Change the default colors so the stacked columns and lines are easier to distinguish.
• Reduce the thickness of the target line if it feels too heavy.
• Consider using a dashed style for the target so it stands out without overpowering the chart.
• Update the chart title to match the KPI clearly.

Simple formatting changes make the chart much easier to interpret.

13. Clean Up the Date Labels

• Adjust the month labels so they display cleanly on the x-axis.
• Update the number format in the chart if the dates take up too much space.
• If needed, also format the source month cells so the summary table and chart stay consistent.

This helps keep the dashboard compact and readable.

14. Add Data Labels Selectively

• Instead of labeling every series, add labels only where they add value.
• Good options include:
  • just the review rate line
  • only a few important high and low points
  • only the 5-star portion of the stacked columns
• You can also format label backgrounds or font styles to improve readability.

Selective labeling keeps the chart informative without making it cluttered.

15. Reuse the Same Structure for Other Breakdowns

• Once the chart is built by month, the same setup can be reused for other views.
• You can swap the monthly grouping for other dimensions such as:
  • acquisition channel
  • technician
  • dispatcher
  • customer service representative
  • job type
  • maintenance plan status
• The overall method stays the same, using the same helper columns and summary logic.

That makes this layout flexible for deeper customer success analysis.