---

Often you may want to apply conditional formatting to bars in a bar chart in Excel.

For example, you might want to use the following logic to determine the colors of bars:

• If the value is less than 20, make the bar color red.
• If the value is between 20 and 30, make the bar color yellow.
• If the value is greater than 30, make the bar color green.

The following step-by-step example shows how to use this logic to create the following bar chart in Excel:

Let’s jump in!

Step 1: Enter the Data

First, let’s create the following dataset that shows the total sales made during each month of the year by some company:

Step 2: Format the Data

Suppose we would like to create a bar chart to visualize the sales each month and use the following logic to determine the colors of the bars in the chart:

• If sales is less than 20, make the bar red to indicate a “Bad” month
• If sales is between 20 and 30, make the bar yellow to indicate an “OK” month
• If sales is greater than 30, make the bar green to indicate a “Good” month

We must first create new columns that we’ll use within the bar chart.

Type the following formulas into the following cells:

• C2: =IF(B2<20, B2, NA())
• D2: =IF(AND(B2>=20, B2<30), B2, NA())
• E2: =IF(B2>=30, B2, NA())

Then click and drag these formulas down to the remaining cells in each column:

Step 3: Insert the Bar Chart

Next, highlight the cell range A1:A13, then hold Ctrl and highlight the cell range C1:E13.

Then click the Insert tab along the top ribbon, then click the icon called Clustered Column within the Charts group:

The following bar chart will appear:

Each bar is now colored based on the bar value, but we can make the chart more aesthetically pleasing by making a few adjustments.

Step 4: Customize the Chart Appearance

Next, right click on any of the bars in the chart and then click Format Data Series from the dropdown menu.

Then change the Series Overlap to 100% and the Gap Width to 50%:

This will increase the width of the bars in the chart:

Lastly, feel free to click on the bars and change their colors to red, yellow or green, and add a title and axis labels to make the chart easier to read:

Additional Resources

The following tutorials explain how to perform other common tasks in Excel: