Create a Five Star Rating System

This post will guide you how to create a five star rating system in your current worksheet in excel. How do I change the five points system to five star rating in excel. How to use the conditional formatting function to create a five star rating system in excel.

Create a Five Star Rating System


The five star rating system is a very useful way of visualizing rating like you see on Amazon site.

For example, you have a list of data in your current worksheet and it contains rating values in each cell in rating column, and you want to create a five star rating system in your worksheet, just do the following steps:

#1 select the range of cells (C1:G1) and merging it as one cell, then type “Star Rating” text in this cell.

create five star rating1

#2 type the numbers from 1 to 5 into each cells of range H1:L1

create five star rating2

#3 type the following formula into the formula box of Cell C2, then drag the Auto Fill Handle over other cells (C2:G3) to apply this formula.

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

create five star rating3

#4 select the range of star rating (C2:G3), go to HOME tab, click Conditional Formatting command under Styles group, then select New Rule from the drop down list. The New Formatting Rule dialog will appear.

create five star rating system1

#5 click Format all cells based on their values from Select a Rule Type list. Choose Icon Sets from the Format Style, select 3 stars icon from Icon Style. Check Show Icon Only box. Then set a full star is shown if the value is greater than or equal to 1, and a half star if the value is greater than or equal to 0.5.

create five star rating4

#6 let’s see the result.

create five star rating5

Related Functions


  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel MOD function
    he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
  • Excel INT function
    The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)…
Related Posts
How to Sum Every Nth Column in Excel
Sum Every Nth Column 27

Sometimes for data listed in rows, we may want to sum data every Nth column, for example sum data only in ODD column or EVEN column (every 2 column). In our daily life, we may meet many cases like this. ...

How to Count Cells that Contain even or odd numbers in Excel
count cells that contain even numbers5

This post will guide you how to count the number of cells that contain odd or even numbers within a range of cells using a formula in Excel 2013/2016.How do I count cells that contain odd numbers through the use ...

How to Count Number of Cells that Contain Errors in Excel
count number cells that contain errors6

This post will guide you how to count the number of cells that contain errors within a range of cells using a formula in Excel 2013/2016.You can easily to count cells equal to a specific value or text string through ...

How to Sum by Group in Excel

Sometimes we may have the requirement that subtotal data by group in a table. To just subtotal data in a column, we can use SUM function directly; for data in a column, amount data in another column, we can use ...

How to Compare Two Columns and Remove the Duplicate Values by Formula in Excel
How to Compare Two Columns and Remove the Duplicate Values by Formula 9

To compare two columns and remove the duplicate values, we have already posted a tutorial about how to solve this problem by ‘Conditional Formatting’ feature ‘Remove Duplicate’ rule. Actually, there are some other ways to solve it as well. If ...

How to Highlight Every Other Row or Every Nth Row in Excel?
How to Highlight Every Other Row or Every Nth Row in Excel 11

Sometimes we may want to highlight every other row or every Nth row in a spreadsheet to make data in different rows looks more clearly. Though we can apply table styles to highlight table rows automatically, we can also highlight ...

How to Calculate Total Work Days or Hours Exclude Weekends/Holidays in Excel
Calculate Total Work Days or Hours 15

In our daily work we often need to count the total working-days or working-hours for employees then we can pay for them the salary in some cases. But we have to count the total exclude weekends or holidays as we ...

How to Stack Data from Multiple Columns into One Column in Excel
Stack Data 12

In previous article, I have shown you the method to split data from one long column to multiple columns by VBA and Index function. This time if we want to stack data from multiple columns to one column, how can ...

How to Convert Julian Date to a Calendar date in Excel
convert julian date to calendar date3

This post will guide you how to convert Julian date to a calendar date in Excel. How do I convert a given Julian Date to a calendar date with a formula in Excel. What is Julian Date? Julian day number ...

How to Highlight Every Other Row Using Conditional Formatting in Excel
highlight every other row6

This post will guide you how to highlight every other row in Excel. How do I shade every other row with conditional formatting in Excel. Highlight Every Other Row Video: Highlight Every Other Row Highlight Every Other Row Assuming that ...

Sidebar