How to Create Star Ratings in Google Sheets Using the REPT Function

Visualising review scores, customer satisfaction surveys, and priority rankings directly within spreadsheet rows can significantly improve reporting clarity. While numeric values like 4 or 5 communicate raw metrics, converting those figures into dynamic star ratings offers an immediate visual cue that stakeholders and team members can grasp at a glance. In Google Sheets, you can achieve this effect natively without installing third-party add-ons or running scripts by combining the REPT text repetition function with standard Unicode star symbols.

This guide explains how to generate single-character star ratings, construct five-star comparative gauges with unfilled stars, handle fractional ratings with rounding logic, and dynamically style the output using conditional formatting.

Understanding the REPT Function Mechanics

The REPT function in Google Sheets repeats an assigned text string a designated number of times. Its syntax is straightforward:

=REPT(text, number_of_times)

• text: The specific string, symbol, or cell reference you wish to repeat.
• number_of_times: A positive integer specifying how many repetitions to display. If this argument contains a decimal, Google Sheets automatically truncates it to the nearest integer.

By supplying a solid Unicode star character—such as ★ (Unicode U+2605)—as the text string and referencing a cell that holds an integer score between 1 and 5, REPT immediately prints that exact number of stars across the cell.

Method 1: Generating Basic Solid Star Ratings

If your dataset contains integer scores and you solely need to show the positive rating count, follow these steps:

1. Open your spreadsheet in Google Sheets.
2. Locate your rating data. For this example, assume customer review scores from 1 to 5 sit in column B, starting at cell B2.
3. Click into an empty adjacent cell where you want the visual stars to appear (such as C2).
4. Enter the following formula:

=REPT("★", B2)

5. Press Enter. If cell B2 contains 4, cell C2 will display ★★★★.
6. Click the small blue fill handle in the lower-right corner of cell C2 and drag it downwards to populate the remainder of your column.

Method 2: Building Fixed 5-Star Gauges with Empty Stars

Displaying solid stars alone can sometimes make it difficult to ascertain the maximum scale, particularly when comparing low scores. Constructing a normalised five-star gauge using both solid stars (★) and hollow outline stars (☆, Unicode U+2606) establishes a constant visual width of five characters across every record.

1. Select your target output cell (for example, C2).
2. Enter the following concatenated formula:

=REPT("★", B2) & REPT("☆", 5 - B2)

3. Press Enter.

Here is how the formula evaluates across sample values:

• If B2 is 5: REPT("★", 5) & REPT("☆", 0) returns ★★★★★.
• If B2 is 3: REPT("★", 3) & REPT("☆", 2) returns ★★★☆☆.
• If B2 is 1: REPT("★", 1) & REPT("☆", 4) returns ★☆☆☆☆.

This creates a balanced, uniform scorecard that mirrors standard e-commerce rating displays.

Handling Decimal Ratings with the ROUND Function

Real-world feedback metrics frequently produce decimal averages, such as 4.3 or 3.7. Because REPT truncates non-integer values rather than rounding them (turning 3.9 into 3 stars), unrounded formulas misrepresent performance scores. To ensure fair visual representation, wrap your numerical cell reference in the ROUND function:

=REPT("★", ROUND(B2, 0)) & REPT("☆", 5 - ROUND(B2, 0))

For datasets containing potential outliers, null values, or blank submissions, protect the formula against errors using IF and MEDIAN:

=IF(ISBLANK(B2), "", REPT("★", ROUND(MEDIAN(0, 5, B2), 0)) & REPT("☆", 5 - ROUND(MEDIAN(0, 5, B2), 0)))

The MEDIAN(0, 5, B2) expression clamps input values between 0 and 5, preventing negative numbers from generating a #VALUE! error and stopping numbers higher than 5 from printing excessive stars.

Applying Conditional Formatting and Custom Colours

By default, Unicode star symbols render in your sheet’s primary text colour (typically black). You can elevate your dashboard by applying a rich gold or amber colour palette, or dynamically shifting colours based on performance bands.

Setting a Universal Golden Star Theme

1. Highlight your entire column of star ratings (e.g. column C).
2. Navigate to the top toolbar and click the Text colour icon (the letter A with an underline).
3. Select an amber or gold swatch (such as Hex #F59E0B or #D97706).
4. Optionally, set the font weight to Bold to enhance star visibility.

Applying Dynamic Colour Coding Based on Satisfaction Scores

To highlight poor scores in red and top scores in green, configure conditional formatting rules tied to the underlying numerical column:

1. Highlight your star rating cells (C2:C100).
2. Click Format in the main menu and select Conditional formatting.
3. Under the Format rules section, open the Format cells if… dropdown and choose Custom formula is.
4. To highlight top-tier ratings (4.5 and above) in emerald green, enter:

   =$B2>=4.5

5. Set the formatting style text colour to green and click Done.
6. Click Add another rule. Set the custom formula to identify underperforming scores:

   =$B2<2.5

7. Set the formatting style text colour to crimson red and click Done.

Quick Reference Comparison Table

Objective	Formula Syntax	Sample Output (Score: 3)
Solid Stars Only	=REPT("★", B2)	★★★
5-Star Fixed Gauge	=REPT("★", B2) & REPT("☆", 5 - B2)	★★★☆☆
Rounded Decimal Gauge	=REPT("★", ROUND(B2, 0)) & REPT("☆", 5 - ROUND(B2, 0))	★★★☆☆ (Score: 3.2)
Clamped & Error-Proof	=IF(B2="", "", REPT("★", ROUND(MEDIAN(0,5,B2),0)) & REPT("☆", 5-ROUND(MEDIAN(0,5,B2),0)))	★★★☆☆

Troubleshooting Common Formatting Quirks

• Formula returns #VALUE! error: Ensure that the rating column contains standard numeric values and not text strings with leading spaces. Also ensure the score is non-negative, as REPT cannot process negative repeat counts.
• Stars appear misaligned or clipping: Some custom display fonts display uneven widths between solid ★ and hollow ☆ glyphs. Switching your cell font to standard system typefaces like Arial, Roboto, or Trebuchet MS ensures pixel-perfect glyph alignment.
• Extra stars appear beyond the 5-star boundary: Verify whether input data contains percentages or ratings evaluated on a 10-point scale. If your ratings are scored out of 10, normalise the calculation by dividing the value by 2: ROUND(B2 / 2, 0).