{"id":7058,"date":"2021-09-03T12:23:53","date_gmt":"2021-09-03T12:23:53","guid":{"rendered":"https:\/\/officetuts.net\/excel\/?p=7058"},"modified":"2024-03-28T14:47:25","modified_gmt":"2024-03-28T14:47:25","slug":"use-conditional-formatting-when-cell-contains-yes","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/","title":{"rendered":"Use Conditional Formatting when Cell Contains Yes"},"content":{"rendered":"\n
Use-Conditional-Formatting-with-Blank-Cells<\/a>Download File<\/a><\/div>\n\n\n\n

When you work with spreadsheets, you will quickly figure out how useful is conditional formatting in Excel. We can use it for basically every set of data and highlight only the data that are falling under a certain category.<\/p>\n\n\n\n

In the example below, we will show how to use conditional formatting when we have a certain text in our data set.<\/p>\n\n\n\n

Use Conditional Formatting When the Cell Contains Yes<\/h2>\n\n\n\n

For the example, we will use the list of loans that were applied for, banking officers, and simple yes\/no to show whether the loan was approved or not.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

We want to highlight only those loans that were approved. To do so, we will select column C<\/strong>, go to Home >> Styles >> Conditional Formatting >> Highlight Cells Rules >> Text that Contains<\/strong>:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

On a pop-up window that appears, we will input a desired word- \u201cYes\u201d<\/strong>, and simply click OK<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Once we do that, we will have the desired cells highlighted:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If we want to filter out only these values, we will select the first row in our first three columns<\/a>, go to Data >> Sort & Filter >> Filter<\/strong>.<\/p>\n\n\n\n

When we click on a dropdown menu and go to Sort by Color<\/strong>, you will notice that we can choose the one with the pink fill.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

When we do that, only the loans that were approved are filtered:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If we want to highlight any other column based on the values in column C<\/strong>, all we have to do is select that column (in our case column A<\/strong>) and go to Home >> Styles >> Conditional Formatting >> Highlight Cells Rules >> More Rules<\/strong>:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

When we click it, we have to select the last option: Use a formula to determine which cells to format<\/strong>, and input the following formula:<\/p>\n\n\n\n

=C1=\"Yes\"<\/code><\/pre>\n\n\n\n

We will define a format for these cells as green fill, just like in the picture below:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Once we click OK<\/strong>, we will have all the cells in column A<\/strong> in which column C<\/strong> is the value \u201cYes\u201d<\/strong> populated in green color.<\/p>\n\n\n\n

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

When you work with spreadsheets, you will quickly figure out how useful is conditional formatting in Excel. We can use it for basically…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nUse Conditional Formatting when Cell Contains Yes<\/title>\n<meta name=\"description\" content=\"Learn how to use conditional formatting in Excel to highlight certain data categories. Download the example file and follow the step-by-step guide.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Use Conditional Formatting when Cell Contains Yes\" \/>\n<meta property=\"og:description\" content=\"Learn how to use conditional formatting in Excel to highlight certain data categories. Download the example file and follow the step-by-step guide.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/\" \/>\n<meta property=\"article:published_time\" content=\"2021-09-03T12:23:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-28T14:47:25+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/09\/word-image-8.png\" \/>\n<meta name=\"author\" content=\"Harun Spahic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Harun Spahic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/\"},\"author\":{\"name\":\"Harun Spahic\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2\"},\"headline\":\"Use Conditional Formatting when Cell Contains Yes\",\"datePublished\":\"2021-09-03T12:23:53+00:00\",\"dateModified\":\"2024-03-28T14:47:25+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/\"},\"wordCount\":351,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/09\/word-image-8.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"examples\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/\",\"url\":\"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/\",\"name\":\"Use Conditional Formatting when Cell Contains Yes\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/09\/word-image-8.png\",\"datePublished\":\"2021-09-03T12:23:53+00:00\",\"dateModified\":\"2024-03-28T14:47:25+00:00\",\"description\":\"Learn how to use conditional formatting in Excel to highlight certain data categories. Download the example file and follow the step-by-step guide.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/09\/word-image-8.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/09\/word-image-8.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Use Conditional Formatting when Cell Contains Yes\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2\",\"name\":\"Harun Spahic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g\",\"caption\":\"Harun Spahic\"},\"sameAs\":[\"http:\/\/human.bsru.ac.th\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Use Conditional Formatting when Cell Contains Yes","description":"Learn how to use conditional formatting in Excel to highlight certain data categories. Download the example file and follow the step-by-step guide.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/","og_locale":"en_US","og_type":"article","og_title":"Use Conditional Formatting when Cell Contains Yes","og_description":"Learn how to use conditional formatting in Excel to highlight certain data categories. Download the example file and follow the step-by-step guide.","og_url":"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/","article_published_time":"2021-09-03T12:23:53+00:00","article_modified_time":"2024-03-28T14:47:25+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/09\/word-image-8.png"}],"author":"Harun Spahic","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Harun Spahic","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/"},"author":{"name":"Harun Spahic","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2"},"headline":"Use Conditional Formatting when Cell Contains Yes","datePublished":"2021-09-03T12:23:53+00:00","dateModified":"2024-03-28T14:47:25+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/"},"wordCount":351,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/09\/word-image-8.png","keywords":["file","pinterest"],"articleSection":["examples"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/","url":"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/","name":"Use Conditional Formatting when Cell Contains Yes","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/09\/word-image-8.png","datePublished":"2021-09-03T12:23:53+00:00","dateModified":"2024-03-28T14:47:25+00:00","description":"Learn how to use conditional formatting in Excel to highlight certain data categories. Download the example file and follow the step-by-step guide.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/09\/word-image-8.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/09\/word-image-8.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/examples\/use-conditional-formatting-when-cell-contains-yes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Use Conditional Formatting when Cell Contains Yes"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."},{"@type":"Person","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2","name":"Harun Spahic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g","caption":"Harun Spahic"},"sameAs":["http:\/\/human.bsru.ac.th\/"]}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/7058"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=7058"}],"version-history":[{"count":5,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/7058\/revisions"}],"predecessor-version":[{"id":17371,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/7058\/revisions\/17371"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=7058"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=7058"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=7058"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}