{"id":8068,"date":"2018-05-31T11:30:16","date_gmt":"2018-05-31T11:30:16","guid":{"rendered":"http:\/\/www.nurturetechacademy.in\/?p=8068"},"modified":"2018-05-31T11:30:16","modified_gmt":"2018-05-31T11:30:16","slug":"conditional-formatting-drop-list","status":"publish","type":"post","link":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/conditional-formatting-drop-list\/","title":{"rendered":"Conditional formatting through drop down list"},"content":{"rendered":"

Hi,<\/p>\n

Conditional formatting is the best way to visualize data in a colourway. With conditional formatting, we can do things like highlights data into colours, apply bars, Icons, Flags and formulas.<\/p>\n

As an Excel user, we usually highlight data into colours however in the practical scenario we are applying colour manually which is a time-consuming process.<\/p>\n

This blog post will help you to apply colours with the help of a drop-down list. In the drop-down list we have text list i.e. Salesman & Types etc. once you select the name, the colour will apply accordingly. Refer the image given below-<\/p>\n

\"Conditional<\/p>\n

isn\u2019t cool!<\/p>\n

Today we will learn how we can use Conditional formatting through Data Validation. Let\u2019s dive into the ocean of colours and condition.<\/p>\n

Step1:<\/strong> As you can see below, we have a dummy data, Workbook having a Sales report and we want to highlight the report as per my salesman and it\u2019s selling type i.e. Cash or Credit.<\/p>\n

\"pic1\"<\/p>\n

Step2:<\/strong> First, we must create a drop-down list in cell K4 and L4.<\/p>\n

How can we create a drop-down list?<\/p>\n

\"Pic2\"<\/p>\n

Go to Data tab-> Data validation-> Settings-> Any value-> select List option (Refer image given below)<\/p>\n

\"pic-3\"<\/p>\n

\"pic-4\"<\/p>\n

In source box either you can select salesman list form your existing data or you can type it manually E.g. Amit, Sumit.<\/p>\n

Note: You can create a separate list anywhere else and select them.<\/em><\/p>\n

Instead of going manually, we can open data validation through the Keyboard shortcut key \u201cAlt+D+L\u201d.<\/p>\n

Follow the step2 and create a drop-down list for the heading\u00a0\u201cType\u201d.<\/p>\n

\"Pic-6\"<\/p>\n

Step 3<\/strong>: Drop-Down list done. Select data without heading<\/u><\/strong>-> Home -> Conditional Formatting-> New Rule<\/p>\n

\"pic-7\"<\/p>\n

Click on New Rule-> new dialogue box will be open-<\/p>\n

\"pic-8\"<\/p>\n

Select option-> \u201cUse a Formula to determine which cell to format\u201d-> Now in this box we will apply a formula as given below-<\/p>\n

If(And($C3=$k$4, $D3=$L$4),\u201dTrue\u201d, \u201cFalse\u201d)<\/strong><\/p>\n

Using If function-<\/u><\/strong><\/p>\n

=If(Logical Test, Value if true, Value if false)<\/strong><\/p>\n

Logical test- <\/strong>With the help of If function we can apply a logic in between two cells or more.<\/p>\n

where you would give logical value. In another way, we can say what you want to do.<\/p>\n

Value if true- <\/strong>If excel finds any value which is equivalent to your given logics then it will revert you True value as you give in your logical test.<\/p>\n

Value if False- <\/strong>If excel could not find the value which is equivalent to your given logics then it will revert you a false as you give in your logical test.<\/p>\n

Using If with AND function- <\/strong><\/p>\n

And is the most useful member of the Logical family. When you have more than one condition and all condition are mandatory then we use AND function.<\/p>\n

=IF(And(Logical1,Logical2,Logical3,\u2026\u2026..)<\/strong><\/p>\n

Mixed cell reference- <\/strong>A mixed cell reference is either absolute column and relative rows or absolute rows or relative columns. When you add $ sign before the column, it means Column will be freeze and row will be a relative reference. For Example- $C3 is absolute for column C and Row 3 is for relative reference or C$3 is absolute for Row 3 and Column C is a relative reference.<\/p>\n

\"Pic-9\"<\/p>\n

Step4: After apply the formula\u201d If(And($C3=$k$4, $D3=$L$4),\u201dTrue\u201d, \u201cFalse\u201d\u00a0 -> <\/u><\/strong>Format->Fill->Select any colours->OK<\/p>\n

\"Pic-10\"<\/p>\n

All done! Select salesman and type from drop-down list E.g. Amit and cash and you will see data will be highlighted as per your selection.<\/p>\n

\"pic-11\"<\/p>\n

When you will change our salesman and their types, Data will be formatted accordingly.<\/p>\n

\"pic-12\"<\/p>\n

Enjoy!<\/strong><\/p>\n

you can download the practice file by clicking on this link-\u00a0Practice File<\/a><\/p>\n

Happy Learning\ud83d\ude0a <\/strong><\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

Hi,
\nConditional formatting is the best way to visualize data in a colourway. With conditional formatting, we can do things like highlights data into colours, apply bars, Icons, Flags and formulas.
\nAs an Excel user, we usually highlight data into <\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"author":1,"featured_media":8082,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"footnotes":""},"categories":[56],"tags":[265,266,263,243,264,257,260,259,249,256,248,244,250,252,245,253,254,246,251,267,255,262,258,247,261],"class_list":["post-8068","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","tag-corporate-training-companies","tag-corporate-training-companies-in-delhi","tag-corporate-training-courses","tag-corporate-training-in-delhi","tag-corporate-training-programs","tag-excel-course-online","tag-excel-courses-online","tag-excel-online-course","tag-excel-online-training","tag-excel-training-in-delhi","tag-excel-training-online","tag-learn-microsoft-excel","tag-learn-ms-excel-online","tag-learning-excel-online","tag-learning-microsoft-excel","tag-microsoft-excel-course","tag-microsoft-excel-online-training","tag-microsoft-excel-training","tag-ms-excel-learning","tag-ms-excel-online-training","tag-ms-excel-training-in-delhi","tag-online-corporate-training","tag-online-excel-course","tag-online-excel-training","tag-online-excel-training-courses"],"_links":{"self":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/8068","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/comments?post=8068"}],"version-history":[{"count":2,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/8068\/revisions"}],"predecessor-version":[{"id":8084,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/8068\/revisions\/8084"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media\/8082"}],"wp:attachment":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media?parent=8068"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/categories?post=8068"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/tags?post=8068"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}