Product Rating Report (5 Star Ratings) – Free Excel Template

As I was compiling the summary of product ratings I received for indzara.com products, I built a simple template to help me. I wanted to share it so that others can also use and benefit from it.

This simple Excel template will help create a report on product rating (5 Star Ratings) data. By entering products’ ratings data in a simple table, the report is automatically generated.

Product Rating Report - Excel Template - Report
Product Rating Report – Excel Template – Report

Benefits of the template

The report provides

  1. Number of ratings and Average product rating, both overall and by each product.
  2. Identify the best (and worst) rated products
  3. Compare product ratings across products in a category
  4. Breakdown of ratings by Stars (1 to 5 stars)

You can also use the slicer in the report to drill down to specific product categories.

Requirements

The template works in Excel 2010 and above for Windows.

Free Download

Video Tour


How to use the template?

The template has two visible sheets (DATA and REPORT).

Enter Data

In the DATA sheet, enter your product rating data.

Note: Please start entering from row 8. Report will be created based on the data inside the Excel tables.  If you directly enter from row 10, the data will not be part of the table and hence report will not recognize this. Please watch the video where I demonstrate this. 

  • Enter Product Name
  • Enter number of ratings with 1 star, 2 stars, 3 stars, 4 stars and 5 stars.
  • Enter Product Category to categorize your products if you have a lot of products.
Product Rating Report - Excel Template - Data Entry
Product Rating Report – Excel Template – Data Entry

There are three calculated columns in the table. They have formulas and please do not modify them.

  • Rating Count: Number of ratings for each product
  • Avg Rating: Average Product rating for each product
  • Rating Rank: Rank of products based on Avg. Rating. For example, Rating Rank of 1 will allow you to see the top rated product.
Product Rating Report - Excel Template - Calculated Data
Product Rating Report – Excel Template – Calculated Data

Sort Products

You can sort products by any of the fields available. For example, to sort by the product average rating (from best to worst), sort the AVG RATING column by choosing ‘Sort Largest to Smallest‘.

Product Rating Report - Excel Template - Sort Products
Product Rating Report – Excel Template – Sort Products

Filter Products

You can also filter products by any of the fields available. For example, to filter products by the product average rating, filter on the AVG RATING column by checking only specific rating values.

Product Rating Report - Excel Template - Filter
Product Rating Report – Excel Template – Filter

Report

Once you have entered data, please view the REPORT sheet.

Note: Please refresh the report by clicking on ‘Refresh All’ button in the DATA ribbon. This will update the pivot tables that feed the Report sheet. Otherwise, the newly entered/updated data will not be reflected in the Report sheet.

Product Rating Report - Excel Template - Data Refresh
Product Rating Report – Excel Template – Data Refresh

This sheet is protected to prevent unintentional editing. If you would like to edit, please use indzara as the password.

After you refreshed the data, the Report sheet will provide the following.

Summary

Total number of ratings, Average Rating and star rating breakdown will be displayed (as shown below).

Product Rating Report - Report Summary
Product Rating Report – Report Summary

Average Rating by Product

Product Rating Report - Report - Avg Rating by Product
Product Rating Report – Report – Avg Rating by Product

Number of Ratings by Product

Product Rating Report - Report - Number of Ratings by Product
Product Rating Report – Report – Number of Ratings by Product

The above Report elements can be filtered by Product Category. The slicer at the top allows you to select one or multiple Product Categories so that you can look at metrics for those specific products.

Product Rating Report - Report - Product Category Slicer
Product Rating Report – Report – Product Category Slicer

Comments

There is a section for you to write any comments/notes about the data.

Product Rating Report - Excel template - Comments
Product Rating Report – Excel template – Comments

Print / Export to PDF

You can print or export the Report sheet as PDF and share.

If you have any suggestions to improve the template and make it useful for more people, please post them in the comments below.

Leave a Reply

Your email address will not be published. Required fields are marked *