Excel : Conditional Formatting on a Chart

Best Online Course : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects & Case Studies
- Job Placement Assistance
- Get 10% off till Oct 26, 2017
- Batch starts from October 28, 2017


Suppose you have been asked to create a chart containing high school students’ statistics score data and also having the students who scored greater than or equal to 35 as well as the students who scored between 24 and 34 and formatted them differently.

Click here to download the file

Highlighting values on the basis of 2 Criteria  :
1. Greater than or equal to 35
2. Between 24 and 34


STEP 1 : 

To accomplish this task you need to create 2 columns i.e. one for GE 35 criteria and other for values that are between 24 and 24.

STEP 2 :
Put the below formula in cell D4
=IF(C4>35,C4,”NA”)
It identifies all the values that meet criteria of greater than or equal to 35 and populates all the other cells with NA

STEP 3 :
Same like above , put the below formula in cell E4
=IF(AND(C4>=24,C4<=35),C4,”NA”)
It identifies all the values that meet criteria of greater than or equal to 35 and populates all the other cells with NA

STEP 4 :
And then insert a chart and select data from cell B4 to E12 in chart data range.

Add 2 series and select D and E column for them and name them “Greater than 35″ and “24-35″ . Formatted them in different colors.


You can see that the NA values are  hidden from the above chart because NA is treated as a zero value . As these data values change, the chart appears to apply conditional formatting.

Click on the link below to download the file.

Excel Tutorials : 100 Excel Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

0 Response to "Excel : Conditional Formatting on a Chart "

Post a Comment

← Prev