Change chart color based on value in Excel

Sometimes, when you insert a chart, you may want to show different value ranges as different colors in the chart. For example, when the value range is 0-60, show series color as blue, if 71-80 then show grey, if 81-90 show color as yellow and so on as below screenshot shown. Now this tutorial will introduce the ways for you to change chart color based on value in Excel.
doc change chart color by value 1

Change column/bar chart color based on value
Method 1:Change bar char color based on value by using formulas and built-in chart feature
Method 2:Change bar char color based on value by using a handy tool

Change line chart color based on value

Download sample file


Change column/bar chart color based on value

Method 1 Change bar char color based on value by using formulas and built-in chart feature

Firstly, you need to create the data as below screenshot shown, list each value range, and then next to the data, insert the value range as column headers.
doc change chart color by value 2

1. In cell C5, type this formula

=IF(AND(C$1<=B5,$B5<=C$2),$B5,"")

Then drag fill handle down to fill cells, then continue dragging the handle right.
doc change chart color by value 3

2. Then select the Column Name, hold Ctrl key, select the formula cells including value range headers.
doc change chart color by value 4

3. click Insert > Insert Column or Bar Chart, select Clustered Column or Cluster Bar as you need.
doc change chart color by value 5

Then the chart has been inserted, and the chart colors are different based on the value.
doc change chart color by value 6

Method 2 Change chart color based on value by using a handy tool

Sometimes, using the formula to create a chart may cause some errors while the formulas are wrong or deleted. Now the Change chart color by value tool of Kutools for Excel can help you.

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 

After free installing Kutools for Excel, please do as below:

1. Click Kutools > Charts > Change chart color by value. See screenshot:
doc change chart color by value 7

2. In the popping dialog, do as these:

1) Select the chart type you want, then select the axis labels and series values separately except column headers.
doc change chart color by value 8

2) Then click Add button doc add button to add a value range as you need.
doc change chart color by value 9

3) Repeat above step to add all value ranges to the Group list. Then click Ok.
doc change chart color by value 10
doc change chart color by value 11

Tip:

1. You can double click the column or bar to display the Format Data Point pane to change the color.

2. If there has been inserted a column or bar chart before, you can apply this tool - Color Chart by Value to change the color of chart based on value.

Select the bar chart or column chart, then click Kutools > Charts > Color Chart by Value. Then in the popped-out dialog, set the value range and the relative color as you need. Click to free download now!
color chart by value


Change line chart color based on value

If you want to insert a line chart with different colors based on values, you need another formula.

Firstly, you need to create the data as below screenshot shown, list each value range, and then next to the data, insert the value range as column headers.

Note: the series value must be sorted from A to Z.
doc change chart color by value 12

1. In cell C5, type this formula

=IF(AND(C$1<=$B5,$B5<=C$2),$B5,NA())

Then drag fill handle down to fill cells, then continue dragging the handle right.
doc change chart color by value 13

3. Select the data range including the value range headers and the formula cells, see screenshot:
doc change chart color by value 14

4. click Insert > Insert Line or Area Chart, select Line type.
doc change chart color by value 15

Now the line chart has been created with different color lines by values.
doc change chart color by value 16


Download sample file

Click to download sample file


Other Operations (Articles) Related To Chart

Dynamic highlight data point on Excel chart
If a chart with multiple series and a lot of data plotted on it, which will be difficult to read or find only relevant data in one series you use.

Create an interactive chart with series-selection checkbox in Excel
In Excel, we usually insert a chart for better displaying data, sometimes, the chart with more than one series selections. In this case, you may want to show the series by checking the checkboxes.

Conditional formatting stacked bar chart in Excel
This tutorial, it introduces how to create conditional formatting stacked bar chart as below screenshot shown step by step in Excel.

Creating an actual vs budget chart in Excel step by step
This tutorial, it introduces how to create conditional formatting stacked bar chart as below screenshot shown step by step in Excel.



  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Rajavelu · 1 years ago
    Dear sir,
    Here I have the daily workers efficiency. Now I need the column chart in a conditional formatting method. I want the graph in a different colors as per the average efficiency. Can you give me the solution?
  • To post as a guest, your comment is unpublished.
    Lewis · 1 years ago
    Is there anyway I can do this to an already existing graph that a colleague made? For example, I'm tracking how productive people are and want anything 95%-105% to be green and anything else to be red; at the moment, anything 100%+ is green and anything below is red.
  • To post as a guest, your comment is unpublished.
    BSA · 1 years ago
    In the "change bar color based on value example", consider using the stacked bar chart as all the columns then will be centred on the x axis label.
  • To post as a guest, your comment is unpublished.
    TK · 2 years ago
    how do I remove the zeroes at the ends of the bars when I choose "show values" for the bars?
  • To post as a guest, your comment is unpublished.
    John Field · 3 years ago
    Re "Change bar chart color based on value" many thanks - I suggest using stacked bars or columns not clustered, then all the bars come evenly spaced even when they have different colors.
  • To post as a guest, your comment is unpublished.
    Dave · 3 years ago
    Extremely helpful, but I'm struggling to get it to work where there is just one data point that should be a different colour. Can you help?
  • To post as a guest, your comment is unpublished.
    Sourabh Jain · 3 years ago
    Greetings

    This does not seem to work in Excel 2016 for Mac. All I get are Colored Horizontal Lines for each X Axis Observation instead of a Multicolored Line Chart. Please help.
  • To post as a guest, your comment is unpublished.
    raj · 4 years ago
    Very helpful. Thank you. But have one query - How do you customise the colours ?
  • To post as a guest, your comment is unpublished.
    Go home · 5 years ago
    Go home
    Go home
    Go home