Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Change Chart Color Based on Value in Excel (2 Methods)

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.

excel chart color based on value


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

Using Vary Colors by Point Option in Excel

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

Using Vary Colors by Point Option in Excel

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

Applying IF and AND Functions to Change Chart Color Based on Value

  • 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,"")

Applying IF and AND Functions to Change Chart Color Based on Value

  • 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,"")

Applying IF and AND Functions to Change Chart Color Based on Value

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

Applying IF and AND Functions to Change Chart Color Based on Value

  • As a result, you will be able to create a column chart.

Applying IF and AND Functions to Change Chart Color Based on Value

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

Applying IF and AND Functions to Change Chart Color Based on Value

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

Create a Pie Chart Color Based on Value

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

Create a Pie Chart 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.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo