Author Message

Joined: 25 Sep 2007
Posts: 272

 Posted: Thu Jan 26, 2017 11:51 am    Post subject: Conditional Formatting with Nested IF Function in Excel 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 > 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 Do experiment with conditional formatting – it's fun and makes you easy-going with one of Excel's most useful tools. Have fun *You may also need to read: How to use 'IF Function' in Excel – written in a very easy way *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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT + 2 Hours Page 1 of 1

 Jump to: Select a forum Forum Rules----------------General Rules Learn German Easily----------------German as a Foreign Language (Deutsch als Fremdsprache) Category----------------General ForumPolitics - commentsJokesScienceTechnology & How-To ForumEnvironmental Issues forumGeographic Information System (GIS) forumWebsites Related ForumPublic Health & Yoga forum Cappuccino & Espresso----------------Cappuccino & Espresso Job Posting----------------Job offerThe Space travellers
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