Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to show rating stars in a cell in Excel and Google Sheets
Excel & Google Sheets
=REPT("★", MIN(5, MAX(0, ROUND(A2, 0)))) & REPT("☆", 5 - MIN(5, MAX(0, ROUND(A2, 0))))✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Rating | Stars |
| 4.7 | |
| 2.3 | |
| 5 | |
| 0 | |
| 3.5 |
=REPT("★", MIN(5, MAX(0, ROUND(A2, 0)))) & REPT("☆", 5 - MIN(5, MAX(0, ROUND(A2, 0))))→★★★★★
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter your rating values (0-5) in column A starting from A2.
- 2In B2, enter the formula provided for Excel or Google Sheets.
- 3Drag the fill handle down to apply the formula to other cells in column B.
Tips
- Ensure your rating values are between 0 and 5 to avoid unexpected results.
- Adjust the formula if you need more or fewer stars by changing the number 5 in the formula.
- This method uses whole numbers for ratings; modify the ROUND function if you want to include half-star ratings.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Can I use this method for half-star ratings?
No, this method uses whole stars. For half-star ratings, you would need a more complex formula or a different approach.
What if my ratings are not integers?
The ROUND function in the formula rounds the rating to the nearest integer. You can change it to FLOOR or CEILING if you prefer to always round down or up.
How do I change the color of the stars?
Excel does not support colored text in formulas directly. In Google Sheets, you can manually format the text after applying the formula.