OmniDocConditional Formatting for Bar Charts in Excel
Cell conditional formatting doesn't reach chart bars. Learn series-based methods to color Excel bar charts by value, with steps and checks.
If you have a bar chart in Excel and want bars above a target to appear one color and bars below it another, you may have tried conditional formatting first. You select the chart, look for a rule that recolors the bars, and find nothing that works. This is expected: conditional formatting changes how cells look, and it does not reach the bars, columns or other parts of a chart.
The workaround is to build the color split into your data. Each color becomes its own series, and the chart colors each series separately, so the bars recolor when the numbers change. This article shows a two-series method for threshold colors, a quicker option for coloring bars by sign, and a static option for fixed reports. It also covers the checks that catch common mistakes.
If your numbers start in a PDF table, you can use OmniDoc's PDF editor to export them to Excel before you build the chart. The steps below assume you have a standard Excel workbook (.xlsx) to work in.
How do you get conditional formatting on an Excel bar chart? The short answer
Use two helper columns that split each value into its own series, then color each series. Conditional formatting alone will not recolor the bars.
| Your situation | What to do | Why |
|---|---|---|
| Bars need a color based on a threshold, such as a target | Split values into two helper columns with IF and NA(), chart both as column series, and set overlap to 100 | Each color is its own series, so the chart updates when the values change |
| You want a different color for negative values | Use Invert if negative on a single series and pick the inverted fill | This setting is built into bar and column series, with no helper columns |
| Colors are fixed for a one-time report | Select one bar and set its fill color by hand | It is quick, but the bar will not change if the data changes |
| The numbers are in a PDF table | Export the table to Excel with the PDF editor, then follow the steps below | It puts the data into a workbook you can chart |
Before you start
- Confirm you have a bar or column chart. The methods below work by splitting data into series, and both chart types support multiple series. Line charts need a different treatment, covered in the FAQ.
- Choose the threshold and store it in one cell. In this guide, the threshold goes in cell F1. Formulas refer to that cell, so you can move the split by changing one value.
- Make sure the values are numbers, not text. Text that looks like a number can make a comparison return the wrong branch, which leaves bars missing or in the wrong color.
- Compare scanned numbers with the source. If the table came from a scanned PDF, OCR can misread digits such as 3 and 8. Check the numbers against the original before you chart them.
Building the two-color bars in Excel, step by step
This example uses 12 months of sales in column B and a target of 100 in cell F1. Adjust the row numbers to match your data.
- Set up the labels and helper columns. Put the months in column A and the sales values in column B. In C1 type "At or above target" and in D1 type "Below target". In C2 enter =IF(B2>=$F$1,B2,NA()) and in D2 enter =IF(B2<$F$1,B2,NA()), then fill both formulas down to row 13.
- Understand why NA() is used. NA() returns #N/A, which a column chart skips, so no zero-height bar appears. Using 0 instead would leave a flat marker and a "0" label on the other series.
- Select the data for the chart. Select A1:A13, then hold Ctrl and select C1:D13. The labels become the categories, and the two helper columns become two series.
- Insert a clustered column chart. Use the chart option on the Insert tab. The chart should show two series, named after the headers you typed.
- Set the series overlap. Open the Format Data Series pane for either series and set Series Overlap to 100. This places both series in the same slot, so each month shows one bar. Adjust Gap Width if the bars look too thin or too wide.
- Color each series. Give the "At or above target" series one fill, such as green, and the "Below target" series another, such as red. Set the fill for the whole series, not for individual bars.
- Check the legend. Keep the legend so readers can tell the two colors apart, or add a title that explains the threshold.
- Test with a changed value. Change one number in column B to move it above and then below the threshold. The bar should move to the other series and change color.
A simpler split by sign. If you only need one color for positive values and another for negative values, use one series. Open the series formatting options, turn on Invert if negative, and choose the fill for negative values. No helper columns are needed.
Coloring bars by hand. You can select one bar twice, which selects only that point, and set its fill. This works for a report that will not change, but the bar keeps its color even if the value moves past the threshold.
Check the result
Once the chart is built, check these items before you share it.
| Check | How to see it | If it's wrong |
|---|---|---|
| Each category has one bar | Look at the chart. Both series should share the same slot for each month. | Set Series Overlap to 100 in the series options. |
| Colors follow the threshold | Change a value in column B so it crosses the threshold. | Check the IF formulas and confirm they point to $F$1. |
| No stray #N/A labels | Look for #N/A text on the data labels. | Remove data labels from the helper series, or show labels only on the source series. |
| Legend names are clear | Read the legend next to the chart. | Rename the header cells, then refresh the chart. |
| Values match the source | Compare the chart values with the original table or PDF. | Re-export the table or retype the values, then correct the helper formulas if needed. |
📊 At a glance
| Method | Updates when data changes | Good for | Setup effort |
|---|---|---|---|
| Threshold split with helper columns | Yes | Target or cutoff colors | Moderate |
| Invert if negative | Yes | Positive versus negative values | Low |
| Manually colored bars | No | One-time reports that won't change | Low, but manual |
❓ FAQ
1. Can I use three colors instead of two?
Yes. Add a third helper column, such as "Well above target", with its own IF condition. Set the overlap to 100 for all three series, then color each one. Each helper column should return NA() for values that belong to a different series.
2. Does the chart update when I add new months?
Yes, if you extend the helper formulas and the chart's source range to the new rows. If you convert the data to an Excel table before you build the chart, the chart usually grows as you add rows, but check the series range afterward.
3. Can I use this method with a line chart?
The same helper-column idea works, but line charts need more care. Where values cross the threshold, the line segments can look broken or join the wrong points, so check those points after you build the chart.
4. How do I get numbers from a PDF table into Excel?
Open the PDF in the OmniDoc desktop client, use the PDF editor to export it to Excel, and check the values against the original. If the PDF is scanned, run OCR first, because the exported numbers depend on how well the text was recognized.
📝 Summary
Conditional formatting in Excel changes cells, so a bar chart needs a different approach for color by value. Split your data into series with helper columns, set the overlap, and color each series, or use Invert if negative for a sign-based split. Then test the chart by changing a value, so your excel bar chart conditional formatting keeps working when the numbers change.
