Charts with Conditional Formatting

Charts with Conditional Formatting

Apply conditional formatting to charts to highlight the highest or lowest value or values. 

When to use

We use conditional formatting with charts whenever we want to provide greater insight into the data by highlighting key values. 

Instructions

Insert a Chart

• Select the data. 
• From the Insert tab, in the Charts group, click the drop-down next to Column Chart and choose 2-D Bar. 

Format the Chart

Adjust Gap Width

• Select the bars and press CTRL+1 to open Format Data Series. 
• Adjust the Gap Width to make the bars wider. 

Add Data Labels

• Click on the bars. 
• Right-click and Add Data Labels. 
• From Label Options, in Label Position, choose Inside End. 
• Change the font to white and bold. 
• Delete the horizontal axis. 

Apply Conditional Formatting

Create an IF Formula

Let's apply conditional formatting so the highest grade shows in a different color. To do this, we need to create a formula first to find the maximum value. 

• Add another column to the dataset and title it 'Max'. 
• In cell D5 type the formula, =IF(C5=MAX($C$5:$C$12),C5,"")

This formula will check the values in column C to find which is the maximum value in the range. If the value is the maximum value, the value will be returned, if its not the cell will be blank. 

Add a New Chart Series

We now need to add the new series to the chart. 

• Click on the chart, right-click and choose Select Data. 
• Click Add. 
• In the Series Label field, click cell D4. 
• In the Series Values field, select the cell range in the 'Max' column. 
• Click OK. 

• Click on the orange bar. 
• From Series Options change the Series Overlap to 100%. 

Highlight the Top 3 Values

We can use conditional formatting to highlight the top 3 values in a chart using the LARGE function. 

• Edit the formula in column D. 
• Type =IF(C5>=LARGE($C$5:$C$12,3),C5,"")

This formula will find the 3 largest values in the select cell range.