How to Use Conditional Formatting in Data Labels in Excel

In this article, we will demonstrate how to use conditional formatting in data labels to present data in charts more eloquently. Download the workbook at the bottom of the article to follow along using the same dataset.

Example 1 – Displaying Positive and Negative Data Labels Separately

In the first example, we will display the positive and negative data in different colors by using conditional formatting. We’ll use the IF function to separate the positive and negative values and then plot them in a graph.

Steps:

In cell D5 enter the following formula:

=IF(C5>0,C5,NA())

Press Enter.

writing code to show conditional formatting data labels in excel

The IF function will evaluate if the value in cell C5 is greater than zero. If it is true then the formula will return the value in cell C5, else #N/A.

A positive value is returned in that cell.

Drag the cursor down to Autofill the rest of the cells.

In cell E5, enter the following formula:

=IF(C5<0,C5,NA())

Press Enter.

typing code to show conditional formatting data labels in excel

The IF function will check if the value in the C5 cell is lower than zero. If it is less than zero then the formula will return the value in cell C5, else #N/A. Either we will get a negative value or no value in that cell.

Drag the cursor down to the last data cell to Autofill.

Select the B4:B10, D4:D10, and E4:E10 cell ranges.
Go to the Insert tab.
Choose the Recommended Charts option.

choosing recommended charts to show conditional formatting data labels in excel

In the Insert Chart dialog box that opens, select the Stacked Column chart.
Click OK.

Consequently, we have a chart.

selecting stacked column chart to show conditional formatting data labels in excel

Select the horizontal axis labels of the chart and right-click.
From the available options, select Format Axis.

formatting axis to show conditional formatting data labels in excel

Go to the Labels Option icon in the sidebar.
Choose Labels.
Choose High as the Label Position.

As a result, the axis labels are on the upper side of the chart.

positioning axis labels high to show conditional formatting data labels in excel

Click on the positive bars on the chart.
Click on the plus sign in the top right corner of the chart.
From the Chart Elements, click Data Labels.
From the available options, select Inside End.

entering data labels to show conditional formatting data labels in excel

Repeat the same process for the negative values.

We have data labels on the chart.

Click on the data labels one by one and drag them out of the bar.

dragging out data labels to show conditional formatting data labels in excel

Select the data labels over the positive bars.

A sidebar will appear.

In the sidebar, select the paint sign.
From the Fill options, select Solid Fill.
Select the color of the fill from the Color option, for example green.

entering solid fills to data labels to show conditional formatting data labels in excel

Do the same for the negative values. Here, we choose red as the fill color for the negative data labels.

Make the color of the bars the same for both negative and positive values to make them look like one series.

In our chart, the negative and positive values display distinctively.

Read More: How to Edit Data Labels in Excel

Example 2 – Showing Maximum and Minimum Data Labels

In this instance, we will make the maximum and minimum values in a chart stand out by applying conditional formatting, and using the IF, AND, MAX, and MIN functions

Steps:

In cell D5, enter the following formula:

=IF(C5=MAX($C$5:$C$10),C5,0)

Press Enter.

entering if, max function to show conditional formatting data labels in excel

The formula will evaluate if the value is the maximum value in the range C5:C10. If true, the formula will return the value, else zero.

Drag the cursor down to the last data cell to Autofill the formula.

Select cell E5 and enter the following formula:

=IF(C5=MIN($C$5:$C$10),C5,0)

Press Enter.

entering if, min functions to show conditional formatting data labels in excel

Like the maximum formula, this one will look for the minimum value in the C5:C10 range.

Drag the cursor down to the last cell.

Select cell F5 and enter the formula below:

=IF(AND(D5=0,E5=0),C5,0)

Press Enter.

This formula will return zero in cell F5 if the values in cells D5 and E5 are zero, else it will return the value of cell C5.

Drag the cursor down to the last cell to get the Autofill values.

entering if, and functions to show conditional formatting data labels in excel

Select the D5:F10 range.
Go to the Home tab.
Click on the outward arrow in the Number group.

In the prompt, select Custom.
Under the Type option, enter:

0;0;;

Click OK.

As a result, all the zeros in that range will be replaced by blanks.

customizing data type to show conditional formatting data labels in excel

Select the B4:B10 and D4:F10 ranges.
From the Insert tab select the Recommended Charts option.

In the prompt, select the Stacked Column chart.
Click OK.

Consequently, the data will be plotted in a Stacked Column Chart with maximum and minimum values appearing in different colors.

Click on the chart.
Select the plus sign to the right of the chart.
From the available options select Data Labels.

As a result, we get data labels over the bars.

Format the chart as desired to make it look more attractive.

We have successfully conditionally formatted our data to show maximum and minimum values in different series.

Example 3 – Displaying Data Labels Below and Above Target Value

Now we will apply conditions to our data to find out how much above or below a certain target value they are, then plot them in a graph and show data labels. We will use a sales quantity of 1000 as the target value and find out by how much the other values are below or above that value.

Steps:

Select cell D5 and enter the following formula:

=IF(C5>$C$12,C5-$C$12,0)

Press Enter.

The formula finds out if the value in cell C5 is greater than or less than 1000. If it is, the difference between the value in cell C5 and 1000 is returned, else zero.