Color chart bars by value
Excel has a built-in feature that allows you to color negative bars differently than positive values. You can even pick colors.
(Chart data is made-up)
Instructions
All columns in this chart are blue.
- Right click on a column
- Click on "Format Data Series..."
-
Click "Fill" button
- Click on "Solid fill"
- Click "Invert if negative"
- Pick a color for positive values
- Pick a color for negative values
Color chart columns by a condition
What if you want to color bars by comparing?
This chart example shows bars colored differently depending on preceding value. If a value is larger than the previous one, it will be colored blue. Smaller than the previous value and the bar will be red.
How to build
The trick here is to split data into two different chart series, you can do that by placing them in two columns using formulas.
Formula in cell B2: =IF(A3>A2,A3,"")
Formula in cell C2: =IF(A3<A2,A3,"")
Copy these cells and paste them on cells below, as far as needed.
Now it is time to build the column chart, shown above.
- Select values in in column A
- Go to tab "Insert" on the ribbon
- Click on "Insert column chart" button
Add data series
-
Right click on columns and click "Select Data..."
-
Click "Edit" button below "Legend Entries (Series)"
- Click "Series values" button and select cell range B2:B33
- Click OK
- Click "Add" button
- Select cell range C2:C33
- Click OK
The chart changes to this:
You can see that there are gaps between series.
- Right click on a column
-
Click "Format Data Series..."
-
Change "Series Overlap" to 100%
This is what the chart looks like:
Download excel 2013 *.xlsx file
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: [โฆ]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above [โฆ]
How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option [โฆ]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and [โฆ]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional [โฆ]
Format fill color on a column chart based on cell color
Table of contents Format fill color on a column chart based on cell color Change stacked bar colors Mark Graveson [โฆ]
Rearrange data source in order to create a dynamic chart
Fatou asks: Going back to my question, I had created a table and used the data to create a chart. [โฆ]
You can easliy change data labels in a chart. Select a single data label and enter a reference to a [โฆ]
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps [โฆ]
Use drop down lists to compare data series in an excel chart
The two drop down lists below the chart letยดs you compare two data series from separate tables. It is easier [โฆ]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
