One common feature that users often request is the ability to add star ratings to their spreadsheets. While this feature is not built into Google Sheets, it is possible to create a star rating system using emojis and data validation.
Explore ↓
Hey there stranger!
Sign up to get access.
Ken Asks: Can We Add Star Ratings?
About this Tutorial
Sheet Resources
Video Transcript
Hello, welcome. Thanks so much for asking this question. Ken. Ken ask, can we add star ratings? So the actual question is a little more involved. He was asking one click star ratings. And unfortunately in order to do that, I imagine you have to do a little bit of scripting and you can sort of do that because let me just bear with me for just a second.
You can. Actually insert an image, oversells and then add a script to those. And you can create like an on click kind of script where you then can I imagine change the image. . But why, why I wanted to answer it in this way with data validation and emojis is because this actually adds a really cool element of ratings, star ratings to our lists or any kind of data we're trying to get from other people.
Maybe we're doing a survey or something maybe we wanna show some kind of rating system, and it could be stars, it could be. Any emoji. I used emojis here and used a data validation to give you a dropdown. So technically the answer is here's a one, two click answer. So it's double the amount of clicks you wanted, but it allows us to do a lot of really cool stuff in with this data that I'll go into once I show you how to do this.
So basically I created a 1, 2, 3, 4, 5 Range. You don't have to do this. You can actually do this within the data validation. Items I'll show you. So I just copied this from the black one from Black Star sorry, Emojipedia. I got copy these from. You can also go to Star and there's a glowing star.
There's a star. If you're rating like hotel stays, you can use beds you can say how many. You can just copy that and we can create a literally one to five beds. How would you rate your stay last night? This is gonna. Take a little bit of time. 1, 2, 3, 4. And you can create this this list, right?
Anywhere on say a, like a, a tab that says dropdowns. And then you can go to another tab. Let's use this. And you have maybe some data here. Oops. And you wanna add a dropdown for star rating, right? You wanna say rating. So here's what we do. Do. We would insert data validation list from a range. We just wanna select this range.
Okay. And save.
And now we can select 1, 2, 1 to five stars and that's it. Look, these glowing stars seem to look better at 15 and above font size. You can keep them blank. You can put You can select three stars, maybe as the center one. But people are hesitant to, to, I think four and five is sort of the ones that are most used.
It's like one, four, and five are the most used star ratings. So star ratings tend to also morph the data into what people are sort of Not one to rate is right around four and five, and then one different cultures rate differently. What you can also do, right, if you don't want to have another tab here, you can do this inside of just data validation.
So we, instead of doing insert. From a range, we can list an items and emojis can do that as well. 1, 2, 3, 1, 2, 3, 4, 1, 2, 3, 4, 5.
Now we have the same option here, but without having to refer to another page or another range. And we can morph this anyway we want, but now what's the issue? The, the next issue coming up is like, great, we have this great visual representation. Ratings, star ratings. One out of five or one outta 10, however you wanna do it.
Usually it's five. We may want to gather data from this, right? We want to say this is two stars, this is three stars. And then what is the average star rating? Well, let's see what happens when we try to average this, these values. Nothing. Right? Cause the value of function average caused a divide by error, divide by zero error.
There's nothing there. It's emojis. They are images, they're not numbers. So maybe we want to have, in this column or like in another sheet, we wanna say, okay, from this data we'll get these 3, 3, 1. And then f we want the average of this two D and here our average is 2.4 stars. Right. So how do we get these?
There are a couple ways to do that. One is the lookup. You can, if you have a range already of these stars, you can add 1, 2, 3, 4, 5 next to it. And this is a pretty typical way to that I've seen in. To grab this data. So we just create a V lookup and we say, okay, when it, when you say three emoji stars, you actually go to b2, two, c6, here, go to second second column and find out what that is.
So that's three. In this case, it's one in. here we can show you one what means one, five stars means five, and so on and so forth, and that's, you look up and that's absolutely usually one of the quickest ways to do it. Right? Right. You need four, four arguments, one of which will always be false for at least I always put false.
I really don't know why anyone would put true. Maybe I think on very, very long amounts of data. Putting true helps you do that V look up faster. I mean, you have like thousands of things, but yeah, you're just going for the search key, which is the emoji area, going for the range, which is gonna be wherever the emoji list is with the numbers.
And then you're gonna go to the second column. There's another way to do it with switch. This is sort of fun cuz it, you have to just copy and paste emojis a lot. And I'll do that again here for you, here. We'll add switch here without just copying that. So we go equal switch and we say, okay, the expression is the, the rating, right?
The D five. This is the dropdown menu and we're gonna copy this all the way down, just like. Avi lookup, but what are the expressions? And so Switch is giving us a set of case and value. So the cases are, what is the, the thing that it is and what do you want to express it as? And so we want to express we have to use quotation marks.
So we do one star in quotation marks one. because when there's one star, we want the number one comma. When there's two stars in quotation marks, we want comma, the number two. And we can do this for three as well. And number three, and then in four quota, in four stars, in quotation marks, we can do the number before in five stars.
We can't do the quotation and quotation five. And now when we change this, To two stars. We have the number two, we have the number four for four stars. We have five for five stars. Great. Now this is awesome because we can take stars, literal stars, and we can actually use different kinds of stars. We can use Black stars here.
We can use beds, we can use anything that emojis, , how whatever you use, balloons, frowny faces, you can even do something interesting like this. Let me do this. Give me a second. I'll be right back. So we can say something like, how was the service? And we can give them like a yay or no. Terrible. We can, we can put any amount and then we.
Say here, this equals switch. And whenever you have you want to use this expression, and whenever you have this frowny face, maybe put a negative one and we can get negative one here. We can do this, we can get this happy face. , we can add this and we can say, okay, when we have a frowny face, or sorry, the opposite of a frowny, a smiley face, put a one.
So now we can do smiley face equals one angry face means negative, negative one. So this is really cool. We can do thumbs up, thumbs down selections. We can then turn that into data, but. Here's one thing that's missing, and one thing you can do even better is what happens. Say if you have a data set and you're trying to display that data set to others, and you wanna take a rating that already exists.
Maybe you download this from a rating site, or you already have a rating site and you're getting gathering data or SEO ratings, and you're trying to visually represent the numbers one through five or good and bad. You can also. Do the opposite of with switch, you can take a number and switch it to a this, this emoji.
So let's do that here. Let's do switch. Switch from data. So we have like a bunch of ratings, right? Maybe we have like this and we want to go, okay. Equals switch. Why do we wanna switch? We wanna switch B3 and case one. It's gonna have one smiley face. In two, we're gonna have two smiley faces and quotation marks.
When we have the number three, we're gonna have three smiley faces. When we have number four can do four smiley faces. Number five, you see where I'm going with this? Five smiley faces. So it's literally just the opposite of taking so smiley faces or stars and moving them to numbers. We can dig numbers and move them to smiley.
So now we copy and paste this down and we suddenly have a visual representation of these numbers. And this is pretty darn cool. And this could really bring a smile to people's faces when they look at your data. They can actually visually represent these numbers instead of taking one like by the area.
So then what happens is this becomes like an area bar chart. Instead of looking at a single number, Also if you have, you know, the number one through 10 or something, that can even show more area here. But this is a really, really fun way to take numbers and move 'em into a visual representation without having to do something like a, a bar chart or a spark line or something really fun and, and may add some whimsy to your sheet.
So, thanks Ken for the question. I hope this was a, a fun answer for you. Bye.
You can. Actually insert an image, oversells and then add a script to those. And you can create like an on click kind of script where you then can I imagine change the image. . But why, why I wanted to answer it in this way with data validation and emojis is because this actually adds a really cool element of ratings, star ratings to our lists or any kind of data we're trying to get from other people.
Maybe we're doing a survey or something maybe we wanna show some kind of rating system, and it could be stars, it could be. Any emoji. I used emojis here and used a data validation to give you a dropdown. So technically the answer is here's a one, two click answer. So it's double the amount of clicks you wanted, but it allows us to do a lot of really cool stuff in with this data that I'll go into once I show you how to do this.
So basically I created a 1, 2, 3, 4, 5 Range. You don't have to do this. You can actually do this within the data validation. Items I'll show you. So I just copied this from the black one from Black Star sorry, Emojipedia. I got copy these from. You can also go to Star and there's a glowing star.
There's a star. If you're rating like hotel stays, you can use beds you can say how many. You can just copy that and we can create a literally one to five beds. How would you rate your stay last night? This is gonna. Take a little bit of time. 1, 2, 3, 4. And you can create this this list, right?
Anywhere on say a, like a, a tab that says dropdowns. And then you can go to another tab. Let's use this. And you have maybe some data here. Oops. And you wanna add a dropdown for star rating, right? You wanna say rating. So here's what we do. Do. We would insert data validation list from a range. We just wanna select this range.
Okay. And save.
And now we can select 1, 2, 1 to five stars and that's it. Look, these glowing stars seem to look better at 15 and above font size. You can keep them blank. You can put You can select three stars, maybe as the center one. But people are hesitant to, to, I think four and five is sort of the ones that are most used.
It's like one, four, and five are the most used star ratings. So star ratings tend to also morph the data into what people are sort of Not one to rate is right around four and five, and then one different cultures rate differently. What you can also do, right, if you don't want to have another tab here, you can do this inside of just data validation.
So we, instead of doing insert. From a range, we can list an items and emojis can do that as well. 1, 2, 3, 1, 2, 3, 4, 1, 2, 3, 4, 5.
Now we have the same option here, but without having to refer to another page or another range. And we can morph this anyway we want, but now what's the issue? The, the next issue coming up is like, great, we have this great visual representation. Ratings, star ratings. One out of five or one outta 10, however you wanna do it.
Usually it's five. We may want to gather data from this, right? We want to say this is two stars, this is three stars. And then what is the average star rating? Well, let's see what happens when we try to average this, these values. Nothing. Right? Cause the value of function average caused a divide by error, divide by zero error.
There's nothing there. It's emojis. They are images, they're not numbers. So maybe we want to have, in this column or like in another sheet, we wanna say, okay, from this data we'll get these 3, 3, 1. And then f we want the average of this two D and here our average is 2.4 stars. Right. So how do we get these?
There are a couple ways to do that. One is the lookup. You can, if you have a range already of these stars, you can add 1, 2, 3, 4, 5 next to it. And this is a pretty typical way to that I've seen in. To grab this data. So we just create a V lookup and we say, okay, when it, when you say three emoji stars, you actually go to b2, two, c6, here, go to second second column and find out what that is.
So that's three. In this case, it's one in. here we can show you one what means one, five stars means five, and so on and so forth, and that's, you look up and that's absolutely usually one of the quickest ways to do it. Right? Right. You need four, four arguments, one of which will always be false for at least I always put false.
I really don't know why anyone would put true. Maybe I think on very, very long amounts of data. Putting true helps you do that V look up faster. I mean, you have like thousands of things, but yeah, you're just going for the search key, which is the emoji area, going for the range, which is gonna be wherever the emoji list is with the numbers.
And then you're gonna go to the second column. There's another way to do it with switch. This is sort of fun cuz it, you have to just copy and paste emojis a lot. And I'll do that again here for you, here. We'll add switch here without just copying that. So we go equal switch and we say, okay, the expression is the, the rating, right?
The D five. This is the dropdown menu and we're gonna copy this all the way down, just like. Avi lookup, but what are the expressions? And so Switch is giving us a set of case and value. So the cases are, what is the, the thing that it is and what do you want to express it as? And so we want to express we have to use quotation marks.
So we do one star in quotation marks one. because when there's one star, we want the number one comma. When there's two stars in quotation marks, we want comma, the number two. And we can do this for three as well. And number three, and then in four quota, in four stars, in quotation marks, we can do the number before in five stars.
We can't do the quotation and quotation five. And now when we change this, To two stars. We have the number two, we have the number four for four stars. We have five for five stars. Great. Now this is awesome because we can take stars, literal stars, and we can actually use different kinds of stars. We can use Black stars here.
We can use beds, we can use anything that emojis, , how whatever you use, balloons, frowny faces, you can even do something interesting like this. Let me do this. Give me a second. I'll be right back. So we can say something like, how was the service? And we can give them like a yay or no. Terrible. We can, we can put any amount and then we.
Say here, this equals switch. And whenever you have you want to use this expression, and whenever you have this frowny face, maybe put a negative one and we can get negative one here. We can do this, we can get this happy face. , we can add this and we can say, okay, when we have a frowny face, or sorry, the opposite of a frowny, a smiley face, put a one.
So now we can do smiley face equals one angry face means negative, negative one. So this is really cool. We can do thumbs up, thumbs down selections. We can then turn that into data, but. Here's one thing that's missing, and one thing you can do even better is what happens. Say if you have a data set and you're trying to display that data set to others, and you wanna take a rating that already exists.
Maybe you download this from a rating site, or you already have a rating site and you're getting gathering data or SEO ratings, and you're trying to visually represent the numbers one through five or good and bad. You can also. Do the opposite of with switch, you can take a number and switch it to a this, this emoji.
So let's do that here. Let's do switch. Switch from data. So we have like a bunch of ratings, right? Maybe we have like this and we want to go, okay. Equals switch. Why do we wanna switch? We wanna switch B3 and case one. It's gonna have one smiley face. In two, we're gonna have two smiley faces and quotation marks.
When we have the number three, we're gonna have three smiley faces. When we have number four can do four smiley faces. Number five, you see where I'm going with this? Five smiley faces. So it's literally just the opposite of taking so smiley faces or stars and moving them to numbers. We can dig numbers and move them to smiley.
So now we copy and paste this down and we suddenly have a visual representation of these numbers. And this is pretty darn cool. And this could really bring a smile to people's faces when they look at your data. They can actually visually represent these numbers instead of taking one like by the area.
So then what happens is this becomes like an area bar chart. Instead of looking at a single number, Also if you have, you know, the number one through 10 or something, that can even show more area here. But this is a really, really fun way to take numbers and move 'em into a visual representation without having to do something like a, a bar chart or a spark line or something really fun and, and may add some whimsy to your sheet.
So, thanks Ken for the question. I hope this was a, a fun answer for you. Bye.