# Excel Bar Graph Color with Conditional Formatting – 3 Examples

This is the sample dataset that you want to convert to a bar graph.

## Example 1. Changing the Excel Bar Graph Color by Applying a Set of Conditions

Steps:

• Create 3 columns named Bad, Medium, and Good to insert the marks.
• Select D5 and enter the following formula.
• Press Enter.
`=IF(C5<60,C5,"")`

• Place your cursor at the bottom right corner of your cell and drag down the fill handle.

• The formula will be copied to all the cells below, and marks lower than 60 will be shown in this column.

• Select E5 and enter the following formula.
• Press Enter.
`=IF(AND(C5>=60,C5<80),C5,"")`

Formula Breakdown:

=IF(AND(C5>=60,C5<80),C5,””) checks if the value of C5 is less than 80, but greater than or equal to 60. If the test is true, it returns the value of C5. Otherwise, it returns blank. Result: Blank Cell

• Place your cursor at the bottom right corner of the cell. Drag down theÂ fill handle to copy the formula.
• All the marks between 60 and 80 will be displayed in this column.

• To find the good criteria, click F5Â  and enter the formula below.
• Press Enter.
`=IF(C5>=80,C5,"")`

• Place your cursor at the bottom right corner of the cell and drag down the fill handle.

• All the good marks will be displayed in this column.

• SelectÂ  D5:F16Â >> go to the Insert tab >> Insert Column or Bar ChartÂ >> Clustered Bar.

• A bar chart is displayed. To format it, right-click the chart and choose Select Dataâ€¦ .

• In the Select Data Source window, check 49 78 and click Remove.

• In the Edit Series window, enter Bad in Series name:
• In Series values enter D5:D16.
• Click OK.

• The Bad column is added as a Bar graph.
• Add two other legend entries. One for the Medium column and the other one for the Good column. Click OK.

• A bar graph with three different colors is displayed.

• Select Chart Elements.
• Untick Axes and Gridlines.
• Tick Data Labels.

• Click Chart Elements >> In the Legend option, choose rightward arrow>> Select Bottom.

• Select any of the Good column bars and right-click it.
• Choose Format Data Seriesâ€¦

• The Format Data Series ribbon will open.
• Go to Series Options and choose 100% as Series Overlap, and 50% as Gap Width.

• Go to the Fill & Line group on the ribbon and choose a color for the Good column. Here, Green, Accent 6.

• Choose a color for the Medium column bars. Here, Orange.
• Choose a color for the Bad column bars. Here,Â Red.

Students’ marks are in colored bars.

## Example 2. Customizing the Bar Graph Color with Conditional Formatting to Display Deviation of Data

Steps:

• To calculate the average mark, merge D5:D16.
• Click the cell and enter the formula below.
• Press Enter.
`=ROUND(AVERAGE(C5:C16),0)`

Formula Breakdown:

=ROUND(AVERAGE(C5:C16),0) returns the average of C5:C16Â  and rounds it to zero decimal place. Result: 76

• To calculate the difference between each student’s mark and the average mark, select E5 cell and enter the following formula.
• Press Enter.
`=C5-\$D\$5`

• Place your cursor at the bottom right corner of the cell and drag down the fill handle.

• Deviations from the average mark will be showcased.

• Select E5:E16Â >> go to the Insert tab >> Insert Column or Bar ChartÂ >> Clustered Bar.

• The bar graph will be displayed.
• Click Chart Elements and untick all options except Data Labels.

• Right-click any of the bars.
• Choose Format Data Seriesâ€¦

• On the Format Data Series ribbon, go to the Series Options group.
• Choose 100%Â  in Series OverlapÂ and 50% in Gap Width.

• In Fill & Line, tickÂ Invert if negative.
• Choose Green as the first Fill Color (for a positive difference) and Red as the second Fill Color (for a negative difference).

This is the output.

Read More:Â How to Color Bar Chart by Category in Excel

## Method 3 – Highlighting a Maximum Value in a Bar Graph Using Excel Conditional Formatting

Steps:

• To find the maximum mark, clickÂ D5 cell and enter the following formula.
• Press Enter.
`=IF(C5=MAX(\$C\$5:\$C\$16),C5,"")`

Formula Breakdown:

=IF(C5=MAX(\$C\$5:\$C\$16),C5,””)Â checks if the C5 value is the maximum in the range C5:C16. If it is true, it will return the C5 value. Otherwise, it will return blank. Result:Â Blank Cell

• Place your cursor at the bottom right corner of your cell. Drag down the fill handle to copy the formula.

• The maximum mark (99) is displayed in D13.

• Insert a bar graph (see Example1).
• Right-click the bar graph and choose Select Dataâ€¦

• In the Select Data Source window, click Add.

• Go to Edit Series. In Series name:,enterÂ Maximum.
• In Series values:, refer to D5:D16.
• Click Ok.

• In the Select Data Source window, click OK.

• Right-click the maximum mark bar and chooseÂ Format Data Seriesâ€¦

• On the Format Data Series ribbon, go to Fill & Line.
• Choose a color for the maximum mark in Fill Color. Here, Blue, Accent 5.

Conditional formatting was applied to customize the bar graph color.

## RelatedÂ  Articles

<< Go Back to Excel Bar Chart |Â Excel ChartsÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF