Five Star Rating in Excel

Five Star Rating in Excel

In this Chapter we will be learning how to create star rating in Excel. we will be using the standard five star rating procedure

Five Star Rating in Excel Step1: Data

We will be using the following data to create the star rating in Excel. The data has list of products and its associated Rating.

ProductRating
Product11.2
Product21.6
Product34.5
Product43.6
Product52.9
Product63.4
Product74.9
Product84.1
Product92.3
Product102.1

Five Star Rating in Excel Step2: create 5 blank columns

Create five blank columns next to our data set. In this blank columns we are going to build excel five star chart. At this stage the data will look like

star rating in excel excel star rating 1

Five Star Rating in Excel Step 3: Formula in the five grid column

Now we need to write a formula to fill values in the five grid column based on which the star rating is formed.

So, the basic idea is, if a product has 4.30 rating, we want to print 1, 1, 1, 1 and .30 in five columns.

We will be using formula =IF(C$1<=$B3,1,IF(ROUNDUP($B3,0)=C$1,MOD($B3,1),0)) to fill the first star column of the product1.

What is this formula is about ?

This formula compares the rating value with the each of the 5 column values

star rating in excel excel star rating 2

If the column value less than or equal to rating value then it replaces the cell value
with 1.

If the column value is greater than the rating value then check the roundup of rating value is equal to column value, If so then take the modulus of the rating value and replace it with the cell value, If not Replace the cell value with 0.

On applying above logic the data will be looking like

star rating in excel excel star rating 2a

Five Star Rating in Excel Step 4: Apply conditional formatting to get the stars

Select the 5 column grid and apply conditional formatting (Home > Conditional Formatting > New rule)

  • From the format style Select Icon Sets in the Drop down as shown below

star rating in excel excel star rating 3

  • In the Icon Style choose stars
  • Then change the type to numbers
  • Mention the Value as 1 and 0.5
  • Enable Show Icon only option

As shown below

star rating in excel excel star rating 4

Click OK button. You are done!!. Star rating in Excel has been created successfully as shown below

star rating in excel excel star rating 5

previous small excel star rating                                                                                                                 next small star rating in excel