{"id":4725,"date":"2024-11-16T06:37:34","date_gmt":"2024-11-16T06:37:34","guid":{"rendered":"http:\/\/www.repairmsexcel.com\/blog\/?p=4725"},"modified":"2024-11-18T20:44:34","modified_gmt":"2024-11-18T20:44:34","slug":"clean-excel-data","status":"publish","type":"post","link":"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data","title":{"rendered":"How to Clean Data in Excel in 12 Ways?"},"content":{"rendered":"<div class=\"1898181a2aebca931198b7f41678d855\" data-index=\"1\" style=\"float: none; margin:0px 0 0px 0; text-align:center;\">\n<style>\r\n.leaderboard { width: 320px; height: 100px; }\r\n@media(min-width: 500px) { .leaderboard { width: 468px; height: 60px; } }\r\n@media(min-width: 800px) { .leaderboard { width: 728px; height: 90px; } }\r\n<\/style>\r\n<script async src=\"https:\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-8329139124199643\" crossorigin=\"anonymous\"><\/script>\r\n<ins class=\"adsbygoogle leaderboard\"\r\n     style=\"display:inline-block\"\r\n\t data-full-width-responsive=\"true\"\r\n     data-ad-client=\"ca-pub-8329139124199643\"\r\n     data-ad-slot=\"1972481166\"><\/ins>\r\n<script>\r\n     (adsbygoogle = window.adsbygoogle || []).push({});\r\n<\/script>\n<\/div>\n<p style=\"text-align: justify;\">Being continuously used, Excel accumulates lots of data that sometimes causes problems and leads to unexpected errors. So, it is important to<strong> clean data in Excel spreadsheets<\/strong> to avoid and fix problems. In this blog, I will cover different methods on <strong>how to clean data in Excel<\/strong> to make the dataset look attractive and beautiful.<!--more--><\/p>\n<p>Before knowing the techniques, let\u2019s understand <strong>why cleaning of data in Excel is necessary.<\/strong><\/p>\n<div style='margin: 8px 0; clear: both;'><div style='background: #eafdeb; padding: 12px;'><p><strong style='font-size: 18px;'>To recover lost Excel data, we recommend this tool:<\/strong><\/p><p style='font-size: 17px;'>This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:<\/p><div><ol style='font-size: 17px; margin-top: 4px;'><li><strong><a href='https:\/\/www.repairmsexcel.com\/blog\/free-download-now' target='_blank'>Try Excel File Repair Tool<\/a><\/strong> rated <i>Excellent<\/i> by Softpedia, Softonic & CNET.<\/li><li>Select the corrupt Excel file (XLS, XLSX) & click <b>Repair<\/b> to initiate the repair process.<\/li><li>Preview the repaired files and click <b>Save File<\/b> to save the files at desired location.<\/li><\/ol><\/div><\/div><\/div><br>\n<h2 style=\"text-align: left;\"><strong>What Is the Shortcut for Data Clear in Excel?<\/strong><\/h2>\n<p style=\"text-align: justify;\">Clearing unnecessary data is not an easy task. In this section, you will get to know different shortcuts of <strong>cleaning data in Excel <\/strong>under different situations.<\/p>\n<ol>\n<li><strong>Clear Content<\/strong>: Delete.<\/li>\n<li><strong>Clear Comments &amp; Notes<\/strong>: Alt &gt; H &gt; E &gt; M.<\/li>\n<li><strong>Clear All<\/strong>: Alt &gt; H &gt; E &gt; A.<\/li>\n<li><strong>Clear Hyperlinks<\/strong>: Alt &gt; H &gt; E &gt; L.<\/li>\n<\/ol>\n<h2 style=\"text-align: left;\"><strong>Why Cleaning Data In Excel Spreadsheet Is Important?<\/strong><\/h2>\n<p style=\"text-align: justify;\">Most of the users use multiple databases to import text files into Excel. This increases the high chances of getting data mislabeled or duplicated.<\/p>\n<p style=\"text-align: justify;\">Industries like retail, banking, telecom, insurance, etc, rely on data to grow their business. It becomes important for these industries to keep their data error-free.<\/p>\n<p style=\"text-align: justify;\">Removing data in Excel spreadsheets will help you to get rid of errors, and inconsistency and also you can map different functions. Cleaning of data will increase marketing effectiveness.<\/p>\n<p style=\"text-align: justify;\">In order to achieve maximum data efficiency, it is mandatory to remove all kinds of inconsistencies and data errors.<\/p>\n<blockquote>\n<p style=\"text-align: left;\"><strong><span style=\"color: #ff0000;\">Also Read<\/span>: <a href=\"https:\/\/www.repairmsexcel.com\/blog\/excel-broken-links\">How to Remove\/Find Broken Links in Excel<\/a><\/strong><\/p>\n<\/blockquote>\n<h2 style=\"text-align: left;\"><strong>How to Clean Data In Excel Spreadsheet?<\/strong><\/h2>\n<div class=\"su-note\"  style=\"border-color:#dad9d9;border-radius:2px;-moz-border-radius:2px;-webkit-border-radius:2px;\"><div class=\"su-note-inner su-u-clearfix su-u-trim\" style=\"background-color:#f4f3f3;border-color:#ffffff;color:#333333;border-radius:2px;-moz-border-radius:2px;-webkit-border-radius:2px;\">\n<h3 style=\"text-align: left;\"><strong>Quick Navigation:<\/strong><\/h3>\n<ol>\n<li><strong><a href=\"#s1\">Clear Duplicate Data In Excel<\/a><\/strong><\/li>\n<li><strong><a href=\"#s2\">Text To Column Feature<\/a><\/strong><\/li>\n<li><strong><a href=\"#s3\">Convert Numbers<\/a><\/strong><\/li>\n<li><strong><a href=\"#s4\">Removing Blank Cells?<\/a><\/strong><\/li>\n<li><strong><a href=\"#s5\">Clean Inconsistency Data In Excel<\/a><\/strong><\/li>\n<li><strong><a href=\"#s6\">Find And Replace Feature<\/a><\/strong><\/li>\n<li><strong><a href=\"#s7\">Remove Formatting In Excel<\/a><\/strong><\/li>\n<li><strong><a href=\"#s8\">Mark Errors In Data Set Of Excel<\/a><\/strong><\/li>\n<li><strong><a href=\"#s9\">Delete Hidden Data In Excel<\/a><\/strong><\/li>\n<li><strong><a href=\"#s10\">Check Spelling In\u00a0 Excel<\/a><\/strong><\/li>\n<li><strong><a href=\"#s11\">Fix Time And Date<\/a><\/strong><\/li>\n<li><strong><a href=\"#s12\">Rotate Or Switch Cells<\/a><\/strong><\/li>\n<\/ol>\n<\/div><\/div>\n<a name=\"s1\"><\/a><\/p>\n<h3 style=\"text-align: justify;\"><strong>1: Clear Duplicate Data In Excel<\/strong><\/h3>\n<p style=\"text-align: justify;\">You may have duplicate data all over Excel Spreadsheet and it may take time to find and remove those duplicates one by one.<\/p>\n<p style=\"text-align: justify;\">So, here Excel helps you to clear it out within a short time. Go through the steps below to remove duplicate data:<\/p>\n<ul style=\"text-align: justify;\">\n<li>Firstly<strong>, <\/strong>click inside <strong>Excel Spreadsheet<\/strong><strong>.<\/strong><\/li>\n<li>Click on <strong>Table Tools<\/strong><strong>.<\/strong><\/li>\n<li>Click on <strong>Design<\/strong><strong>.<\/strong><\/li>\n<li>Then click on <strong>Remove Duplicate<\/strong><strong>.<\/strong><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-4727 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Remove-Duplicate-in-excel.png\" alt=\"Remove Duplicate in excel\" width=\"450\" height=\"476\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Remove-Duplicate-in-excel.png 450w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Remove-Duplicate-in-excel-284x300.png 284w\" sizes=\"auto, (max-width: 450px) 100vw, 450px\" \/><\/p>\n<ul style=\"text-align: justify;\">\n<li>Select the column that includes <strong>duplicate data <\/strong>and click <strong>OK<\/strong><strong>.<\/strong><\/li>\n<\/ul>\n<p><a name=\"s2\"><\/a><\/p>\n<h3 style=\"text-align: left;\"><strong>2: Text To Column Feature<\/strong><\/h3>\n<p style=\"text-align: justify;\">There may be the possibility of getting text cramped into a single cell while importing or exporting text files from or to the database.<\/p>\n<p style=\"text-align: justify;\">In order to <strong>clean data in Excel Spreadsheet<\/strong>, you can parse the text into various cells using the text to column method.<\/p>\n<ul style=\"text-align: justify;\">\n<li>Select the <strong>text<\/strong> which you want to\u00a0split into\u00a0multiple cells.<\/li>\n<li>Click on the <strong>Data tab<\/strong>.<\/li>\n<li>In <strong>Data Tools<\/strong>, select <strong>Text to Columns<\/strong>.<\/li>\n<li>In <strong>Convert Text to Columns Wizard, <\/strong>select the <strong>Delimiters checkbox.<\/strong><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-4739 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Text-to-Columns-in-Excel-1.png\" alt=\"\" width=\"594\" height=\"370\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Text-to-Columns-in-Excel-1.png 655w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Text-to-Columns-in-Excel-1-300x187.png 300w\" sizes=\"auto, (max-width: 594px) 100vw, 594px\" \/><\/p>\n<ul>\n<li style=\"text-align: left;\">Click on<strong> the Next button.<\/strong><\/li>\n<li style=\"text-align: left;\">Now, under <strong>Delimiter<\/strong><strong>, <\/strong><strong>only select the <\/strong><strong>Space checkbox <\/strong><strong>and<\/strong> <strong>leave the rest of the checkboxes.<\/strong><\/li>\n<li style=\"text-align: left;\">Click on the <strong>Finish button<\/strong>.<img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4741 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/text-to-columns-wizard-2-2.jpg\" alt=\"\" width=\"501\" height=\"403\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/text-to-columns-wizard-2-2.jpg 519w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/text-to-columns-wizard-2-2-300x242.jpg 300w\" sizes=\"auto, (max-width: 501px) 100vw, 501px\" \/><\/li>\n<\/ul>\n<p><a name=\"s3\"><\/a><\/p>\n<h3 style=\"text-align: left;\"><strong>3: Convert Numbers<\/strong><\/h3>\n<p style=\"text-align: justify;\">When you are importing or exporting text files from or to the database, then you may find numbers in text form which can cause issues, especially on calculation.<\/p>\n<p style=\"text-align: justify;\">To avoid such issue further, follow the steps below:<\/p>\n<ul style=\"text-align: justify;\">\n<li>First, <strong>type 1<\/strong> in any <strong>blank cell<\/strong>.<\/li>\n<li>Then where you have typed 1, select that cell and press <strong>Ctrl + C<\/strong>.<\/li>\n<li>Select the cells that you want to convert into numbers.<\/li>\n<li>Right-click and select <strong>Paste<\/strong>.<\/li>\n<li>Select <strong>Paste Special <\/strong>to open the Paste Special Dialogue box<strong>.<\/strong><\/li>\n<li>In<strong> the operation section, <\/strong>select<strong> Multiply.<\/strong><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-4743 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/excel-paste-special-multiply.png\" alt=\"convert numbers\" width=\"411\" height=\"352\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/excel-paste-special-multiply.png 411w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/excel-paste-special-multiply-300x257.png 300w\" sizes=\"auto, (max-width: 411px) 100vw, 411px\" \/><\/p>\n<ul style=\"text-align: justify;\">\n<li>Click <strong>Ok.<\/strong><\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Now you will find your numbers back in numeric form.<\/p>\n<p><a name=\"s4\"><\/a><\/p>\n<h3 style=\"text-align: justify;\"><strong>4: How to Clean Data in Excel by Removing Blank Cells?<\/strong><\/h3>\n<p style=\"text-align: justify;\">Blank cells also may create further issues in your reports of Excel. If there is a small data set, then you can manually search and fill up those blank cells with 0 or Not Available.<\/p>\n<p style=\"text-align: justify;\">In the case of a huge Excel file, you can select blank cells at a time using the below steps:<\/p>\n<ul style=\"text-align: justify;\">\n<li>Firstly, select the <strong>data set <\/strong>in Excel.<\/li>\n<li>To open <strong>Go To dialogue box<\/strong>, <strong>press F5<\/strong>.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-4745 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Go-To-Dialogue-Box-Excel.png\" alt=\"remove blank cell\" width=\"333\" height=\"310\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Go-To-Dialogue-Box-Excel.png 470w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Go-To-Dialogue-Box-Excel-300x280.png 300w\" sizes=\"auto, (max-width: 333px) 100vw, 333px\" \/><\/p>\n<ul style=\"text-align: justify;\">\n<li>Now to open <strong>Go To Special dialogue box<\/strong>, select the <strong>Special\u2026<\/strong>option<strong>.<\/strong><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-4746 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/doc-display-text-if-cell-blank-2.png\" alt=\"remove blanks in excel 1\" width=\"318\" height=\"327\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/doc-display-text-if-cell-blank-2.png 318w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/doc-display-text-if-cell-blank-2-292x300.png 292w\" sizes=\"auto, (max-width: 318px) 100vw, 318px\" \/><\/p>\n<ul style=\"text-align: justify;\">\n<li>In<strong> Go To Special<\/strong>, select <strong>Blanks<\/strong>.<\/li>\n<li>Click on<strong> the OK <\/strong>button<strong>.<\/strong><\/li>\n<\/ul>\n<p style=\"text-align: justify;\">After applying these above steps, you will find all the blank cells in your data set. Now, you can fill up those blanks with 0 or not available, and after filling up press <strong>Ctrl + Enter.<\/strong><\/p>\n<p><a name=\"s5\"><\/a><\/p>\n<h3 style=\"text-align: left;\"><strong>5: Clean Inconsistency Data In Excel<\/strong><\/h3>\n<p style=\"text-align: justify;\">Sometimes you may find text inconsistent while importing data from text files which makes your data set untidy.<\/p>\n<p style=\"text-align: justify;\">You can use below mentioned some of the formulas to <strong>clean inconsistency<\/strong> <strong>data in Excel.<\/strong><\/p>\n<p><strong>Formula 1. =REPLACE(A2,2,1,\u201do\u201d)<\/strong><\/p>\n<p>Here, in this formula:<\/p>\n<ul>\n<li>A2 &#8211; position to replace<\/li>\n<li>2 &#8211; where to start<\/li>\n<li>1- number of characters to replace<\/li>\n<li>o &#8211; replace with<\/li>\n<\/ul>\n<p>Example: Before Text: Sun\u00a0 \u00a0 |\u00a0 \u00a0 \u00a0After Text: Son<\/p>\n<p><strong>Formula 2. =UPPER(A4)<\/strong><\/p>\n<p>Here, A4- Row number<\/p>\n<p style=\"text-align: justify;\">Example: Before Text: will wallece\u00a0 |\u00a0 \u00a0 \u00a0After Text: WILL WALLECE<\/p>\n<p style=\"text-align: justify;\"><strong>Formula 3. =LOWER(A5)<\/strong><\/p>\n<p>Here, A5- Row number<\/p>\n<p style=\"text-align: justify;\">Example: Before Text: Mary moore\u00a0\u00a0\u00a0 |\u00a0\u00a0 \u00a0\u00a0\u00a0After Text: mary moore<\/p>\n<p style=\"text-align: justify;\"><strong>Formula 4. =PROPER(A6)<\/strong><\/p>\n<p>Here, A6- Row number<\/p>\n<p style=\"text-align: justify;\">Example: Before Text: John jones \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0 \u00a0\u00a0\u00a0After Text: John Jones<\/p>\n<p><strong>Formula 5. =+SUBSTITUTE(A4,\u201d-\u201d,\u201d\/\u201d)<\/strong><\/p>\n<p>Here, in this formula:<\/p>\n<ul>\n<li>A4 &#8211; Row number<\/li>\n<li>&#8220;-&#8220;: symbol to be replaced.<\/li>\n<li>&#8220;\/&#8221;: symbol to be used in place of &#8220;-&#8220;<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Example: Before Text: 267-483\u00a0 \u00a0 \u00a0 \u00a0 |\u00a0 \u00a0 \u00a0 After Text: 267\/483<\/p>\n<p><strong>Formula 6. =CLEAN(A6)<\/strong><\/p>\n<p>Here, the A6-Row number.<\/p>\n<p style=\"text-align: justify;\">Example: Before Text: Monthly$report |\u00a0 \u00a0After Text: Monthlyreport<\/p>\n<p style=\"text-align: justify;\"><strong>Formula 7. =+TRIM(A5)<\/strong> (It allows to remove extra spaces)<\/p>\n<p style=\"text-align: justify;\">Here, the A5-Row number.<\/p>\n<p style=\"text-align: justify;\">Example: Before Text: 140 23\u00a0 \u00a0 \u00a0 \u00a0 |\u00a0 \u00a0 \u00a0 After Text: 14023<\/p>\n<p><a name=\"s6\"><\/a><\/p>\n<h3 style=\"text-align: left;\"><strong>6: Find And Replace Feature<\/strong><\/h3>\n<p style=\"text-align: justify;\">Excel has its inbuilt feature Find and Replace, which allows users to search for the particular data and replace it within seconds.<\/p>\n<p style=\"text-align: justify;\">So, to replace data in Excel Spreadsheet, follow the below steps:<\/p>\n<ol style=\"text-align: justify;\">\n<li>To open the Find and Replace dialogue box, press <strong>Ctrl + H.<\/strong><\/li>\n<li>Click on <strong>Replace.<\/strong><\/li>\n<li>In<strong> the Find what: <\/strong>box, enter data that you want to remove.<\/li>\n<li>In <strong>Replace with<\/strong>\u00a0box, enter data that you want to replace it.<\/li>\n<li>You can replace particular data occurrences at a time by clicking on <strong>Replace All<\/strong><\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-4747 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/findreplace-box1.png\" alt=\"findreplace-box1\" width=\"561\" height=\"231\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/findreplace-box1.png 561w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/findreplace-box1-300x124.png 300w\" sizes=\"auto, (max-width: 561px) 100vw, 561px\" \/><br \/>\n<a name=\"s7\"><\/a><\/p>\n<h3 style=\"text-align: left;\"><strong>7: Remove Formatting In Excel<\/strong><\/h3>\n<p style=\"text-align: justify;\">To get data in excel, you might be using multiple databases. If you have all your data in one place then you can clear all formats.<\/p>\n<p style=\"text-align: justify;\">Follow the below steps to remove formatting in excel:<\/p>\n<ol style=\"text-align: justify;\">\n<li>Select your <strong>data set <\/strong>of Excel.<\/li>\n<li>Click on the <strong>Home tab<\/strong>.<\/li>\n<li>Select<strong> Clear<\/strong>.<\/li>\n<li>Then select <strong>Clear Formats<\/strong>.<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-4748 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/tables_remove_clear.png\" alt=\"remove formatting\" width=\"581\" height=\"203\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/tables_remove_clear.png 730w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/tables_remove_clear-300x105.png 300w\" sizes=\"auto, (max-width: 581px) 100vw, 581px\" \/><\/p>\n<p style=\"text-align: justify;\">It will simply clean your all formatting in Excel. Using the above-given steps, you can also remove only hyperlinks, content, and comments.<\/p>\n<p><a name=\"s8\"><\/a><\/p>\n<h3 style=\"text-align: justify;\"><strong>8: Mark Errors In Data Set Of Excel<\/strong><\/h3>\n<p style=\"text-align: justify;\">You can use Conditional formatting or the Go To Special technique to mark errors in the data set so that you can delete data or replace it with another data in Excel.<\/p>\n<p style=\"text-align: justify;\">Follow the below steps for Conditional formatting:<\/p>\n<ul style=\"text-align: justify;\">\n<li>First, select the <strong>data set <\/strong>of Excel.<\/li>\n<li>Click on <strong>Home<\/strong>.<\/li>\n<li>Select <strong>Conditional Formatting<\/strong>.<\/li>\n<li>To open the <strong>New Formatting Rule dialogue box<\/strong>, select <strong>New Formatting Rule<\/strong>.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-4750 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/newRule.png\" alt=\"mark errors in dataset in excel\" width=\"303\" height=\"296\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/newRule.png 384w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/newRule-300x293.png 300w\" sizes=\"auto, (max-width: 303px) 100vw, 303px\" \/><\/p>\n<ul style=\"text-align: justify;\">\n<li>Click on <strong>Format only cells that contain<\/strong>.<\/li>\n<li>In <strong>Edit the Rule Description:<\/strong>\u00a0 select <strong>Errors<\/strong> under <strong>Format only cells with:<\/strong><\/li>\n<li>Click on <strong>Format<\/strong>.<\/li>\n<li>Click <strong>Ok<\/strong>. This will mark the errors in your data set.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-4749 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/3-s2.0-B9780128182499000054-f05-23-9780128182499.jpg\" alt=\"mark errors in dataset in excel 1\" width=\"335\" height=\"328\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/3-s2.0-B9780128182499000054-f05-23-9780128182499.jpg 532w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/3-s2.0-B9780128182499000054-f05-23-9780128182499-300x294.jpg 300w\" sizes=\"auto, (max-width: 335px) 100vw, 335px\" \/><\/p>\n<p style=\"text-align: justify;\">Now, here are the steps for Go To Special technique:<\/p>\n<ul style=\"text-align: justify;\">\n<li>Firstly, select the <strong>data set <\/strong>of Excel.<\/li>\n<li>To open <strong>Go To dialogue box<\/strong>, press<strong> F5<\/strong>.<\/li>\n<li>Select <strong>Special<\/strong> located at the bottom left side.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4745 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Go-To-Dialogue-Box-Excel.png\" alt=\"\" width=\"283\" height=\"264\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Go-To-Dialogue-Box-Excel.png 470w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Go-To-Dialogue-Box-Excel-300x280.png 300w\" sizes=\"auto, (max-width: 283px) 100vw, 283px\" \/><\/p>\n<ul style=\"text-align: justify;\">\n<li>Select <strong>Formulas.<\/strong><\/li>\n<li>Then select only <strong>Errors <\/strong>under <strong>Formulas<\/strong> and leave empty rest of the checkboxes.<\/li>\n<li>Click <strong>Ok<\/strong>. You can see errors marked up in your data set.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-4753 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/doc-select-error-values1.png\" alt=\"mark error in excel 1\" width=\"416\" height=\"283\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/doc-select-error-values1.png 580w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/doc-select-error-values1-300x204.png 300w\" sizes=\"auto, (max-width: 416px) 100vw, 416px\" \/><br \/>\n<a name=\"s9\"><\/a><\/p>\n<h3 style=\"text-align: justify;\"><strong>9: Delete Hidden Data In Excel<\/strong><\/h3>\n<p style=\"text-align: justify;\">When you collect data from different sources, there may be hidden data such as names related to comments or documents author.<\/p>\n<p style=\"text-align: justify;\">To avoid this hidden information, go through the below steps:<\/p>\n<ul style=\"text-align: justify;\">\n<li>First, click on <strong>File<\/strong>.<\/li>\n<li>Select <strong>Info<\/strong>.<\/li>\n<li>Select <strong>Check for Issues<\/strong>.<\/li>\n<li>Click on <strong>Inspect Document<\/strong>.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-4755 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/inspectdocument2010-01.jpg\" alt=\"remove hidden data\" width=\"461\" height=\"477\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/inspectdocument2010-01.jpg 505w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/inspectdocument2010-01-290x300.jpg 290w\" sizes=\"auto, (max-width: 461px) 100vw, 461px\" \/><\/p>\n<ul style=\"text-align: justify;\">\n<li>Ensure that every checkbox is marked and then click on <strong>Inspect button.<\/strong><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-4757 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/inspect_dialog.png\" alt=\"remove hidden data 1\" width=\"436\" height=\"401\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/inspect_dialog.png 536w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/inspect_dialog-300x276.png 300w\" sizes=\"auto, (max-width: 436px) 100vw, 436px\" \/><\/p>\n<ul style=\"text-align: justify;\">\n<li>Now click on <strong>Remove All button.<\/strong><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-4758 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/documentinspector.png\" alt=\"remove hidden data 2\" width=\"413\" height=\"376\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/documentinspector.png 560w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/documentinspector-300x273.png 300w\" sizes=\"auto, (max-width: 413px) 100vw, 413px\" \/><br \/>\n<a name=\"s10\"><\/a><\/p>\n<h3 style=\"text-align: justify;\"><strong>10: Check Spelling In\u00a0 Excel<\/strong><\/h3>\n<p>In order to keep data clean in Excel, it is important to keep checking spellings in Excel. It identifies the misspelled words and puts a red line below that misspelled or incorrect data.<\/p>\n<p>To check the correctness of spellings, go through the steps below:<\/p>\n<ul>\n<li>First, select the <strong>range of the cell<\/strong> in Excel that you want to check spellings.<\/li>\n<li>Then just click on the <strong>F7 key<\/strong> on the keyboard.<\/li>\n<li>Now you will find the words that are misspelled in your selected data in Excel.<\/li>\n<\/ul>\n<blockquote>\n<p style=\"text-align: left;\"><strong><span style=\"color: #ff0000;\">Also Read<\/span>: <a href=\"https:\/\/www.repairmsexcel.com\/blog\/delete-blank-rows-in-excel\">Quick Ways to Remove Blank Rows in Excel<\/a><\/strong><\/p>\n<\/blockquote>\n<p><a name=\"s11\"><\/a><\/p>\n<h3><strong>11: Fix Time And Date<\/strong><\/h3>\n<p>There may be a possibility that time and date may get jumbled with\u00a0hyphens or slash symbols of other strings. So, it is important to convert and reformat those jumbled data.<\/p>\n<p>Use convert function to change time.<\/p>\n<p><strong>= CONVERT (number, from_unit, to_unit)<\/strong><strong>\u00a0\u00a0\u00a0\u00a0 <\/strong><\/p>\n<p>number: row number where specific data is present and you want to convert it.<\/p>\n<p>from_unit: give unit that you want to convert.<\/p>\n<p>to_unit: give unit for further conversion.<\/p>\n<p>Now to change time, go through the below steps:<\/p>\n<ul>\n<li>To change the hour value in time, enter this formula in Excel\u00a0 <strong>= CONVERT (B2, &#8220;day&#8221;, &#8220;hr&#8221;)<\/strong> and hit <strong>Enter<\/strong>. It will show you the changed value in an hour with a calculation.<\/li>\n<li>To change the minute value in time, enter this formula in Excel <strong>= CONVERT (B2, &#8220;day&#8221;, &#8220;mn&#8221;) <\/strong>and hit<strong> Enter. <\/strong>It will produce the result.<\/li>\n<li>To change the second value in time<strong>,<\/strong> enter this formula in Excel <strong>= CONVERT (B2, &#8220;day&#8221;, &#8220;sec&#8221;) <\/strong>and hit<strong> Enter<\/strong><strong>. <\/strong>It will give you the changed value.<\/li>\n<\/ul>\n<p>You can convert your text date using the DATEVALUE function.<\/p>\n<p>Now for the conversion of your text date, go through the below steps:<\/p>\n<ul>\n<li>Firstly, select the <strong>blank cell<\/strong> in Excel and check if its number format is in General or not.<\/li>\n<li>Now type <strong>DATEVALUE( <\/strong>in the blank cell.<\/li>\n<li>Select the <strong>date<\/strong> that is in the text format.<\/li>\n<li>Enter close bracket ).<\/li>\n<li>Then hit <strong>Enter<\/strong> and you will find the serial number of the text date.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-4867 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/excel-formulas-functions-datevalue.jpg\" alt=\"Fix Time And Date1\" width=\"535\" height=\"208\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/excel-formulas-functions-datevalue.jpg 535w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/excel-formulas-functions-datevalue-300x117.jpg 300w\" sizes=\"auto, (max-width: 535px) 100vw, 535px\" \/><\/p>\n<ul>\n<li>Now drag <strong>fill handle<\/strong> into blank cells range same as the range of text data cell.<\/li>\n<li>Select the <strong>serial number cell<\/strong>.<\/li>\n<li>In-Home tab, select <strong>Copy <\/strong>under Clipboard section.<\/li>\n<li>Select the <strong>text date cell<\/strong>.<\/li>\n<li>Again in the Home tab, click on the <b>arrow below the Paste<\/b>.<\/li>\n<li>Choose <strong>Paste Special<\/strong>.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-4872 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/dc1204c8-c279-4f6a-8e02-fd0a72ef1949.png\" alt=\"removing data in Excel Spreadsheet\" width=\"133\" height=\"183\" \/><\/p>\n<ul>\n<li>Now, select <strong>Values <\/strong>under Paste.<\/li>\n<li>Click <strong>Ok<\/strong>.<\/li>\n<li>In-Home tab, click on the <strong>corner arrow of the Number section<\/strong> to open the Number category.<\/li>\n<li>Click on <strong>Date<\/strong> under the Number category.<\/li>\n<li>Select the <strong>format of the date<\/strong> that you want from the Type list.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-4866 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/regional_settings.jpg\" alt=\"Fix Time And Date\" width=\"479\" height=\"421\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/regional_settings.jpg 529w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/regional_settings-300x264.jpg 300w\" sizes=\"auto, (max-width: 479px) 100vw, 479px\" \/><\/p>\n<ul>\n<li>Click on <strong>Ok<\/strong>. After the completion of the conversion, select the serial number cell and just press Delete.<\/li>\n<\/ul>\n<p><a name=\"s12\"><\/a><\/p>\n<h3 style=\"text-align: left;\"><strong>12: Rotate Or Switch Cells <\/strong><\/h3>\n<p>Sometimes while importing or exporting data in Excel, structured data in tabular format changes to unstructured data in nontabular format.<\/p>\n<p>In order to transform data from columns to rows and rows to columns, use the <strong>=TRANSPOSE()<\/strong> function. Follow the below steps:<\/p>\n<ol>\n<li>Select the <strong>blank cells<\/strong> as per cells of the original set. (In the below figure, you can see eight cells vertically which are the original set. So, here eight horizontal blank cells are selected.)<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4860 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/4e28050c-4c72-4f19-86ad-7e1f0e4aaf60.png\" alt=\"Rotate Or Switch Cells\" width=\"517\" height=\"122\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/4e28050c-4c72-4f19-86ad-7e1f0e4aaf60.png 513w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/4e28050c-4c72-4f19-86ad-7e1f0e4aaf60-300x71.png 300w\" sizes=\"auto, (max-width: 517px) 100vw, 517px\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-4861 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/db13f266-e8a4-43f1-8d36-f720057d1434.png\" alt=\"Rotate Or Switch Cells1\" width=\"513\" height=\"213\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/db13f266-e8a4-43f1-8d36-f720057d1434.png 513w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/db13f266-e8a4-43f1-8d36-f720057d1434-300x125.png 300w\" sizes=\"auto, (max-width: 513px) 100vw, 513px\" \/><\/p>\n<ol start=\"2\">\n<li>Type <strong>=TRANSPOSE() <\/strong>in the selected blank cells. (Verify that after typing the function, those eight cells are remaining selected.)<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-4862 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/de782748-86ea-48de-82ba-78c4ec4c61eb.png\" alt=\"Rotate Or Switch Cells2\" width=\"520\" height=\"214\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/de782748-86ea-48de-82ba-78c4ec4c61eb.png 520w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/de782748-86ea-48de-82ba-78c4ec4c61eb-300x123.png 300w\" sizes=\"auto, (max-width: 520px) 100vw, 520px\" \/><\/p>\n<ol start=\"3\">\n<li>Now type the <strong>cell range<\/strong> in the function bracket that you want to transpose. (Same as shown in the figure)<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-4863 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/130c90d4-a2b6-421d-82e0-114e9a034c4e.png\" alt=\"Rotate Or Switch Cells 3\" width=\"495\" height=\"212\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/130c90d4-a2b6-421d-82e0-114e9a034c4e.png 495w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/130c90d4-a2b6-421d-82e0-114e9a034c4e-300x128.png 300w\" sizes=\"auto, (max-width: 495px) 100vw, 495px\" \/><\/p>\n<ol start=\"4\">\n<li>After typing, press <strong>CTRL + SHIFT + ENTER<\/strong>.<\/li>\n<\/ol>\n<p style=\"text-align: justify;\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-4865 aligncenter\" src=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/23c47f8f-620d-4147-822b-3604e9eb5cb9.png\" alt=\" removing data in Excel spreadsheets\" width=\"506\" height=\"212\" srcset=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/23c47f8f-620d-4147-822b-3604e9eb5cb9.png 506w, https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/23c47f8f-620d-4147-822b-3604e9eb5cb9-300x126.png 300w\" sizes=\"auto, (max-width: 506px) 100vw, 506px\" \/><\/p>\n<p>In this way, you can easily rearrange or transform columns and rows in your Excel.<\/p>\n<h2 style=\"text-align: left;\"><strong>Related FAQs:<\/strong><\/h2>\n        <section class=\"sc_fs_faq sc_card \">\n            <div>\n\t\t\t\t<h3>Is Excel a Data Cleaning Tool?<\/h3>                <div>\n\t\t\t\t\t                    <p>\n\t\t\t\t\t\tYes, Microsoft Excel is an excellent cleaning, and shaping technology tool that helps to cleaning unnecessary data from spreadsheet with ease.                    <\/p>\n                <\/div>\n            <\/div>\n        <\/section>\n\t\t        <section class=\"sc_fs_faq sc_card \">\n            <div>\n\t\t\t\t<h3>Is Data Cleaning Easy?<\/h3>                <div>\n\t\t\t\t\t                    <p>\n\t\t\t\t\t\tNo, data cleaning in Excel is not at all easy task                     <\/p>\n                <\/div>\n            <\/div>\n        <\/section>\n\t\t        <section class=\"sc_fs_faq sc_card \">\n            <div>\n\t\t\t\t<h3>How to Quickly Clear Contents in Excel?<\/h3>                <div>\n\t\t\t\t\t                    <p>\n\t\t\t\t\t\tBy pressing the <strong>Delete<\/strong> key in the keyboard, you can clear content in Excel quickly. But if you don't have a Del key on your keyboard, you can press fn+Backspace simultaneously.                    <\/p>\n                <\/div>\n            <\/div>\n        <\/section>\n\t\t        <section class=\"sc_fs_faq sc_card \">\n            <div>\n\t\t\t\t<h3>Can We Automate Data Cleaning?<\/h3>                <div>\n\t\t\t\t\t                    <p>\n\t\t\t\t\t\tYes, data cleaning doesn't have to be time consuming.                    <\/p>\n                <\/div>\n            <\/div>\n        <\/section>\n\t\t        <section class=\"sc_fs_faq sc_card \">\n            <div>\n\t\t\t\t<h3>What Are the Steps of Data Cleaning?<\/h3>                <div>\n\t\t\t\t\t                    <p>\n\t\t\t\t\t\tFollow the below steps carefully to clear data: <ul><li>Identify the quality issues. <\/li><li>Correct the inconsistent values. <\/li><li>Remove duplicates and fix structural errors. <\/li><li>Standardize data. <\/li><li>Spot &amp; remove unwanted outliers. <\/li><li>Address missing data. <\/li><li>Validate and cross-check.                     <\/p>\n                <\/div>\n            <\/div>\n        <\/section>\n\t\t        <section class=\"sc_fs_faq sc_card \">\n            <div>\n\t\t\t\t<h3>How Do I Filter Data in Excel PDF?<\/h3>                <div>\n\t\t\t\t\t                    <p>\n\t\t\t\t\t\tTo filter data in Microsoft Excel PDF, follow these steps: <ul><li>Select a column header arrow. <\/li><li>Uncheck (Select All) &amp; select the boxes that you want to show. <\/li><li>Choose OK. The column of the header arrow changes to a. Filter icon. Choose this icon to clear or change the filter.                     <\/p>\n                <\/div>\n            <\/div>\n        <\/section>\n\t\t\n<script type=\"application\/ld+json\">\n    {\n\t\t\"@context\": \"https:\/\/schema.org\",\n\t\t\"@type\": \"FAQPage\",\n\t\t\"mainEntity\": [\n\t\t\t\t{\n\t\t\t\t\"@type\": \"Question\",\n\t\t\t\t\"name\": \"Is Excel a Data Cleaning Tool?\",\n\t\t\t\t\"acceptedAnswer\": {\n\t\t\t\t\t\"@type\": \"Answer\",\n\t\t\t\t\t\"text\": \"Yes, Microsoft Excel is an excellent cleaning, and shaping technology tool that helps to cleaning unnecessary data from spreadsheet with ease.\"\n\t\t\t\t\t\t\t\t\t}\n\t\t\t}\n\t\t\t,\t\t\t\t{\n\t\t\t\t\"@type\": \"Question\",\n\t\t\t\t\"name\": \"Is Data Cleaning Easy?\",\n\t\t\t\t\"acceptedAnswer\": {\n\t\t\t\t\t\"@type\": \"Answer\",\n\t\t\t\t\t\"text\": \"No, data cleaning in Excel is not at all easy task\"\n\t\t\t\t\t\t\t\t\t}\n\t\t\t}\n\t\t\t,\t\t\t\t{\n\t\t\t\t\"@type\": \"Question\",\n\t\t\t\t\"name\": \"How to Quickly Clear Contents in Excel?\",\n\t\t\t\t\"acceptedAnswer\": {\n\t\t\t\t\t\"@type\": \"Answer\",\n\t\t\t\t\t\"text\": \"By pressing the <strong>Delete<\/strong> key in the keyboard, you can clear content in Excel quickly. But if you don't have a Del key on your keyboard, you can press fn+Backspace simultaneously.\"\n\t\t\t\t\t\t\t\t\t}\n\t\t\t}\n\t\t\t,\t\t\t\t{\n\t\t\t\t\"@type\": \"Question\",\n\t\t\t\t\"name\": \"Can We Automate Data Cleaning?\",\n\t\t\t\t\"acceptedAnswer\": {\n\t\t\t\t\t\"@type\": \"Answer\",\n\t\t\t\t\t\"text\": \"Yes, data cleaning doesn't have to be time consuming.\"\n\t\t\t\t\t\t\t\t\t}\n\t\t\t}\n\t\t\t,\t\t\t\t{\n\t\t\t\t\"@type\": \"Question\",\n\t\t\t\t\"name\": \"What Are the Steps of Data Cleaning?\",\n\t\t\t\t\"acceptedAnswer\": {\n\t\t\t\t\t\"@type\": \"Answer\",\n\t\t\t\t\t\"text\": \"Follow the below steps carefully to clear data: &lt;ul&gt;&lt;li&gt;Identify the quality issues. &lt;\/li&gt;&lt;li&gt;Correct the inconsistent values. &lt;\/li&gt;&lt;li&gt;Remove duplicates and fix structural errors. &lt;\/li&gt;&lt;li&gt;Standardize data. &lt;\/li&gt;&lt;li&gt;Spot &amp; remove unwanted outliers. &lt;\/li&gt;&lt;li&gt;Address missing data. &lt;\/li&gt;&lt;li&gt;Validate and cross-check.\"\n\t\t\t\t\t\t\t\t\t}\n\t\t\t}\n\t\t\t,\t\t\t\t{\n\t\t\t\t\"@type\": \"Question\",\n\t\t\t\t\"name\": \"How Do I Filter Data in Excel PDF?\",\n\t\t\t\t\"acceptedAnswer\": {\n\t\t\t\t\t\"@type\": \"Answer\",\n\t\t\t\t\t\"text\": \"To filter data in Microsoft Excel PDF, follow these steps: &lt;ul&gt;&lt;li&gt;Select a column header arrow. &lt;\/li&gt;&lt;li&gt;Uncheck (Select All) &amp; select the boxes that you want to show. &lt;\/li&gt;&lt;li&gt;Choose OK. The column of the header arrow changes to a. Filter icon. Choose this icon to clear or change the filter.\"\n\t\t\t\t\t\t\t\t\t}\n\t\t\t}\n\t\t\t\t    ]\n}\n<\/script>\n\n<h2 style=\"text-align: left;\"><strong>Final Thoughts<\/strong><\/h2>\n<p style=\"text-align: justify;\">So, this is all about <strong>how to clean data in Excel spreadsheet<\/strong>.<\/p>\n<p style=\"text-align: justify;\">Cleaning data is a way of modifying information using different techniques. However, by applying the solutions and step-by-step methods specified in this blog, you can ensure that your data is usable, correct, and consistent.<\/p>\n<p style=\"text-align: justify;\">I hope you enjoyed reading this post!<\/p>\n<div class=\"su-note\"  style=\"border-color:#dad9d9;border-radius:2px;-moz-border-radius:2px;-webkit-border-radius:2px;\"><div class=\"su-note-inner su-u-clearfix su-u-trim\" style=\"background-color:#f4f3f3;border-color:#ffffff;color:#333333;border-radius:2px;-moz-border-radius:2px;-webkit-border-radius:2px;\">\n<p style=\"text-align: justify;\"><strong>External References:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/training\/modules\/automate-data-cleaning-power-query\/\" target=\"_blank\" rel=\"noopener\">https:\/\/learn.microsoft.com\/en-us\/training\/modules\/automate-data-cleaning-power-query\/<\/a><\/li>\n<\/ul>\n<\/div><\/div>\n<p style=\"text-align: justify;\"><br><center><a href='https:\/\/www.repairmsexcel.com\/blog\/free-download-now' title='Try Excel Repair Now'><img src='https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2017\/08\/729x120-en.png' \/><\/a><\/center><br>\n\n<div style=\"font-size: 0px; height: 0px; line-height: 0px; margin: 0; padding: 0; clear: both;\"><\/div>","protected":false},"excerpt":{"rendered":"<p>Being continuously used, Excel accumulates lots of data that sometimes causes problems and leads to unexpected errors. So, it is &#8230; <\/p>\n<p class=\"read-more-container\"><a title=\"How to Clean Data in Excel in 12 Ways?\" class=\"read-more button\" href=\"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data#more-4725\" aria-label=\"Read more about How to Clean Data in Excel in 12 Ways?\">Read more<\/a><\/p>\n","protected":false},"author":5,"featured_media":4878,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[632],"tags":[1866,1865,1864],"class_list":["post-4725","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-cleaning-data-in-excel","tag-data-cleaning-in-excel","tag-how-to-clean-data-in-excel","resize-featured-image"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.2 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to Clean Data in Excel in 12 Ways?<\/title>\n<meta name=\"description\" content=\"Want to know how to clean data in Excel? Learn the importance of data cleaning, try different tips for cleaning data in Excel spreadsheets.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Clean Data in Excel in 12 Ways?\" \/>\n<meta property=\"og:description\" content=\"Want to know how to clean data in Excel? Learn the importance of data cleaning, try different tips for cleaning data in Excel spreadsheets.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data\" \/>\n<meta property=\"og:site_name\" content=\"Excel File Repair Blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-11-16T06:37:34+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-11-18T20:44:34+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Clean-Data-In-Excel.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1024\" \/>\n\t<meta property=\"og:image:height\" content=\"599\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Priyanka Sahu\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Priyanka Sahu\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"15 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data\"},\"author\":{\"name\":\"Priyanka Sahu\",\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/#\/schema\/person\/ab2250dcc6b25c12e617748b1fc166e3\"},\"headline\":\"How to Clean Data in Excel in 12 Ways?\",\"datePublished\":\"2024-11-16T06:37:34+00:00\",\"dateModified\":\"2024-11-18T20:44:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data\"},\"wordCount\":2303,\"publisher\":{\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Clean-Data-In-Excel.png\",\"keywords\":[\"cleaning data in Excel\",\"data cleaning in Excel\",\"how to clean data in Excel\"],\"articleSection\":[\"Tutorials\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data\",\"url\":\"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data\",\"name\":\"How to Clean Data in Excel in 12 Ways?\",\"isPartOf\":{\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Clean-Data-In-Excel.png\",\"datePublished\":\"2024-11-16T06:37:34+00:00\",\"dateModified\":\"2024-11-18T20:44:34+00:00\",\"description\":\"Want to know how to clean data in Excel? Learn the importance of data cleaning, try different tips for cleaning data in Excel spreadsheets.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data#primaryimage\",\"url\":\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Clean-Data-In-Excel.png\",\"contentUrl\":\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Clean-Data-In-Excel.png\",\"width\":1024,\"height\":599,\"caption\":\"12 Ways To Clean Data In Excel Spreadsheet\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.repairmsexcel.com\/blog\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Clean Data in Excel in 12 Ways?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/#website\",\"url\":\"https:\/\/www.repairmsexcel.com\/blog\/\",\"name\":\"Repair MS Excel\",\"description\":\"Get Tips, Tricks and Fixes of MS Excel Issues\",\"publisher\":{\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.repairmsexcel.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/#organization\",\"name\":\"Repair MS Excel\",\"url\":\"https:\/\/www.repairmsexcel.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/06\/repairmsexcel.com-logo.png\",\"contentUrl\":\"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/06\/repairmsexcel.com-logo.png\",\"width\":350,\"height\":70,\"caption\":\"Repair MS Excel\"},\"image\":{\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/#\/schema\/person\/ab2250dcc6b25c12e617748b1fc166e3\",\"name\":\"Priyanka Sahu\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.repairmsexcel.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/7e53e98dbd92755427e57013018cbed29ee3ae27e0a21f434dbd1ee1ce867558?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/7e53e98dbd92755427e57013018cbed29ee3ae27e0a21f434dbd1ee1ce867558?s=96&d=mm&r=g\",\"caption\":\"Priyanka Sahu\"},\"description\":\"Priyanka is a content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.\",\"sameAs\":[\"https:\/\/www.repairmsexcel.com\/\",\"https:\/\/www.linkedin.com\/in\/priyanka-sahu-2794a1242\"],\"url\":\"https:\/\/www.repairmsexcel.com\/blog\/author\/priyanka\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Clean Data in Excel in 12 Ways?","description":"Want to know how to clean data in Excel? Learn the importance of data cleaning, try different tips for cleaning data in Excel spreadsheets.","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:\/\/www.repairmsexcel.com\/blog\/clean-excel-data","og_locale":"en_US","og_type":"article","og_title":"How to Clean Data in Excel in 12 Ways?","og_description":"Want to know how to clean data in Excel? Learn the importance of data cleaning, try different tips for cleaning data in Excel spreadsheets.","og_url":"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data","og_site_name":"Excel File Repair Blog","article_published_time":"2024-11-16T06:37:34+00:00","article_modified_time":"2024-11-18T20:44:34+00:00","og_image":[{"width":1024,"height":599,"url":"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Clean-Data-In-Excel.png","type":"image\/png"}],"author":"Priyanka Sahu","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Priyanka Sahu","Est. reading time":"15 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data#article","isPartOf":{"@id":"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data"},"author":{"name":"Priyanka Sahu","@id":"https:\/\/www.repairmsexcel.com\/blog\/#\/schema\/person\/ab2250dcc6b25c12e617748b1fc166e3"},"headline":"How to Clean Data in Excel in 12 Ways?","datePublished":"2024-11-16T06:37:34+00:00","dateModified":"2024-11-18T20:44:34+00:00","mainEntityOfPage":{"@id":"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data"},"wordCount":2303,"publisher":{"@id":"https:\/\/www.repairmsexcel.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data#primaryimage"},"thumbnailUrl":"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Clean-Data-In-Excel.png","keywords":["cleaning data in Excel","data cleaning in Excel","how to clean data in Excel"],"articleSection":["Tutorials"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data","url":"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data","name":"How to Clean Data in Excel in 12 Ways?","isPartOf":{"@id":"https:\/\/www.repairmsexcel.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data#primaryimage"},"image":{"@id":"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data#primaryimage"},"thumbnailUrl":"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Clean-Data-In-Excel.png","datePublished":"2024-11-16T06:37:34+00:00","dateModified":"2024-11-18T20:44:34+00:00","description":"Want to know how to clean data in Excel? Learn the importance of data cleaning, try different tips for cleaning data in Excel spreadsheets.","breadcrumb":{"@id":"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data#primaryimage","url":"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Clean-Data-In-Excel.png","contentUrl":"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/04\/Clean-Data-In-Excel.png","width":1024,"height":599,"caption":"12 Ways To Clean Data In Excel Spreadsheet"},{"@type":"BreadcrumbList","@id":"https:\/\/www.repairmsexcel.com\/blog\/clean-excel-data#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.repairmsexcel.com\/blog"},{"@type":"ListItem","position":2,"name":"How to Clean Data in Excel in 12 Ways?"}]},{"@type":"WebSite","@id":"https:\/\/www.repairmsexcel.com\/blog\/#website","url":"https:\/\/www.repairmsexcel.com\/blog\/","name":"Repair MS Excel","description":"Get Tips, Tricks and Fixes of MS Excel Issues","publisher":{"@id":"https:\/\/www.repairmsexcel.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.repairmsexcel.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.repairmsexcel.com\/blog\/#organization","name":"Repair MS Excel","url":"https:\/\/www.repairmsexcel.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.repairmsexcel.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/06\/repairmsexcel.com-logo.png","contentUrl":"https:\/\/www.repairmsexcel.com\/blog\/wp-content\/uploads\/2021\/06\/repairmsexcel.com-logo.png","width":350,"height":70,"caption":"Repair MS Excel"},"image":{"@id":"https:\/\/www.repairmsexcel.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.repairmsexcel.com\/blog\/#\/schema\/person\/ab2250dcc6b25c12e617748b1fc166e3","name":"Priyanka Sahu","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.repairmsexcel.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/7e53e98dbd92755427e57013018cbed29ee3ae27e0a21f434dbd1ee1ce867558?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/7e53e98dbd92755427e57013018cbed29ee3ae27e0a21f434dbd1ee1ce867558?s=96&d=mm&r=g","caption":"Priyanka Sahu"},"description":"Priyanka is a content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.","sameAs":["https:\/\/www.repairmsexcel.com\/","https:\/\/www.linkedin.com\/in\/priyanka-sahu-2794a1242"],"url":"https:\/\/www.repairmsexcel.com\/blog\/author\/priyanka"}]}},"_links":{"self":[{"href":"https:\/\/www.repairmsexcel.com\/blog\/wp-json\/wp\/v2\/posts\/4725","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.repairmsexcel.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.repairmsexcel.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.repairmsexcel.com\/blog\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.repairmsexcel.com\/blog\/wp-json\/wp\/v2\/comments?post=4725"}],"version-history":[{"count":3,"href":"https:\/\/www.repairmsexcel.com\/blog\/wp-json\/wp\/v2\/posts\/4725\/revisions"}],"predecessor-version":[{"id":8807,"href":"https:\/\/www.repairmsexcel.com\/blog\/wp-json\/wp\/v2\/posts\/4725\/revisions\/8807"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.repairmsexcel.com\/blog\/wp-json\/wp\/v2\/media\/4878"}],"wp:attachment":[{"href":"https:\/\/www.repairmsexcel.com\/blog\/wp-json\/wp\/v2\/media?parent=4725"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.repairmsexcel.com\/blog\/wp-json\/wp\/v2\/categories?post=4725"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.repairmsexcel.com\/blog\/wp-json\/wp\/v2\/tags?post=4725"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}