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.
Benefits of the template
The report provides
- Number of ratings and Average product rating, both overall and by each product.
- Identify the best (and worst) rated products
- Compare product ratings across products in a category
- 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.
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.
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‘.
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.
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.
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).
Average Rating by Product
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.
Comments
There is a section for you to write any comments/notes about the data.
Print / Export to PDF
You can print or export the Report sheet as PDF and share.
Related Templates
Recommended Templates
If you have any suggestions to improve the template and make it useful for more people, please post them in the comments below.