Change column/bar color in charts
This article demonstrates how to set up a chart so it shows one color for increasing bars/columns and another color for decreasing bars/columns.
The image above shows a chart with blue and red columns, the blue columns are increasing based on the previous column and the red columns are decreasing meaning they are smaller than the previous column.
Hello,
I’m new working with dynamic charts using Excel 2007. I created a dynamic bar chart using 2 series of yearly sales. I have defined range names FW for series 1 and SS for Series 2. I would like to display each respective bar from each series in blue each time sales increase from the previous year and in red each time the sales decrease from previous year.
How would I go about doing this?
Thanks.
Peter
The image below shows the data behind the chart above, two formulas are used to separate decreasing and increasing values from the original source in cell range B3:C12.
It is important that increasing values are in one column and decreasing values are in another column. This makes it easier to separate the values into different chart series.
Increasing values are extracted with the following formula in cell E3:
Decreasing values are extracted with the following formula in cell F3:
Copy cell E3 and paste to cells below as far as needed, repeat with cell F3.
Explaining formula in cell E4
Step 1 - Check if above cell is text
The ISTEXT function will tell us if the cell is the first one in the column. We want to avoid comparing a number with text.
IF(ISTEXT(C3),C4,IF(C4>C3,C4,""))
becomes
IF(FALSE,C4,IF(C4>C3,C4,""))
Step 2 - Compare number with cell above
The IF function allows you to return a value if the logical expression evaluates to TRUE and another if FALSE.
IF(FALSE,C4,IF(C4>C3,C4,""))
becomes
IF(FALSE,C4,IF(39>25,C4,""))
becomes
IF(FALSE,C4,IF(TRUE,C4,""))
and returns 39 in cell E4.
Setting up the chart
We now have data in separate columns, this way we can use two data series to color bars/columns differently.
- Select cell range E3:F12.
- Go to tab "Insert" on the ribbon.
- Press with left mouse button on "Insert column or bar chart" button.
- Press with mouse on "Clustered column" button.
- Press with right mouse button on on chart.
- Press with left mouse button on "Select Data...".
- Press with left mouse button on "Edit" button.
- Select cell range E3:E12.
- Press with left mouse button on OK button.
- Press with left mouse button on the other "Edit" button.
- Select cell range B3:B12.
- Press with left mouse button on OK button.
- Press with left mouse button on the "Add" button.
- Select cell range F3:F12.
- Press with left mouse button on OK button.
- Press with left mouse button on OK button.
Charts category
Table of Contents How to add lines between stacked columns/bars (Excel charts) Use slicers to quickly filter chart data How […]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
Excel categories
One Response to “Change column/bar color in charts”
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.
Hi Oscar,
I'm wondering if this approach could be modified to provide "consistent" formatting between charts?
My situation is that I have two charts forecasting staff and workforce levels by month. Source data shows the forecast levels for actual work (current projects and head office) and tenders, with two similar tables i.e. one for staff, one for workforce, each driving its respective chart. The data is organised with each row representing one job or tender, and the figures by month spread across by columns...
To help highlight the "current work" vs "tender" split, I (so far) manually adjust the fill transparency of the tender forecasts to 20% and leave the current work at 0%. Problem is, with two charts I then have to flick back and forth between charts to provide consistency between the same job or tender (i.e. if Job A uses "turquoise" on the staff chart, I want it to use the same colour on the workforce chart, if Tender Y has "tan 20%" on staff it should be the same on the workforce chart, and so on).
Could this be automated or assigned against the tables? Note that this is a monthly report so the data may change i.e. in terms of the listed projects and tenders, but the range of the report remains steady (36 mths).
Thanks,
Mark.