How to Create a Star Rating System in Excel
Star rating systems are commonly used to rank things like films, TV shows, hotels, and shopping products. Typically, they are done on a scale of 1–5 (or 0–5), although you may have seen the odd six-star hotel or 10-star energy-rated home. Some sources also use half stars to add a touch of ambivalence.
This article explores the methods you can use in Excel to create star rating systems.
Before We Start
Download the example workbook:
Use an Excel for Microsoft 365 desktop version for maximum compatibility.
There are four worksheets: Part A, Part B, Part C, and Extras.
Part A
This worksheet contains examples that use traditional methods.
The first thing you need to understand is how to make a star show up in a cell. Behind every icon is a value, and this dictates whether a full star, half star or empty star is displayed.
How to display star icons in cells
Apply the star icon set to a range by going to: Home (tab) > Conditional Formatting (dropdown) > Icon Sets (dropdown) > 3 Stars (button).
To adjust the rule, go to Manage Rules… in the Conditional Formatting dropdown.
Highlight Icon Set and click Edit Rule.
Here, you can set the parameter values to determine which star should show up based on a cell’s value.
ℹ️
All examples in Part A use the default values>= 67%and>= 33%.
These percentages mean:
- If the cell value is greater than or equal to 0.67, display a full star.
- If the cell value is greater than or equal to 0.33, display a half star.
- If the cell value is below 0.33, display an empty star.
Table A.1
The Rating column contains a sequential list of numbers from 0 to 5, with each increasing in 0.1 increments. Example A.1.1 uses this to determine how many stars should display. Each one is in a separate cell and its formula returns either 1 (full star), 0.5 (half star) or 0 (empty star).
Look at cell I36:
The formula starts with an IF statement to check if the rating (2.5) is greater than or equal to the column number of A (1). As 2.5 >= 1 evaluates to TRUE, the [value_if_true] argument returns 1.
In cell K36, 2.5 is not greater than or equal to 3 so [value_if_false] kicks in.
Another IF statement checks to see if the rating converted to an integer (2) equals the column number of C minus 1 (3 – 1 = 2). As it does, [value_if_true] is proceeded with.
The MOD function calculates 2.5 ÷ 1 and returns a remainder of 0.5. This is fed into MROUND, which rounds it to the nearest 0.5, resulting in the same number.
Cell M36 returns 0 because neither 2.5 >= 5 nor 2 = 5 return TRUE, so the formula jumps to the final [value_if_false] argument.
Other Variations
Examples A.1.2 and A.1.3 use the same formulas as A.1.1. The difference is how they are outputted due to their settings in the Edit Formatting Rule window you saw earlier.
A.1.2 has the Show Icon Only checkbox ticked to hide the underlying cell values.
A.1.3 has No Cell Icon set for values less than 33%, meaning no empty stars will show.
Table A.2 is based on a scale of between 0–100 and its system is a little different to Table A.1.
The headings at the top are in increments of 20: 20, 40, 60, 80 and 100. These are used to determine the star type to display.
Let’s look at the formula in cell Z61.
The IF statement says if the rating (50) is less than 20, proceed to the [value_if_true] argument. As this is FALSE, the formula jumps to [value_if_false], so 1 is returned.
Now, look at cell AB61, which displays a half star.
Because the rating 50 is greater than 60, this time [value_if_true] is triggered.
Another IF condition checks to see if 50 is greater than 40 (60 — 20). It is, so MROUND calculates 50 — 60 + 20, which is 10 according to the rules of BODMAS. When rounded to the nearest multiple of 10, it stays at 10. The final part is to divide this figure by 20, so the calculation becomes 10 ÷ 20 = 0.5.
For an empty star, cell AD61 returns 0 as the logical_test in the second IF statement equates to 50 > 80, which is FALSE.
Part B
This section shows how to create star rating systems using modern methods.
Compared to Part A, the big difference is just one formula is used for each table column and the stars are not spread across multiple cells.
Table B.1 features two basic ways of returning stars based on the value in Rating.
Example B.1.1 uses the REPT function to repeat the star symbol 𝑥 number of times, with 𝑥 representing each figure in the Rating column. Here, unlike the number system you saw in Part A, SEQUENCE spills the numbers down from 0 to 10.
ℹ️
The star symbol appears as a question mark surrounded by a square in the formula bar due to the use of the font, Segoe MDL2 Assets. More details about this are found in the Extras section.
B.1.2 includes an IF statement to prevent values above 5 from being calculated. The IFERROR function also hides the #VALUE errors that occur with minus numbers.
Table B.2 takes things to the next level by featuring a couple of dynamic array versions of what you saw in Table A.1 in Part A. The scale is exactly the same—0–5 in increments of 0.1.
Let’s look at example B.2.1.
The formula uses the concatenation operator (&) to separate the first part, which determines how many full stars should display—from the second— which concentrates on the halves.
REPT repeats the star symbol according to the result of MROUND(--L11#,0.5), which produces the following:
{0;0;0;0.5;0.5;0.5;0.5;0.5;1;1;1;1;1;1.5;1.5;1.5;1.5;1.5;2;2;2;2;2;2.5;2.5;2.5;2.5;2.5;3;3;3;3;3;3.5;3.5;3.5;3.5;3.5;4;4;4;4;4;4.5;4.5;4.5;4.5;4.5;5;5;5}ℹ️
Notice the--operator that precedes the range in thenumberargument—this is necessary so the function works with spilt arrays. I wrote a LinkedIn post about this recently.
REPT can only repeat strings by a whole number, so you can’t return 0.5 of a symbol, for example. That’s why the number after the decimal place in each array index does not matter—it will just be ignored.
The second part uses a combination of IF, MOD and MROUND to check if each array index is not equal to 0. Those that evaluate to TRUE will return the half star symbol; those that don’t return a blank string.
Here is the conversion process:
IF(MOD(MROUND(--L11#,0.5),1)<>0=TRUE,
"[half star]",
"")
IF(MOD({0;0;0;0.5;0.5;0.5;0.5;0.5;1;1;1;1;1;1.5;1.5;1.5;1.5;1.5;2;2;2;2;2;2.5;2.5;2.5;2.5;2.5;3;3;3;3;3;3.5;3.5;3.5;3.5;3.5;4;4;4;4;4;4.5;4.5;4.5;4.5;4.5;5;5;5},1)<>0=TRUE,
"[half star]",
"")
IF({0;0;0;0.5;0.5;0.5;0.5;0.5;0;0;0;0;0;0.5;0.5;0.5;0.5;0.5;0;0;0;0;0;0.5;0.5;0.5;0.5;0.5;0;0;0;0;0;0.5;0.5;0.5;0.5;0.5;0;0;0;0;0;0.5;0.5;0.5;0.5;0.5;0;0;0}<>0=TRUE,
"[half star]",
"")
IF({FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE},
"[half star]",
"")Example B.2.2 is a variation on what you just saw, but it uses empty stars instead of blank strings.
The LET function holds the variable Rating and stores the same formula as the previous example. This is then concatenated to REPT("[empty star]",5-LEN(Rating), which subtracts the character length of Rating from 5 to return the empty stars.
Table B.3 contains two more variations — based on a scale of 0–100. Instead of MROUND though, the ROUND function in the first REPT divides the rating by 10 and rounds it to the nearest whole number. This is then divided by 2.
Changing the 10 affects the quantity of each star amount. A larger number will increase the range of rows that are occupied by that number of stars. For instance, swapping 10 for 20 will increase each block of stars from 20 to 40.
Dividing the whole of the ROUND statement by 2 pushes the stars down by 10 rows and doubles the span to 20. If it was 3, they would drop by 20 rows and there would be 40 in a block.
Here is the breakdown:
=REPT("[star]",
ROUND(P11#/10,0)/2)
=REPT("[star]",
{0;0;0;0;0;1;1;1;1;1;1;1;1;1;1;2;2;2;2;2;2;2;2;2;2;3;3;3;3;3;3;3;3;3;3;4;4;4;4;4;4;4;4;4;4;5;5;5;5;5;5;5;5;5;5;6;6;6;6;6;6;6;6;6;6;7;7;7;7;7;7;7;7;7;7;8;8;8;8;8;8;8;8;8;8;9;9;9;9;9;9;9;9;9;9;10;10;10;10;10;10}/2)
=REPT("[star]",{0;0;0;0;0;0.5;0.5;0.5;0.5;0.5;0.5;0.5;0.5;0.5;0.5;1;1;1;1;1;1;1;1;1;1;1.5;1.5;1.5;1.5;1.5;1.5;1.5;1.5;1.5;1.5;2;2;2;2;2;2;2;2;2;2;2.5;2.5;2.5;2.5;2.5;2.5;2.5;2.5;2.5;2.5;3;3;3;3;3;3;3;3;3;3;3.5;3.5;3.5;3.5;3.5;3.5;3.5;3.5;3.5;3.5;4;4;4;4;4;4;4;4;4;4;4.5;4.5;4.5;4.5;4.5;4.5;4.5;4.5;4.5;4.5;5;5;5;5;5;5})In the second part of the formula, the rating array is divided by 10 again and rounded to the nearest whole number. The MOD function then divides each number by 2 to leave a remainder of 1 or 0. If it’s the former, a half star is added to the others.
This is how it plays out:
REPT("[half star]",MOD(ROUND(P11#/10,0),2))
REPT("[half star]",MOD({0;0;0;0;0;1;1;1;1;1;1;1;1;1;1;2;2;2;2;2;2;2;2;2;2;3;3;3;3;3;3;3;3;3;3;4;4;4;4;4;4;4;4;4;4;5;5;5;5;5;5;5;5;5;5;6;6;6;6;6;6;6;6;6;6;7;7;7;7;7;7;7;7;7;7;8;8;8;8;8;8;8;8;8;8;9;9;9;9;9;9;9;9;9;9;10;10;10;10;10;10},2))
REPT("[half star]",{0;0;0;0;0;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;0})
Example B.3.2 is based on the same template as B.2.2 but includes the B.3.1 formula so it works with a different scale.
Part C
Just to prove you don’t have to use stars, this section contains examples with alternative symbols.
Examples C.1.1 to C.1.8 are based on the same formulas you saw in Part B.
Be careful when mixing symbols in the same cell. Some will display at different sizes when certain fonts are used. That’s why the first eight examples all use Segoe UI Emoji instead of Segoe MDL2 Assets — to keep them consistent.
Here’s what happens when you use the wrong font…
C.1.9 and C.1.10 differ slightly from the others. They use the font Segoe UI Symbol, and five numbered circles are stored in the LEFT function instead of REPT. The [num_chars] argument then returns a number to determine how many to extract from the string.
Extras
This worksheet contains the complete range of symbols used in Part B and Part C, along with their details. I’ve also included many star variations found in the Segoe MDL2 Assets character set, as well as the official Unicode ones.
There are a few things you might be wondering about…
How do I insert a symbol into text?
Select an empty cell and go to Insert (tab) > Symbol (button).
The Symbol window pops up. Choose the font and either click on the desired symbol or type the code (the number after U+) in the Character code field.
Press Insert to dump it into the cell.
Why are the half star symbols not visible?
Despite their prominence on high profile websites like Amazon, Google Maps, and Trustpilot, surprisingly few fonts support them.
In 2016, a 14-page proposal was submitted by Andrew West, who argued the case for the half star. Two years later, a batch of four variations was added to Unicode 11.0.
ℹ️
Unicode is a universal character encoding standard that covers almost every writing system worldwide, ensuring consistency and accurate representation.
I could only find three fonts these codes that support these codes, but they must be obtained from the web:
Since the launch of Windows 10 (2015), Segoe MDL2 Assets has been included as part of the operating system, enabling you to access it in Excel natively. However, like most of the font’s symbols, the half stars are not officially supported by Unicode as they are mapped to the Private Use Area (PUA).
Microsoft states: “The PUA allows font developers to assign private Unicode values to glyphs that don’t map to existing code points.”
The big advantage of Segoe MDL2 Assets though is compatibility; sharing a workbook with half stars won’t require further action from the recipient (in most cases).
If you’re using a macOS version of Excel, you’ll need to install Segoe MDL2 Assets as it’s not included.
ℹ️
For a guide on how to install fonts, check out How-To Geek.
Why are there duplicate stars?
Beware of identical-looking characters in Unicode. There are eight black stars in the Segoe MDL2 Assets set alone. Each has a separate code and exists for compatibility with legacy systems.
If you combine the wrong code with the wrong font, you’ll get unwanted results. For example, using U+E00A (black star) with Calibri returns a rectangle filled with a question mark.
Likewise, BabelStone Han produces an obscure symbol.
Some symbols are not featured in a font’s character set but still work because they have a discreet fallback font. That’s why certain variants of the star and other dingbats will still display when fonts like Calibri are used.
Final Words
You’ve now learnt how to create a star rating system!
I’m sure you’ll agree the modern methods are far more convenient than the traditional ones. Conditional formatting can be somewhat irritating to work with, especially when it occupies so many cells. That’s why a single formula is the way to go.
However, the biggest drawback is symbol support. Consideration must be given to which symbols are used and their respective fonts. Half stars were a challenge, and even after a long period of digging about for information, the consensus was they were not supported by any pre-installed font. Thankfully, that proved to be false, as I found the wonderful Segoe MDL2 Assets!
Despite barely any uptake thus far, we await the day the official Unicode half stars get widespread support.
