{"id":400809,"date":"2022-01-28T22:00:51","date_gmt":"2022-01-28T19:00:51","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-automatically-highlight-certain-text-in-google-sheets\/"},"modified":"2022-01-28T22:00:51","modified_gmt":"2022-01-28T19:00:51","slug":"how-to-automatically-highlight-certain-text-in-google-sheets","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-automatically-highlight-certain-text-in-google-sheets\/","title":{"rendered":"#How to Automatically Highlight Certain Text in Google Sheets"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a26bad68196b\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a26bad68196b\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-automatically-highlight-certain-text-in-google-sheets\/#Set_Up_Conditional_Formatting_for_Text_in_Google_Sheets\" >Set Up Conditional Formatting for Text in Google Sheets<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-automatically-highlight-certain-text-in-google-sheets\/#Text_Contains_or_Does_Not_Contain\" >Text Contains or Does Not Contain<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-automatically-highlight-certain-text-in-google-sheets\/#Text_Starts_or_Ends_With\" >Text Starts or Ends With<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-automatically-highlight-certain-text-in-google-sheets\/#Text_Is_Exactly\" >Text Is Exactly<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<p><strong>&#8220;#How to Automatically Highlight Certain Text in Google Sheets&#8221;<\/strong><\/p>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-731741\" data-pagespeed-lazy-srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 400w, https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 1200w\" sizes=\"auto, 400w, 1200w\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"\" width=\"1200\" height=\"675\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Many times, the text in your spreadsheet is just as important as the numbers, or maybe more depending on your data. If you want to make certain text stand out, you can automatically highlight it in Google Sheets.<\/p>\n<p>You may have a sheet containing customer names, product details, student grades, or business locations. With conditional formatting, you can automatically highlight the text you need so that you can spot it quickly.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Set_Up_Conditional_Formatting_for_Text_in_Google_Sheets\"><\/span>Set Up Conditional Formatting for Text in Google Sheets<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Google Sheets offers several ways to automatically highlight the text you want. Your options are:<\/p>\n<ul>\n<li>Text contains<\/li>\n<li>Text does not contain<\/li>\n<li>Text starts with<\/li>\n<li>Text ends with<\/li>\n<li>Text is exactly<\/li>\n<\/ul>\n<p>You can choose the option that fits your data.<\/p>\n<p>To <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>ly the conditional formatting, select the cells you want to format, go to the Format tab, and choose \u201cConditional Formatting.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-777434\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/01\/FormatConditionalFormatting-GoogleSheetsHighlightText.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Select Conditional Formatting\" width=\"300\" height=\"428\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Use the Single Color tab at the top of the sidebar that appears. Confirm the cell range in the Apply to Range box or add another range if you like.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-777432\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/01\/ApplyToRange-GoogleSheetsHighlightText.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Confirm the cells in Apply To Range\" width=\"288\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Under Format Rules, use the Format Cells If drop-down box to choose one of the above text options. In the subsequent cell, enter the text to look for.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-777433\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/01\/FormatRulesText-GoogleSheetsHighlightText.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Choose a Text rule\" width=\"300\" height=\"485\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Set up the font or cell highlighting in the Formatting Style section and click \u201cDone\u201d to apply the conditional formatting rule.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-777436\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/01\/FormattingStyle-GoogleSheetsHighlightText.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Pick the Formatting Style\" width=\"288\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Let\u2019s look at some examples.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Text_Contains_or_Does_Not_Contain\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"autotoc_anchor_1\">Text Contains or Does Not Contain<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Maybe you imported data that contains random characters like percent signs. You want to highlight the cells so that you can correct that data. For this situation, you can use Text Contains for the rule.<\/p>\n<p>Follow the steps above to select the cells and open the Conditional Formatting sidebar. Choose \u201cText Contains\u201d in the Format Cells If drop-down box and enter the percent sign in the box below it.<\/p>\n<p>Pick the formatting you want to use, then click \u201cDone,\u201d and you should see all the cells highlighted that contain the character you want to correct.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-777437\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/01\/Contains-GoogleSheetsHighlightText.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Text Contains conditional formatting rule in Google Sheets\" width=\"650\" height=\"428\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Alternatively, maybe you want to review a sheet <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/756213\/how-to-restrict-data-to-email-addresses-in-google-sheets\/\">for email addresses and highlight those that do not contain the @ (at) symbol so you can fix them. For this scenario, you would use Text Does Not Contain for the rule.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Restrict Data to Email Addresses in Google Sheets<\/em><\/strong><\/p>\n<p>Select the cells and open the sidebar. Pick \u201cText Does Not Contain\u201d in the Format Cells If drop-down box and enter the @ (at) symbol in the box below it.<\/p>\n<p>When you set up the formatting and click \u201cDone,\u201d you should see the cells highlighted that are missing that symbol you need for an email address.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-777438\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/01\/DoesNotContain-GoogleSheetsHighlightText.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Text Does Not Contain conditional formatting rule in Google Sheets\" width=\"650\" height=\"433\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Text_Starts_or_Ends_With\"><\/span>Text Starts or Ends With<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>You can use entire words, a set of letters, or even a single letter to find the text you need. Here, we have a list of vehicles and we want to highlight all of the Honda and Hyundai models. For this rule, you can use the Text Starts With option.<\/p>\n<p>Select the cells and open the sidebar to create the rule. Pick \u201cText Starts With\u201d in the Format Cells If drop-down box and enter the letter H in the box below it.<\/p>\n<p>Choose the format for the cells or text and click \u201cDone\u201d to highlight all the Honda and Hyundai vehicles in the list.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-777441\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/01\/StartsWith-GoogleSheetsHighlightText.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Text Starts With conditional formatting rule in Google Sheets\" width=\"650\" height=\"423\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>For an example using a set of letters, we\u2019ll highlight all science classes that end in \u201cology\u201d using the Text Ends With option.<\/p>\n<p>Select the cells and open the sidebar to create the rule. Pick \u201cText Ends With\u201d in the Format Cells If drop-down box and enter \u201cology\u201d in the box below it.<\/p>\n<p>Choose the format for the cells or text and click \u201cDone\u201d to highlight all the science classes in the list.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-777440\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/01\/EndsWith-GoogleSheetsHighlightText.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Text Ends With conditional formatting rule in Google Sheets\" width=\"650\" height=\"426\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Text_Is_Exactly\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"autotoc_anchor_3\">Text Is Exactly<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>For our final example, we have a sheet of student names and grades. We want to apply conditional formatting so that when a student receives a grade of F, that grade is highlighted. Here, you would use the Text Is Exactly option for the rule.<\/p>\n<p>Select the cells and open the sidebar. Pick \u201cText Is Exactly\u201d in the Format Cells If drop-down box and enter the letter F in the box below it.<\/p>\n<p>Choose the format and click \u201cDone\u201d to highlight all letter F grades.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-777439\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/01\/IsExactly-GoogleSheetsHighlightText.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Text Is Exactly conditional formatting rule in Google Sheets\" width=\"650\" height=\"423\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>When you want to highlight specific text in your Google Sheet, conditional formatting provides you with several options to find the exact text you need.<\/p>\n<p>For more ways to use conditional formatting in Google Sheets, take a look at how to <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/733558\/how-to-highlight-blanks-or-errors-in-google-sheets\/\">highlight blanks or errors or how to apply a color scale based on value.<\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/777429\/how-to-automatically-highlight-certain-text-in-google-sheets\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#How to Automatically Highlight Certain Text in Google Sheets&#8221; Many times, the text in your spreadsheet is just as important as the numbers, or maybe more depending on your data. If you want to make certain text stand out, you can automatically highlight it in Google Sheets. You may have a sheet containing customer names,&#8230;<\/p>\n","protected":false},"author":1,"featured_media":400810,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-400809","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/400809","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=400809"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/400809\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/400810"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=400809"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=400809"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=400809"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}