Create Data Bars and Star Rating KPIs in Excel

​You can easily get data bars and star ratings added to your report with a few steps. Often a way to save real estate and indicate differences, people use the spark line visuals. However, this can only be effective if you are dealing with time series data. So, this tutorial is going to show a quick and easy way.

​Add Data Data Bars to Your Table Report

​We are going to be utilizing the REPT function which will allow us to repeat a specified text based on a column value. The below shows so example data of concert tickets and ratings for those concerts.

1. ​​Add REPT function to add "|" that will repeat for number in B2. As: =REPT("|",B2)
2.  Drag the formula down to B8 or end of the range.
   
3. Change the font to Playbill
   

​Switch your font to Playbill.

​You can easily add conditional formatting with a rule to change the colors of the data bars. You can see the video.

​Add Stars or other Symbols to Table as KPIs

​We will be following a similar format the symbols. However, symbols take up a lot of real estate so, we are going to be using a smaller number to represent these. So the concert ratings will be a value used to create the symbol. 

1. Click Insert from the top ribbon
   
2. Choose the star symbol from wingdings
   
3.  Insert that into cell and copy it to the clipboard
   
4. Enter the REPT function next to your first value in this case F2, You sill see "<<" representing your winding symbol So your formulas should look as.  =REPT("​<<",F2)
   
5. ​Drag the formula down and change your column font to windings.
   

Now you can change the column font to windings and font color to get the star.

​Switch Symbols with IF

​You can easily switch between symbols by utilizng an IF statement with the the REPT functions. See the example formula below:, The "J" and "M" are standins for the windings symbols. 

=IF(J2>5,REPT("J",J2),REPT("M",J2))​

​Then make the final product sexy by removing the cell borders and grid lines and merge the headings. ​