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.
| Product | Rating |
|---|---|
| Product1 | 1.2 |
| Product2 | 1.6 |
| Product3 | 4.5 |
| Product4 | 3.6 |
| Product5 | 2.9 |
| Product6 | 3.4 |
| Product7 | 4.9 |
| Product8 | 4.1 |
| Product9 | 2.3 |
| Product10 | 2.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
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
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
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
- 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
Click OK button. You are done!!. Star rating in Excel has been created successfully as shown below
