There are times when we want specific colors for bars or column in Excel charts to represent certain values or categories. Unfortunately, this isn’t a default feature, and it necessitates manually coloring each bar or series, which doesn’t automatically adjust when the data is altered. However, there are ways to achieve that with a little bit of creativity. In this post, I’ll share three practical scenarios examples of how to conditionally format chart colors so they automatically update in response to data based on our preset conditions.
EXAMPLE 1
Scenario: We have a dataset of daily check measurements of something produced by 15 different machines. We want to chart a bar/column chart to show which measurements are: Accept, Reject (unacceptable) indicated by their colors (based on their values in the dataset per machine).
If the value is within 15 units from the Accept value, it is acceptable. We want to color-code those as green. Anything that deviates from than 15 units from the Accept value, we classify it as Reject. We want to color-code those as yellow (if lower than the lower limitL Too Low), or red (if greater than the higher limit: Too High).
The goal is to create a chart as below where the bars are automatically colored according to the values they represent. When the values change in the dataset, the bars will automatically color themselves according to our conditions set.
Solution:
The trick is to have separate series for each condition, so when charted, each series will be colored independently. Of the series added (which is really grouping), only one and exactly one group will have a numeric value, and blank or #NA for the others per row (depending on the formula used). Then we overlap the series at 100% (there will be only 1 bar per x-value).
STEPS:
- Set up the boundaries or limits for the values.
e.g. If we want up to 3 different colors of chart based on their values, then we need to define 3 limit values (e.g. upper limit, lower limit, etc. depending on our logic).
In this example, we’ll set 3 different limit values for : Accept, Reject (Too High), Reject (Too Low) Accept (Excellent): 450
Margin of error: 15
Accept UpperLimit: 450+15 [margin of error or deviation]=465
Accept LowerLimit: 450-15=435
Reject (Too Low): <435 Reject (Too High): >465 - Reshape the data source by adding additional groups where we’ll enter IF() formulas.
If we have 3 groups, we will need 3 new columns (with the formulas, which will be dragged down to the required # of rows).
Each of these columns will be a data series for the chart, and the first column will be the category label (e.g. Machine # or name along x-axis).
The new dataset will be reshaped to:
Machine | Accept | Reject:Too Low | Reject: Too High
- Enter formula for each column.
Then drag it down to as many rows needed. (Be sure to anchor properly!) Accept column formula:
=IF(AND($B12 <=$B$31,$B12>=$B$32), $B12, “”) [NOTE: the last argument says, blank if criteria is not met. We could also use NA() function as that argument in the formula)
Where B12 has the value to be evaluated. B31 is the Accept-Upper Limit value, and B32 is the Accept-Lower Limit value. Reject: Too Low column formula:
=IF($B12 < $B$32, $B12, “”)
Where B12 has the value to be evaluated. B32 is the Accept-Lower Limit value. Reject:Too High column formula:
=IF($B12 > $B$31, $B12, “”)
Where B12 has the value to be evaluated. B31 is the Accept-Upper Limit value. - Next, create a Clustered Column chart (or Clustered Bar chart). Select the reshaped dataset (including headers and all the new conditional fiormulated columns). Insert->All Charts: Column: Clustered Column chart. Then click on any of the bar on the chart->Format Data Series (ctrl-1), set Series Overlap=100%. Without having the series overlap to 100%, we get multiple bars/columns side-by-side for each machine. Now we have each series bars/columns colored automatically to a distinct color. All is left to do now is to change the colors to our preference for each group/series. To do so: Click on any series to selec all its series bars, Ctrl-1 (to open Format Data Series) and from Fill&Line tab, customize the color. Repeat for each series. To reduce the gap in-between series’, click on any of the bar on the chart->Format Data Series (ctrl-1), set Gap Width=20%. So from now on, if the values change, the bar colors will change accordingly.
The dataset layout and the limit parameters may look like this (steps 1,2,3):
Each row has just one numeric data and in only 1 column of the classification/groups: Accept or Reject (Too Low) or Reject (Too High)…and everything else in the row is blank. As long as there is one and ONLY one numeric value in each row (in ONLY 1 column), we have the conditions correct.
After step 4, the final chart looks like this…exactly what we wanted:
We can now clearly see which machines are producing acceptable outputs and which are not. We can verify the chart updates correctly by changing the values, and watch the bar magically change to appropriate colors. Also, we see (Right-click on chart->Select Data), that in the chart, we now have 3 series: Accept, Reject: Too Low, Reject: Too High (plus one for the x-axis labels: Machine) as shown below:
EXAMPLE 2
Scenario: We have a dataset of Actual vs Budget values for several projects. We want to show the deviation from Budget. If the difference is positive, I want to show those in a certain color, and if negative, show them as a different color.
Solution:
In this case, we won’t need to add conditional formulas at all. All we need is a setting in Excel that allows for [x] Invert if Negative in Format Data Series (for bar/chart columns)…which enables us to choose colors for Positive and Negative independently without formulas. This is the the easiest of the scenarios presented here.
STEPS:
- First, calculate the difference (Actual-Budget) for each project. This is the new Delta column as shown below.
2. Chart a clustered Bar chart: Select the Project names coumn, press-ctrl-key and select the Delta column, Insert->Clustered Bar chart. The first chart looks like this:
3. Click on a bar on chart, ctrl-1 (right-click->Format Data Series), and in Fill & Line tab, under Fill select [x] Invert if negative. The chart now looks like this:
- Fine-tune the look.
Change the gap width for the bars to smaller % (e.g. 80%)
Add Data Labels to Outside End.
Remove the x-axis labels, and vertical grid lines in plot area.
Bold the y-axis label and increase the font (Project names).
Optional: Make the outline of the chart rounded.
Click to select the chart, ctrl-1 (opens Format Chart Area pane), there at bottom select [x] Rounded corners. The chart now looks like this:
Notice that the order of project is reversed from the dataset…it shows project E first, and A last. To fix this, we need to reverse its order.
- Fix the order of projects.
Click on the y-axis label. Ctrl-1 to bring up Format Axis pane. There select [x] Categories in reverse order. The final chart looks like this:
It’s easy to see which projects ran over budget, came in under budget, or at budget. And as the numbers change or projects change, the chart automatically updates with the correct color codes.
EXAMPLE 3
Scenario: We want to plot a bar/column chart, but want to hilighlight MAX and MIN values as separate colors (automatically updated when values change). We’ll use the same dataset (same as in EXAMPLE 1).
Solution:
As in EXAMPLE 1, the trick is to have separate series for each condition: MAX and MIN and then add those series into the chart, so each series will be colored independently.
Of the series added (which is really grouping), only one and exactly one group will have a numeric value, and blank for the others per row. Then we overlap the series at 100% for each group, there’s only 1 bar per x-values.
STEPS:
- Enter the formulas for the conditions (Max, Min). The range looks like this:
We can see that the 2 new series (Min, Max) have exactly one value in each column for two specific Machine’s measurements.
2. Chart a Clustered Bar (or Clustered Column) chart by selecting all the data range + the Min, Max column range. The chart looks like this:
3. Change the Series overlap = 100%.
- Fine-tune the look.
Select a bar, ctrl-1 to Format Data Series. Then adjust Gap Width=80%, and colors for Min, Max series.
Notice that the order of Machines is reversed from the dataset…it shows Machine 5 first, and 1 last. To fix this, we need to reverse its order.
- Fix the order of Machines.
Click on the y-axis label. Ctrl-1 to bring up Format Axis pane. There select [x] Categories in reverse order. The final chart looks like this:
And there we have it. All done! I hope this was informative for you, thanks for reading.
Related:
