Create a 5 Star Rating System in Excel

In this video, we create a 5 star rating system in Excel using Conditional Formatting.
The formula below is used in the video to calculate what star is needed in each cell.
=IF($B2*=I$1,1,IF(INT($B2)=I$1-1,MOD($B2,1),0))
Use the greater than sign in place of the asterisk. The asterisk is used as angle brackets are not allowed in video descriptions.
Conditional Formatting is then applied to these cells to show the star ratings.
Find more great free tutorials at;
www.computerga...
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/Ultimat...
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1

[@mahamohan1]

After going through a number of UA-cam tutorials on this subject, I find your tutorial much useful. Most of the tutorials are based on whole number, where as yours take into account of decimals also for rating purpose. Nice tutorial.

[@Computergaga]

Thank you Mahamohan.

[@valclark5552]

This is really nice, and not too difficult. Just what I needed for my vendor rating project. Thank you!!

[@Computergaga]

You're welcome Val.

[@karenaylmer]

I just copied the formula you presented without knowing much the function of the formula. Thank you!

[@semperdiscendum7439]

That was awesome Sir!! Please keep such videos coming. 👍👍

[@Computergaga]

Thank you Shashikant. Will do.

[@5minutetutorial756]

Nice video! Thanks!

[@Computergaga]

Thank you.

[@cocktail7426]

thanks sir, u really made it simple for me. hats off for u.

[@Computergaga]

You're welcome, Shahzada. Thank you.

[@jingyicai4925]

Hey sir, hope u can help with this. It’s working fine for me in Excel. Now what would I do if I want to copy paste it into word document? Thank you in advance!

[@mellyayble]

omg i probably watched this 50 times and just could not get it. somehow my error was because i wanted the 1,2,3,4,5 numbers on another sheet and was getting an error with that, i think. when i put it on the same sheet and just hid the cells it worked first time. i have been trying to figure this out for an embarrassing amount of time lol

[@Memer44]

Your method is fascinating, but how do you calculate the mean rating score in the 5 point scale.
I just can't find it anywhere. 😵

[@mayanksinghal8787]

Thankyou sir for being a teacher 👍

[@Computergaga]

Thank you Mayank.

[@swap9423]

Great explanation. Thanks. Definitely subscribing

[@Computergaga]

Thanks for the sub and for your comments. Happy to help Swapnil.

[@giorgosi.fovakis8810]

Excellent job!

[@Computergaga]

Thank you very much, Giorgos.

[@yogieerlanggahaq5660]

I've tried the formula and steps given, but I'm still wrong.

[@joestarfish]

Thank you

[@Computergaga]

You're welcome 👍

[@iotsugua]

That's one useful video!

[@Computergaga]

Thank you very much.

[@CaseyCorriere]

Hi! How would the formula change if my rating was a whole number and didn't include a decimal? I can't figure it out. Any cell with a whole number is coming back as 0. HELP! Thank you :)

[@matsdevries1244]

Hi! To exercise, I gave ny sheet the same structure as you and I copied the formula, but I am still getting an error. I do not know how to solve this.

[@Computergaga]

What error are you getting Mats

[@ZacharyMcLellanMusic]

Hey! Was wondering if you could lend a hand... I applied this method to a 10 pt scale, making a few minor adjustments, and it's worked for the most part. I'm just unable to get half stars at all. Any thoughts as to why that is? I've double checked everything and am still unsure as to why it's not working.

[@MistressOP]

does this work for google sheets?

[@keshosingh5645]

thank you Sir

[@Computergaga]

You're welcome, Kesho.

[@chinmayeesingh9040]

nice video sir

[@santhoshpalar8672]

How to do this in googlesheet?

[@Computergaga]

Probably similar, but I don't use Google Sheets so couldn't say.

[@MistressOP]

@@Computergaga thanks for the info too bad :(

[@syeduzairahmed7144]

WHAT'S THE SHORTCUT TO LOCK THE CELL, YOU ARE QUITE FAST IN LOCKING IN IT ?

[@Computergaga]

F4 👍

[@FirstLast-lx8lr]

I got an error in a cell but everything else worked. I am working with %. I have a value of 58% and the stars set up so that 1star=25%, 2 stars=50%, 3 stars =75%, 4 stars =100% and 5 stars = 125%.There is one cell valued at 58% and the stars are displayed as:First and second star are full stars, the third and fifth star are blank and the fourth star is half full. It should just read at the first two stars being full and the third star as half full.What am I missing?

[@sundersunder6042]

How to merge all the stars in single cell

[@pixelq6471]

if i copy paste the formula into my excel sheet and try to use it it gives me an error message that there is a problem with the formula

[@Computergaga]

Ok. Difficult to know why this is without seeing it. Check the value being used is definitely a number. Also, where are you based? You may need a semi colon (;) instead of a comma (,)

[@pixelq6471]

@@Computergaga oh yeah i needed to replace the commas with semi colons. thanks fot he help!

[@WanderyenErin]

4:27 how do you put automatically this $ thing?

[@WanderyenErin]

oh I found it: F4

[@Computergaga]

Nice one!

[@amitsemwal1572]

Damn Man, this is some woodoo shit!!
Jk, but it's cool.

[@Computergaga]

Thank you Amit 😄

[@gevkhalulyan3583]

thank you

[@Computergaga]

You're welcome 👍