{"id":478949,"date":"2022-07-28T21:00:29","date_gmt":"2022-07-28T18:00:29","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-circle-invalid-data-in-microsoft-excel\/"},"modified":"2022-07-28T21:00:29","modified_gmt":"2022-07-28T18:00:29","slug":"how-to-circle-invalid-data-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-circle-invalid-data-in-microsoft-excel\/","title":{"rendered":"#How to Circle Invalid Data in Microsoft Excel"},"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-6a2ef26199f27\" 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-6a2ef26199f27\" 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-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-circle-invalid-data-in-microsoft-excel\/#%E2%80%9CHow_to_Circle_Invalid_Data_in_Microsoft_Excel%E2%80%9D\" >&#8220;How to Circle Invalid Data in Microsoft Excel&#8221;<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-circle-invalid-data-in-microsoft-excel\/#Error_Handling_With_Data_Validation\" >Error Handling With Data Validation<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-circle-invalid-data-in-microsoft-excel\/#Circle_Invalid_Data\" >Circle Invalid Data<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Circle_Invalid_Data_in_Microsoft_Excel%E2%80%9D\"><\/span>&#8220;How to Circle Invalid Data in Microsoft Excel&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-757715\" data-pagespeed-no-defer=\"\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel logo on a green background\" width=\"1200\" height=\"675\"\/><\/p>\n<p>You can use data validation in Microsoft Excel to stop incorrect data entries. But there may be times when that validation doesn\u2019t work as planned. To help, you can use the Circle Invalid Data feature.<\/p>\n<p>With data validation, you may have a formula in the cell or have data already entered in the cell. Unfortunately, these types of situations can skip right past the data validation you require. If this h<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>ens, you can end up with incorrect data. Let\u2019s look at how to spot it by having Excel circle it for you.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Error_Handling_With_Data_Validation\"><\/span>Error Handling With Data Validation<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>When you set up the data validation for a cell or range, you can add error handling at the same time. This is a good way to let the user know that what they\u2019re trying to enter isn\u2019t valid.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-812722\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/SetUpErrorMessage-ExcelCircleInvalidData.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Error settings for data validation\" width=\"588\" height=\"412\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You can also provide a message letting them know what they <i>should<\/i> enter.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-812721\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/ErrorMessage-ExcelCircleInvalidData.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Error message for data validation\" width=\"349\" height=\"145\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Circle_Invalid_Data\"><\/span>Circle Invalid Data<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>A perfect example of data validation not doing its job is when you use a formula. You might require a specific number, decimal, or date. If the result of that formula changes and the data no longer meets the requirement, that data is allowed, and you won\u2019t see an error.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>The Basics of Structuring Formulas in Microsoft Excel<\/em><\/strong><\/p>\n<p>Let\u2019s look at an example. Here we have a formula that adds the values in cells A1 and A2 and places the result in A3. We have data validation set up in cell A3 to only allow a number between 10 and 20.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-812723\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/DataValidationNumber-ExcelCircleInvalidData.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Data validation for a number\" width=\"650\" height=\"421\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>At first, the result from the formula fits within the data validation requirement.<\/p>\n<p>But if the numbers in cells A1 or A2 change, thus changing the formula\u2019s result, the data is still allowed in that cell even though it\u2019s invalid. Plus, you don\u2019t see an error message.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-812724\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/FormulaResultChange-ExcelCircleInvalidData.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Formula result change\" width=\"430\" height=\"170\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In this case, you can use the Circle Invalid Data feature.<\/p>\n<p>Go to the Data tab, select the Data Validation drop-down arrow, and pick \u201cCircle Invalid Data\u201d in the Data Tools section of the ribbon.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-812726\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/AddCircles-ExcelCircleInvalidData.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Circle Invalid Data on the Data tab\" width=\"650\" height=\"202\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You\u2019ll then see that cell with data validation and incorrect data with a red circle around it.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-812725\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/DataCircled-ExcelCircleInvalidData.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Invalid data circled in Excel\" width=\"176\" height=\"119\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>This allows you to make whatever corrections are necessary. The circle is then removed. You can also select Data Validation &gt; Clear Validation Circles if you want to accept the data.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-812727\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/ClearCircles-ExcelCircleInvalidData.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Clear Validation Circles on the Data tab\" width=\"650\" height=\"202\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Another prime example of invalid data is if you already have data in a cell and <i>then<\/i> set up data validation. Here, we have text in our cell that\u2019s longer than 10 characters. Even though we set up the validation to accept only text less than 10 characters, the validation doesn\u2019t catch the invalid data because it\u2019s pre-existing.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-812728\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/DataValidationTextLimit-ExcelCircleInvalidData.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Data validation for a text limit\" width=\"650\" height=\"385\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In this case, using Circle Invalid Data works as expected.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-812729\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/TextCircled-ExcelCircleInvalidData.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Invalid data circled in Excel\" width=\"283\" height=\"120\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>There may be instances other than these examples where invalid data makes its way into a cell with data validation. As <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/support.microsoft.com\/en-us\/office\/display-or-hide-circles-around-invalid-data-69144e43-fc71-48e0-8d4f-0b9f1ea96c03\">Microsoft mentions<\/a>, this can happen when you copy and paste data into the cell, use the fill feature, or run a macro. However, you may simply see an error disallowing you from pasting or filling the data. Or, in some cases the data validation is simply removed.<\/p>\n<blockquote><p>\u2026validation messages won\u2019t appear and invalid data can be entered if you enter data in a cell by copying or filling, if a formula in the cell calculates a result that isn\u2019t valid, or if a macro enters invalid data in the cell.<\/p>\n<\/blockquote>\n<p>While Circle Invalid Data may not work in each of these additional situations, keep these other scenarios in mind when you initially set up data validation. And, remember to use the Circle Invalid Data feature when you can.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Add a Drop-Down List to a Cell in Excel<\/em><\/strong><\/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>\n<\/p><\/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\/812717\/how-to-circle-invalid-data-in-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Circle Invalid Data in Microsoft Excel&#8221; You can use data validation in Microsoft Excel to stop incorrect data entries. But there may be times when that validation doesn\u2019t work as planned. To help, you can use the Circle Invalid Data feature. With data validation, you may have a formula in the cell or&#8230;<\/p>\n","protected":false},"author":1,"featured_media":478950,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-478949","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\/478949","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=478949"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/478949\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/478950"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=478949"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=478949"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=478949"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}