How do you use conditional formatting in Excel charts?

How do you use conditional formatting in an Excel chart? In the datasheet we often use conditional formatting to mark the maximum and the minimum in different colors, and how do we achieve the same effect in the chart?

As shown in the chart, we need to make a chart based on the semi-annual sales report, taking the column chart as an example, the longest column (maximum) and the shortest column (minimum) we display in different colors, the effect is as follows

Start with the idea, instead of changing the color of the columns by simply modifying the data points, we need the graph to change in real time according to the data table, and when it comes to dynamics, we naturally think of functions, and conditional formats, and we need to use functions to help us find the maximum and minimum values in the data. Use this to make two secondary columns.

1. In C2 input formula =if (B2=max ($B $: $B $), B2, #N/a), D2 input formula =if (B2=min ($B $ $B $), B2, #N/a), fill down.

2, select A1:b6 Insert Chart, the effect is as shown

3, right click on the chart, point "select data", Bring up the "Select Data Source" dialog box

4, click Add, in the Edit Data Series dialog box, the series name Input =sheet1! $C (maximum column headings), series value input =sheet1! $C $: $C $ (maximum column data), OK.

5, click Add again, in the Edit Data Series dialog box, the series name Input =sheet1! $C (minimum column headings), series value input =sheet1! $D $: $D $ (minimum column data), OK.

6. Right-click the data series, select Format data series, and in the Series Options tab, the series overlay setting is 100%, OK.

7, right click the "Sales" series, click "Add Data Label", the results are as shown.

8, the final appropriate landscaping chart, the results are as shown

Precautions:

1, relative to the 0 value, the advantage of error value #n/a is more apparent on the line chart, the chart ignores it and does not depict it as a number of positions.

2, in order to add data labels when the #n/a is not displayed, so we add the sales data series of labels on it.