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.

**Table of Contents**hide

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

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

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

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

**How to Add Additional Data Labels to Excel Chart****[Fixed!] Excel Chart Data Labels Overlap (3 Possible Solutions)****How to Use Millions in Data Labels of Excel Chart (3 Easy Ways)****Data Labels in Excel Pivot Chart (Detailed Analysis)****How to Add Outside End Data Labels in Excel (2 Examples)**

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

**How to Change Font Size of Data Labels in Excel****[Fixed:] Excel Chart Is Not Showing All Data Labels (2 Solutions)****How to Move Data Labels In Excel Chart (2 Easy Methods)****Show Data Labels in Excel 3D Maps (2 Easy Ways)****How to Remove Zero Data Labels in Excel Graph (3 Easy Ways)****Add Two Data Labels in Excel Chart (with Easy Steps)**