{"id":481174,"date":"2022-08-04T22:00:37","date_gmt":"2022-08-04T19:00:37","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/3-ways-to-clean-up-your-google-sheets-data\/"},"modified":"2022-08-04T22:00:37","modified_gmt":"2022-08-04T19:00:37","slug":"3-ways-to-clean-up-your-google-sheets-data","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/3-ways-to-clean-up-your-google-sheets-data\/","title":{"rendered":"#3 Ways to Clean Up Your Google Sheets Data"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 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-6a35a70566a39\" 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-6a35a70566a39\" 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\/3-ways-to-clean-up-your-google-sheets-data\/#%E2%80%9C3_Ways_to_Clean_Up_Your_Google_Sheets_Data%E2%80%9D\" >&#8220;3 Ways to Clean Up Your Google Sheets Data&#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\/3-ways-to-clean-up-your-google-sheets-data\/#1_Cleanup_Suggestions\" >1. Cleanup Suggestions<\/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\/3-ways-to-clean-up-your-google-sheets-data\/#2_Remove_Duplicates\" >2. Remove Duplicates<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/3-ways-to-clean-up-your-google-sheets-data\/#3_Trim_Whitespace\" >3. Trim Whitespace<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9C3_Ways_to_Clean_Up_Your_Google_Sheets_Data%E2%80%9D\"><\/span>&#8220;3 Ways to Clean Up Your Google Sheets Data&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<!-- UNCACHED CONTENT --><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-731741\" data-pagespeed-no-defer=\"\" 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\"\/><\/p>\n<p>Sometimes when you import data or work with others on a spreadsheet, data can get messy. You may have duplicates, inconsistent data, or extra spaces. Google Sheets offers a few features to help you clean up your data.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"1_Cleanup_Suggestions\"><\/span>1. Cleanup Suggestions<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>One feature to help you find and correct data is the suggestions. This tool shows you inconsistencies in your data along with <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/198052\/how-to-remove-duplicate-rows-in-excel\/\">duplicates or cells with leading or trailing spaces. If you\u2019re not sure what type of cleanup you need to do, this is a good way to begin.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Remove Spaces in Microsoft Excel<\/em><\/strong><\/p>\n<p>Select a sheet to get started. Go to the Data tab, move to Data Cleanup, and pick \u201cCleanup Suggestions.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-815782\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/07\/CleanupSuggestions-GoogleSheetsCleanup.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Cleanup Suggestions in the Data Cleanup menu\" width=\"559\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>When the sidebar opens on the right, you\u2019ll see everything that the tool found in your sheet. In our example, you can see that we have inconsistent data. We have USA in some cells and United States in others.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-815781\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/07\/InconsistentData-GoogleSheetsCleanup.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Inconsistent data in Google Sheets\" width=\"281\" height=\"422\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If you hover your cursor over an item in the sidebar, it highlights that data in your sheet. In some cases, you may see red for an error and green for a suggestion. And, if there is a lot of data involved, you\u2019ll see an arrow at the bottom of that section that you can expand.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-815780\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/07\/InconsistentDataHighlight-GoogleSheetsCleanup.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Inconsistent data highlighted\" width=\"650\" height=\"462\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You can allow the tool to clean up the data automatically by selecting the checkmark. You can optionally edit the data if you prefer to use something different by clicking the pencil icon and typing what you prefer. Here, we\u2019ll keep the USA suggestion which will replace all United States entries.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-815779\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/07\/InconsistentDataCorrect-GoogleSheetsCleanup.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Edit or correct the inconsistent data\" width=\"268\" height=\"433\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Next, you can see that the tool also found duplicate rows and extra spaces called Whitespace. Again, hover your cursor over an item and you\u2019ll see it highlighted in your sheet.<\/p>\n<p>To allow the tool to fix the additional data for you, choose \u201cRemove\u201d for the duplicates or \u201cTrim All\u201d for the whitespace.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-815785\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/07\/RemoveTrimAll-GoogleSheetsCleanup.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Duplicate and Whitespace errors in Cleanup Suggestions\" width=\"650\" height=\"357\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If you make changes to your sheet while you have the Cleanup Suggestions sidebar open, click \u201cRefresh\u201d to recheck the sheet.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-815786\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/07\/Refresh-GoogleSheetsCleanup.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Refresh displayed in Cleanup Suggestions \" width=\"298\" height=\"130\" 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=\"2_Remove_Duplicates\"><\/span>2. Remove Duplicates<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>An alternative to the Cleanup Suggestions is the Remove Duplicates tool. This is handy if you know up front that your data may <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/413134\/how-to-remove-duplicates-in-google-sheets\/\">contain duplicates. You can jump directly to this option to remove them.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Remove Duplicates in Google Sheets<\/em><\/strong><\/p>\n<p>Select the cells you want to check for duplicates. Go to the Data tab, select Data Cleanup, and pick \u201cRemove Duplicates.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-815790\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/07\/CleanupDuplicates-GoogleSheetsCleanup.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Remove Duplicates in the Data Cleanup menu\" width=\"559\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If your data has headers, check that box at the top of the pop-up window. Then, confirm the columns you want to review and click \u201cRemove Duplicates.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-815789\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/07\/RemoveDuplicates-GoogleSheetsCleanup.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Columns for removing duplicates\" width=\"329\" height=\"469\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You\u2019ll see a message letting you know how many duplicates were found and removed. Select \u201cOK\u201d to close the window.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-815788\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/07\/RemovedDuplicates-GoogleSheetsCleanup.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Duplicates removed message\" width=\"532\" height=\"169\" 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=\"3_Trim_Whitespace\"><\/span>3. Trim Whitespace<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Like the Remove Duplicates feature, you may know that you have extra spaces in some of your cells. The Trim Whitespace tool can fix this for you without manually <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/756263\/how-to-remove-extra-spaces-in-your-google-sheets-data\/\">removing the spaces.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Remove Extra Spaces in Your Google Sheets Data<\/em><\/strong><\/p>\n<p>Select the cells you want to check or select the whole sheet by clicking the rectangle on the top left. Go to the Data tab, select Data Cleanup, and pick \u201cTrim Whitespace.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-815791\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/07\/CleanupWhitespace-GoogleSheetsCleanup.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Trim Whitespace in the Data Cleanup menu\" width=\"559\" height=\"500\" 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 a message letting you know how many cells with extra spaces were trimmed.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-815787\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/07\/TrimmedWhitespace-GoogleSheetsCleanup.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Whitespace trimmed message\" width=\"532\" height=\"142\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Using these three data cleanup tools in Google Sheets, you can find and correct messy data without all the manual work.<\/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\/815774\/clean-up-google-sheets-data\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;3 Ways to Clean Up Your Google Sheets Data&#8221; Sometimes when you import data or work with others on a spreadsheet, data can get messy. You may have duplicates, inconsistent data, or extra spaces. Google Sheets offers a few features to help you clean up your data. 1. Cleanup Suggestions One feature to help you&#8230;<\/p>\n","protected":false},"author":1,"featured_media":481175,"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-481174","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\/481174","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=481174"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/481174\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/481175"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=481174"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=481174"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=481174"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}