How to make an EASY color-changing rating chart in Microsoft Excel, no Visual Basic for Applications
To make an easy 5-star rating chart with changing colors in Microsoft Excel without using Visual Basic for Applications you will need to add additional columns with formulas to the score table, so that for each score only one column displays the achieved score based on meeting a condition. Other columns should not show any value. One more column is necessary as the background and its values should all be equal to the maximum possible score. You will also need a star shape. Add a clustered bar chart (or clustered column chart depending on the direction that you want). Add one series for the background and one series for each color. Fix the bounds of the value axis (my score ranged from 0 to 100). Copy the shape and paste it onto the background series (shape fill property of Microsoft Excel). In the Format Data Series pane (opens by double clicking on the chart series) under Fill select Stack and Scale with and specify 20 units per picture to show 5 stars (because the maximum score is 100). Change the color of the star and repeat the process for each color series. In the Format Data Series pane set series overlap to 100% and the gap width to 0. An adjust the chart size.
0:00 Introduction
0:27 Prepare the table with additional columns
1:00 Apply formulas with conditions to the columns
1:47 Add a chart
2:44 Change the chart to clustered bar
2:58 Fix the bounds of the value axis
3:10 Apply shape fill to the background series
3:56 Stack and scale with
4:15 Apply formatting to the color series
5:52 Adjust series overlap and gap width
6:35 Test the solution
For more contents like this, please subscribe to my channel.
#MsExcel #ExcelTips
Screenshots used with permission from Microsoft.