• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Dashboards / Create a Five Star Rating System in Excel

Create a Five Star Rating System in Excel

Create a five star rating system in Excel using Conditional Formatting. This can be a useful way of visualising ratings like you see on eBay and Amazon.

Watch the Video – Star Rating System in Excel

How it Will Be Done

The Conditional Formatting feature of Excel has a 3 star rating option. However, we would like a 5 star rating system. So, we will need to be clever and use another technique.

In the image below I have 6 products with an average rating for each. There are then 5 columns, one for each star.

Set up for the star rating system

I have entered numbers 1 to 5 in cells J1:N1.The reasons for this will be clear when we start writing the formula.

Numbers entered in five columns

We will write a formula in each cell to calculate that part of the average score. Then the Conditional Formatting tool will insert the stars.

Writing the Formula for the Star Rating System

The following formula was used in cell C2 and copied to the other cells of the table.

=IF($B2>=J$1,1,IF(INT($B2)=J$1-1,MOD($B2,1),0))

The end result is this.

Excel formula to calculate the ratings

The first IF function tests if the rating is a greater number than the value in cell J1, and if it is, it puts a number 1 into the cell. As the formula is copied from cell C2 to D2 and E2 etc. The formula is tested against the values in cell K1, L1 etc.

The first rating has a number 1 in cells C2 to F2 because 4.3 is greater than the numbers 1, 2, 3 and 4.

The MOD function has been used to extract the decimal part of the rating. For the first rating this is 0.3. This is returned to the cell when the rating falls short.

Applying the Star Rating

To apply the Conditional Formatting rule,

  1. Select range C2:G7.
  2. Click Home > Conditional Formatting > New Rule.
  3. Select Icon Sets from the Format Style list, and then the star rating from the Icon Style list.
  4. Complete the Conditional Formatting rule like the image below. A full star is shown if the value is greater than or equal to 1, a half star if the value is greater than or equal to 0.5 and a blank star is less than 0.5.
  5. Select the Show Icon Only box to hide the formula results.
Icon Sets Conditional Formatting rule window settings applied

The completed five star rating system looks like this.

Five star rating system in Excel complete

If you have to work with reviews and feedback this can be a neat way for visualising these responses.

Related Tutorials

  • Essential Conditional Formatting tricks
  • Create a geographic heat map
  • Conditional Formatting with charts
  • 10 Paste Special tricks to make you a pro

Related Posts:

  • N Functions in Excel thumbnail
    N Function in Excel
  • Calculate age in Excel thumbnail
    How to Calculate Age in Excel
  • Moving average in Excel thumbnail
    Moving Average in Excel
  • SWITCH function feature
    Excel SWITCH Function - Nested IF Function Alternative

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar

Recent Posts

  • Excel SWITCH Function – Nested IF Function Alternative
  • Sum Formulas Only in Excel
  • How to Calculate Age in Excel
  • Custom Sort in Excel
  • Case Sensitive Lookup with XLOOKUP in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

LinkedIn Twitter YouTube

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Blog
  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2009 Computergaga · All Rights Reserved ·