Md. Abdur Rahim Rasel
The article will show you how to change the Chart color based on the value in Excel. The application of this can help you to visualize certain data conveniently. We use Columns and Pie Charts to demonstrate the comparison among several data and how they change over time. It’s sometimes difficult to identify information about a particular set of data from a casual Column and Pie Charts, even though we activate the Data Labels for that Column and Pie Charts. It’s better to vary the Column and Pie Charts color based on some values or formulas in the data. In that case, you can have a perfect visualization of your dataset. I’m going to show you the easiest possible ways to do that.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Ways to Change Chart Color Based on Value in Excel
In the dataset, we have secured marks in Physics for several students of XYZ school. Here’s an overview of the dataset for today’s task.
Method 1: Using Vary Colors by Point Option in Excel
If you don’t want to vary the color of the Column Chart on your own, you can change it automatically with Excel commands and applications. Let’s go through the process below.
Steps:
- First of all, select the range that you want to demonstrate in the Column Chart. From my dataset, I selected B6:C14. Hence, from your Insert tab, go to,
Insert → Charts → 2D Column Chart
- As a result, you will be able to create a column chart.
- After that, select any of the columns in the chart. Further, press the right-click on your Mouse. As a result, a window pops up. From that window, select Format Data Series.
- After that, the Format Data Series window will appear. Fix your Series Overlap and Gap Width according to your convenience and select the Fill Shape icon.
- Thereafter, check the Vary colors by point.
- Finally, you will be able to change the chart color based on the cell value which has been given in the below screenshot.
Read More: [Solved:] Vary Colors by Point Is Not Available in Excel
Method 2: Applying IF and AND Functions to Change Chart Color Based on Value
If you don’t want to use commands to change the color of your Column Chart, you can categorize the data in your dataset by IF and AND functions. For instance, I’ll show the series color as Yellow when the value range is 0 – 50, Green when it is 51 – 75, and Dark Orange when it is 76 – 100. Let’s follow the instructions below to learn!
Step 1:
- First of all, select cell D7, and write down the below IF and AND functions in that cell.
=IF(AND(D$4<=C7,$C7<=D$5),$C7,"")- Hence, simply press Enter on your keyboard. As a result, you will get the return of the IF and AND The return is 37.
- After that, AutoFill the IF and AND functions to the rest of the cells in column D.
- Similarly, write down the below functions in cell E7, and AutoFill the functions in column E.
=IF(AND(E$4<=C7,$C7<=E$5),$C7,"")
- Similarly, write down the below functions in cell E7, and AutoFill the functions in column F.
=IF(AND(E$4<=C7,$C7<=E$5),$C7,"")
Step 2:
- First of all, select the range that you want to demonstrate in the Column Chart. From my dataset, I selected B6:B14 and D6:F14. Hence, from your Insert tab, go to,
Insert → Charts → 2D Column Chart
- As a result, you will be able to create a column chart.
- Now, we will change the color of the column chart. First of all, I’ll change the series color to Yellow when the value range is 0 – 50. To do that, select the column of the Keat and press the right-click on that column. As a result, a window pops up. From that window, select Yellow color under the Fill color option.
- Similarly, change the color to Green when it is 51 – 75, and Dark Orange when it is 76 – 100.
- Finally, you will be able to change the chart color based on the cell value which has been given in the below screenshot.
Read More: How to Change Series Color in Excel Chart (5 Quick Ways)
Create a Pie Chart Color Based on Value
In this section, we will create a Pie chart to change color based on value. This is an easy task. This task is time-saving as well. Let’s follow the instructions below to learn!
Steps:
- First of all, select the range that you want to demonstrate in the Pie Chart. From my dataset, I selected B4:C12. Hence, from your Insert tab, go to,
Insert → Charts → 3D Pie Chart
- As a result, you will be able to create a Pie chart.
- Now, we will give the formatting of the Pie chart. First, give the title of that Pie chart. The title is “ Pie chart”. Hence, do like the below screenshot.
- After completing the above process, you will be able to create a Pie chart with changing color based on value.
Read More: How to Keep Excel Chart Colors Consistent (3 Simple Ways)
Conclusion
I hope all of the suitable steps mentioned above to change chart color based on value will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
