How to Create a Star Rating Template in Excel

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 REPT function. REPT Function is useful when you want to repeat a given text for a number of times.

Steps to Create a Star Rating Template

  1. Insert two different types of stars (☆★) in your worksheet and place them in two different cells.
  2. Insert five option buttons (Form Control) from developer tab. And, create a single cell link for all the buttons. Place them in five cells in a Vertical direction.
  3. Merge 5 cells, just one column before, where you have buttons.
  4. Enter ❝=REPT(H11,5-K11)&REPT(I11,K11)❞ in the merged cell.
  5. Change the text orientation of the cell to the vertical text and then text alignment to the center and bottom.

Your template is ready.

star rating template in excel

How it Works

The first part of the formula will give you outlined stars. For Example, when you click on 3-star rating it will give rest of the two stars in 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.

Quick Tip: You can also insert symbols within the formula itself or you can also use another type of symbols as you like.

Related Function

  1. Excel REPT Function

Related Formulas

  1. In-Cell Bar Chart with REPT Function
  2. In-Cell Column Chart with REPT Function
  3. How to Apply Strikethrough in Excel
  4. The Best 5 Way To Add Leading Zeros in Excel
  5. 5-Popular Ways to Concatenate a Range of Cells in Excel (Array)

Content Protection by


  1. Santosh Kumar Pandit 4 Aug, 18 at 10:00 am - Reply

    Thanks Puneet,

  2. Emily T 12 Sep, 17 at 4:28 am - Reply

    Also, how did you get cell H1 to populate in your sample file as there’s no visible formula in it? 🙂 thank you

  3. Emily T 12 Sep, 17 at 4:26 am - Reply

    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 🙂

  4. Gary Evans 11 Sep, 17 at 11:31 am - Reply

    Insert two different types of stars (☆★) in your worksheet is a bit glib. How do I do this?

    • Puneet Gogia 13 Sep, 17 at 12:46 pm - Reply

      Just copy paste these stars into your workbook in two different cells

  5. Ankur Shukla 1 Mar, 16 at 11:39 am - Reply

    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..

    • Puneet Gogia 1 Mar, 16 at 11:51 am - Reply

      Thanks Ankur.

      I am working on a basic VBA E-Book. Will Share With You Soon.

Leave A Comment