How to Use Conditional Formatting in Data Labels in Excel

In this article, we will show 3 examples of how to use conditional formatting in data labels in Excel. In these methods, users can apply conditions in data and format the data labels in the chart accordingly. These will help them to present their data more eloquently.

In this article, we will discuss 3 instances of using conditional formatting in data labels in Excel. In the first example, we will display the positive and negative data labels separately. Secondly, we will show the maximum and minimum values of data on the chart in different colors. Finally, we will show the data labels above and below a target value.

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. To do so, we will use the IF function to separate the positive and negative values and then plot them in the graph.

Steps:

Firstly, click on the D5 cell and type the following formula,

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

Then, hit Enter.

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

As a result, we will get a positive value in that cell.
Finally, lower the cursor down to autofill the rest of the cells.

After that, choose the E5 cell and type,

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

Then, press Enter.

In this case, the IF function will examine 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 the C5 cell, or it will return #N/A.

Thus, we will get the negative values or no values in that cell.
Move the cursor down to the last data cell to autofill.

After that, choose the B4:B10, D4:D10, and E4:E10 cell ranges.
Next, go to the Insert tab.
From the Insert tab, choose the Recommended Charts option.
As a result, a window will be opened.

From the Insert Chart dialogue box, select the Stacked Column chart.
Finally, click OK.
Consequently, we will have a chart on the screen.

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

Then, first, go to the Labels Option icon in the sidebar.
Secondly, choose Labels.
Finally, choose High as the Label Position.
As a result, the axis labels will be on the upper side of the chart.

Then, click on the positive bars on the chart.
After that, click on the plus sign in the top right corner of the chart.
Next, from the Chart Elements, click Data Labels.
Finally, from the available options, select Inside End.

Repeat the same process for the negative values.

Consequently, we will get the data labels on the chart.
Afterward, click on the data labels one by one and drag them out of the bar.

Thereafter, select the data labels over the positive bars.
As a result, a sidebar will appear.
In the sidebar, first, select the paint sign.
Then, from the Fill options, select Solid Fill.
Finally, select the color of the fill from the Color option.
Here, we will choose green as the color of the fill.

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

Finally, we will make the color of the bars the same for both negative and positive values to make them look like one series.

Thus, we will apply conditional formatting in our chart to show the negative and positive values distinctively.

Read More: How to Edit Data Labels in Excel

2. Showing Maximum and Minimum Data Labels

In this instance, we will stand out the maximum and minimum values in a chart by applying conditional formatting. We will use the IF, AND, MAX, and MIN functions to do that.

Steps:

To begin with, select the D5 cell and enter the following,

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

Then, hit the Enter button.

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

Next, move the cursor down to the last data cell.

After that, choose the E5 cell and enter,

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

Hit Enter.

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

Finally, lower the cursor down to the last cell.

After that, select the F5 cell and type the formula below,

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

Then, press Enter.

This formula will return zero the value in the cell C5 if the values in the D5 and E5 cells are zero. Else it will return zero.

Next, move the cursor down to the last cell to get the autofill values.

After that, select the D5:F10 cell range.
Then, go to the Home tab.
From the Home tab, click on the outward arrow in the Number group.
As a result, a prompt will be on the screen.

In the prompt, first, select Custom.
Then, under Type option write,

0;0;;

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

After that, select the B4:B10 and D4:F10 range.
Then, from the Insert tab select the Recommended Charts option.
Consequently, a prompt will be on the screen.

Then, from the prompt, first, choose the Stacked Column chart.
Click OK.
Consequently, the data will be plotted in a Stacked Column Chart with maximum and minimum values plotted in different colors.

Next, click on the chart first.
Then, select the plus sign to the right of the chart.
Finally, from the available options select Data Labels.
As a result, we will get the data labels over the bars.

You could format the chart a little more to make it look more attractive.

In this way, we will conditionally format our data to show maximum and minimum values in a different series.

3. Displaying Data Labels Below and Above Target Value

In the final example, we will apply conditions to our data to find out how much above or below the area to a certain target value and then plot them in a graph and show data labels. Here, we will use 1000 sales quantity as the target value and find out how the other values are below or above that value.

Steps:

Firstly, choose the D5 cell and type,

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

Then, hit the Enter button.

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

Move the cursor down to the last cell to fill automatically.

Next, choose the E5 cell and write,

=IF(C5<$C$12,C5-$C$12,0

Press Enter after that.

This formula will evaluate if the C5 value is greater or lower than 1000. If lower, then the formula will return the difference of the two. Otherwise, it will return zero.

Finally, lower the cursor down to the last data cell to autofill the values.

After that, select the cells in the C5:C10 range.
Then, go to the Home tab.
From there, go to the Numbers group and select the outward arrow.
A prompt will be on the screen as a result.

In the prompt, first, select Custom.
Then, write the following input under the Type option,

0;0;;

Finally, click OK.

As a result, all the zero values in the D5:D10 range will be replaced by blanks.
Then, choose the E5:E10 cells.
Go to the Home tab.
From the Number group, click on the outward arrow.

In the Format Cells prompt, choose the Custom option first.
Then, under the Type option enter the following input,

0;-0;;

Finally, click OK.

As a result, all the zero values in the E5:E10 cell will be replaced by blanks.
After that, select the B4:B10 and D4:E10 cell range.
Then, go to the Insert tab.
From there, select the Recommended Charts option.

Next, from the Insert Chart prompt, select the Stacked Column chart.
Finally, click OK.
As a result, we will have a chart showing how much the data values are below or above the target value.

After that, click on the horizontal axis labels of the chart and right-click.
From the prevailing options, select the Format Axis command.

Then, first, select the Labels Option sign in the sidebar.
Secondly, select Labels.
Lastly, select High as the Label Position.
Consequently, the axis labels will be on the upper side of the chart.

Thereafter, select the chart first.
Then, click on the plus sign to the top right of the chart.
Finally, from the prevailing options select Data Labels.

As a result, we will have data labels showing how much the data are above or below 1000.

In this way, we will apply conditional formatting to data labels to show how much a value is above or below a particular value.

Download Practice Workbook

You can download the practice workbook here.

Conditional Formatting Data Labels.xlsx

Conclusion

In this article, we have talked about 3 instances of how to use conditional formatting in data labels in Excel. These methods will allow users to apply a certain criterion to their data and present them accordingly. If you have any questions regarding this essay, feel free to let us know in the comments.

<< Go Back To Data Labels in Excel | Excel Chart Elements | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Use Conditional Formatting in Data Labels in Excel

1. Displaying Positive and Negative Data Labels Separately

2. Showing Maximum and Minimum Data Labels

3. Displaying Data Labels Below and Above Target Value

Conclusion

Related Articles

Leave a reply Cancel reply