Here is a quick tip for you to create a star rating template in Excel. To create this star rating template all you need is the REPT function. REPT Function is useful when you want to repeat a given text a number of times.
Steps to Create a Star Rating Template
- Insert two different types of stars(☆★) in your worksheet and place them in two different cells.
- Insert five option buttons (Form Control) from the developer tab. And, create a single-cell link for all the buttons. Place them in five cells in a Vertical direction.
- Merge 5 cells, just one column before, where you have buttons.
- Enter “=REPT(H11,5-K11)&REPT(I11,K11)” in the merged cell.
- Change the text orientation of the cell to vertical text and then text alignment to the center and bottom.
Your template is ready.
How it Works
The first part of the formula will give you outlined stars. For Example, when you click on a 3-star rating it will give the rest of the two stars in the outline.
The second part of the formula will give you filled stars. For example, when you click on the 4-star rating you will get 4 filled stars.
You can also insert symbols within the formula itself or you can also use other types of symbols as you like.
More Formulas
- Capitalize First Letter in Excel
- Change Column to Row (and Vice Versa) in Excel
- Change to Sentence Case in Excel
- Create a Horizontal Filter in Excel
- Get File Name in Excel
- Get Sheet Name in Excel
- Generate Random Letters in Excel
- Randomize a List (Random Sort) in Excel
- Count Characters in Excel (Cell and Range)
- Flip the First & Last Names and Add a Comma Between
- Get File Path (Excel Formula)
- Get the Value from a Cell
Hi Puneet! This is helpful! I understand that this is for a template, when I try to copy the radio buttons on other columns(or rows– i tried to do it on a horizontal as I am creating a To-Read list with a star-rate feature), it’s not working. I believe it has something to do with Cell Link in Control settings of the button. Can you please clarify, what’s with the Cell Link? thank you!
There is a problem, that How to convert the star to tex?
Dear Puneet, thank you for your sharing of this tip. Could you please edit the steps to give details which feature is used to get the 2 different type of stars here and how to create a single cell link for all the buttons? Thanks
Would you consider any further explanation for this great lesson ? Seem to be missing something on the fonts for the ratings cells and getting only a single star to show instead of several. Thank you for all you do for us.
I really look forward to your daily posts Puneet. Thank you for your dedication and time you must spend on creating these extremely useful Excel tips! On a number of occasions the content of your email has been relevant to a project I have been working on and so helpful.
You always leave me mesmerized with the awesome knowledge you share. Thanks BRO!!
Thanks Puneet,
Also, how did you get cell H1 to populate in your sample file as there’s no visible formula in it? 🙂 thank you
Dear Puneet, thank you for your sharing of this tip. Could you please edit the steps to give details which feature is used to get the 2 different type of stars here and how to create a single cell link for all the buttons? Thanks 🙂
Insert two different types of stars (☆★) in your worksheet is a bit glib. How do I do this?
Just copy paste these stars into your workbook in two different cells
Awesome 🙂 Puneet can you please make post for VBA basic ??? It would be good. Many of FB excel group members are looking for the same..
Thanks Ankur.
I am working on a basic VBA E-Book. Will Share With You Soon.