How to Create a Rating Scale in Excel (4 Easy Ways)

Rating scales are used to evaluate responders’ feedback towards a particular product. Excel can help greatly in creating a rating scale for any number of products. In this article, we will show you how to create a rating scale in Excel in four easy ways.

How to Create a Rating Scale in Excel: 4 Suitable Ways

In this article, we will demonstrate four easy ways to create a rating scale in Excel. We will use the following dataset for this purpose.

1. Use Conditional Formatting Feature to Create a Star Rating Scale in Excel

In the first method, we will use Conditional Formatting to create a star rating scale in Excel. The steps are discussed below.

Steps:

First of all, insert five columns and name them Star Rating beside the Rating column.

Next, write 1 to 5 in cells D14 to H14.
Then select cell D5 and write down the following formula.

=IF(D$14<=$C5,1,IF(ROUNDUP($C5,0)=D$14,MOD($C5,1),0))

Formula Breakdown:

MOD($C5,1) returns the remainder after dividing cell C5 by 1.
IF(ROUNDUP($C5,0)=D$14) rounds up the C5 cell value to 1.
IF(D$14<=$C5,1,IF(ROUNDUP($C5,0)=D$14,MOD($C5,1),0)) returns 1 if C5>D14 and returns the decimal part if C5<D14.

After that, Autofill the formula to the rest of the cells.

Then, from the Home tab, go to,

Home → Styles → Conditional Formatting → New Rule

A New Formatting Rule dialogue box will appear. Select Format all cells based on their values.
Then choose Icon Sets in the Format Style option.
Select 3 Stars as the Icon Style.
Next, change the Type to Number.
Set values to show a filled star, half-filled star, or empty star. We set values 8 and 0.3 in this example. Any value less than 0.3 will show an empty star, a value between 0.3 to 0.8 will show a half-filled star and a filled star will be visible if the value is greater than 0.8.
Finally, check the box of Show Icon Only and press Enter.

Hence, you will find star ratings for all the food items.

You can change any of the food ratings and the star rating will automatically get updated.

2. Apply REPT Function

In this method, you will learn how to use the REPT function in Excel to create data bars and star rating scales.

2.1 Add Data Bars to Create Rating Scale

Now we will create a rating scale by adding data bars. Read the following steps to do it.

Steps:

First, insert a column named Bar Rating. Then select cell D5 and type the following formula there.

=REPT("|",C5*10)

Then Autofill the formula to the rest of the cells in column D.

Now select the D column with all the vertical bars and change the text font to Playbill.

As a result, the vertical bars will look like one single wide bar.

Now we want to change the colors of the bars. To do it, select column D and from the Home tab, go to,

Home → Conditional Formatting → New Rule

Next, select Use a formula to determine which cells to format and write the below formula.

=C5<3

Then go to Format.

In the Format Cells box, go to the Font tab and change the Color to Red.
Then click OK.

As a result, the bars with ratings less than 3 will be turned into red.

Similarly, select cells with all bars and write down the following formula in the New Format Cells dialogue box.

=C5>4

Then select the color Green from the Font tab.

Hence, the bars with more than 4 ratings will look green.

Similarly, following the same process, the yellow color is chosen for values between 3 and 4.
Finally, to make it more appealing, increase the font size of the bars and align them to the left.

2.2 Create Star Rating Scale

In this method, we will create a star rating scale using the REPT function. Keep reading to learn how to do it.

Steps:

First of all, select any cell and then go to,

Insert → Symbols → Symbol

Then from the Wingdings font, insert the Star symbol.

Next, copy the star symbol.

Select cell D5 and type the following formula there. Paste the copied star between “”.

=REPT("«",IF(MOD(C5,1)>0.49,CEILING.MATH(C5),C5))

Formula Breakdown:

CEILING.MATH(C5) rounds up the C5 cell value to the next integer.
MOD(C5,1)>0.49 checks if the remainder of cell C5 divided by 1 is greater than 49 or not.

Now Autofill the formula to the rest of the cells in column D.

To convert the “<<” symbol to the Star symbol, select column D and change the font to Wingdings.

Hence, you will find that all the symbols have now turned into stars.

Next, we will change the font and color of the symbols to enhance their appearance.

Consequently, you will get your desired rating scale.

3. Utilize Form Control Feature to Create Star Rating Scale

Now we will use the Form Control feature to create a star rating scale in Excel. The procedure is discussed in the following section.

Steps:

First copy these two symbols (☆★) from here and paste them into two different cells.

Next, go to the Developer tab, and from there,

Developer → Insert → Form Control → Option Button

Click and drag your cursor to insert the Option button in cell D9.

Then double-click on the text to edit the text beside the button.

Similarly, add four more buttons to cells D8 to D5.

Now right-click on any of the buttons and select Format Control.

The Format Control box will pop up.
In the box, go to the Control tab and select Unchecked.
Then choose cell D10 to link the buttons.
After that, click Ok.

Next, write down the following formula in the Star Rating column.

=REPT(B10,5-D10)&REPT(C10,D10)

Then from the Home tab, go to,

Home → Alignment → Orientation → Vertical Text

As a result, the text will be vertically aligned now.
Finally, change the font and color to improve its look.

Now if you click on different buttons, the Star Rating will be updated automatically.

4. Create Dropdown List to Create a Rating Scale

In the last method, we will add dropdowns to cells to create a rating scale. Follow these steps to learn.

Steps:

First of all, create a column and write texts from Very Good to Very Poor in the column.

Then select the Rating column and from the Data tab, go to,

Data → Data Tools → Data Validation

The Data Validation dialogue box will appear.
In the box, go to the Settings tab and select List from Allow option.
Then choose the source from D5 to D9 and click OK.

Now select any cell in column C and you will see a dropdown button beside it.
Then click on the dropdown button and you will have all the options to choose from.
Click on any of the options to rate the food item.

Similarly, you can rate all the food items on the list.

Notes

You can follow the same steps to create rating scales with other symbols.
Additionally, you can create 1-10 and 1-100 scales following the above procedures.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Rating Scale.xlsx

Conclusion

Thanks for making it this far. I hope you find this article useful. Now you know four easy ways to create a rating scale in Excel. Please let us know if you have any further queries, and feel free to give us any recommendations in the comment section below.

<< Go Back to Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Create a Rating Scale in Excel (4 Easy Ways)

How to Create a Rating Scale in Excel: 4 Suitable Ways

1. Use Conditional Formatting Feature to Create a Star Rating Scale in Excel

2. Apply REPT Function

2.1 Add Data Bars to Create Rating Scale

2.2 Create Star Rating Scale

3. Utilize Form Control Feature to Create Star Rating Scale

4. Create Dropdown List to Create a Rating Scale

Conclusion

Related Articles

Leave a reply Cancel reply