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.


Download Practice Workbook

You can download the practice workbook here.


3 Handy Examples to Use Conditional Formatting in Data Labels in Excel

In this article, we will discuss 3 instances to use 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 value 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.

writing code to show conditional formatting data labels in excel

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.

typing code to show conditional formatting data labels in excel

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.

choosing recommended charts to show conditional formatting data labels in excel

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

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

  • Thereafter, 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

  • 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.

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

  • Then, click on the positive bars on the chart.
  • After that, click on the plus sign on the top right corner of the chart.
  • Next, from the Chart Elements, click Data Labels.
  • Finally, 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.

  • 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.

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

  • 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.

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

  • 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 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 Add Data Labels in Excel (2 Handy Ways)


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.

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 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.

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.

  • 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.

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

  • 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 om 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.

customizing data type to show conditional formatting data labels in excel

  • 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.

Read More: How to Edit Data Labels in Excel (6 Easy Ways)


Similar Readings


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 are 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 value 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.

Read More: How to Show Data Labels in Thousands in Excel Chart


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. Also, if you want to see more Excel content like this, please visit our website ExcelDemy.Com and unlock a great resource for Excel-related content.


Related Articles

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo