Creating an Excel five star rating chart is easy with Conditional Formatting. They’re not limited to stars, you can also create column, pie and waffle rating charts.
Constructing an Excel Five Star Rating Chart
Step 1: In the top left cell of your table, in my case cell C5, insert the formula below that calculates the portion of star for each column; 1 through 5:
Tip: If you’re using Office 365* you can use the following Dynamic Array formula instead, which means you don't need the numbers 1-5 in row 4:
More on the SEQUENCE function here.
*At the time of writing, Dynamic Arrays are only available in Office 365 and are currently in beta on the Insiders channel. Excel 2019 will not have the Dynamic Array functions.
Step 2: Copy the formula in C5 to all cells in the table, i.e. C5:G10. It returns the following results:
Step 3: Set the Conditional Formatting to apply a whole star to any value that is greater than or equal to 1.
Make the following selections in the Conditional Formatting dialog box:
Tip: If you don’t want the empty star to show you can set the < 0.5 star to ‘No Cell Icon’:
And now you have an Excel five star rating chart:
Tip: Set the cell font size to alter the size of the stars to suit and hide the 1 – 5 numbers in C4:G4 by formatting them in a white font.
Other Excel Rating Charts
You’re not limited to star ratings. Conditional Formatting icons also have pies, column charts and waffle style charts:
Examples of the waffle and column charts are below and included in the Excel file for download below:
Enter your email address below to download the sample workbook.
If you liked this please share this tutorial with your friends and colleagues.