Forum Index
shamskm.com Home
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Conditional Formatting with Nested IF Function in Excel

 
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum
View previous topic :: View next topic  
Author Message
Admin
Site Admin


Joined: 25 Sep 2007
Posts: 268

PostPosted: Thu Jan 26, 2017 11:51 am    Post subject: Conditional Formatting with Nested IF Function in Excel Reply with quote

To meet multiple conditions in data or record, Excel offers the nested if function. We know, the IF function has three arguments – in case of nesting, you can replace one of the arguments with another function – it can be another IF function or SUM function or AVERAGE function and so on. It depends on the reqiurements of how you want to assess your data.


In the following table, I want to classify or assess my data as follows: if the temperature is

below - 8°C, then „Hard Winter“,

0 to - 8°C as „Cold“,

0 to 5°C as „Mild“,

5 to 12°C as „Good“, and

above 12°C as „Warm“.



So in cell C2 enter

=IF(B2<=-8;"Hard Winter";IF(B2<=0;"Cold";IF(B2<=5;"Mild";IF(AND(B2>5;B2<=12);"Good";"Warm"))))





Now copy it down the column.







Now do the conditional formatting


In Excel, you can highlight cell values with colors through conditional formatting, to quickly capture important information in your spreadsheet. We will do it here step-by-step to highlight our data based on their values:

Select the range (C2:C12)

Click on Conditional Formatting (you find it under the Home Tab) > Click on New Rule...






The New Formatting Rule window opens.

from Select a Rule Type > click on Format only cells that contain

here under Format only cells with > select Specific Text > type Hard Winter in the empty box

from the Format... below > select Red color > Click OK.





Now you will see that in Column C cells containing Hard Winter are marked with red.






Go ahead for the rest:

Select your range again (C2:C12) > click on Conditional Formatting > this time click on Manage Rules...

The Conditional Formatting Rules Manager window opens. Here you can see the first condition you just specified for Hard Winter.






Click on New Rule... > the New Formatting Rule window opens. Do like before:

from Select a Rule Type > click on Format only cells that contain

here under Format only cells with > select Specific Text > type Cold in the empty box

from the Format... below > select any color of your choice > click OK.


Repeat the same procedure from the Conditional Formatting Rules Manager dialog box : I mean, click again on New Rule..... and so on ….... you move on Smile > click on OK.






If you want to edit the colors or other options, either double click on any colorband or click on Edit Rule..., the Edit Formatting Rule window will open.


You can always do editing anytime later.

Just select your range > go to Conditional Formatting > click on Manage Rules...


By the way your temperature table has now a new look & feel Smile






Do experiment with conditional formatting – it's fun and makes you easy-going with one of Excel's most useful tools.

Have fun Smile




*You may also need to read:

How to use 'IF Function' in Excel – written in a very easy way Smile





*Recommended:

Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena.

For comments of suggestions, please contact us: info@shamskm.com
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group