How do you use conditional formatting in Excel charts?

Source: Internet
Author: User

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


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.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.