![\"10](\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2022\/10\/10-Quick-Ways-to-clean-data-in-Excel-Easily-300x157.jpg\")
<\/figure>\n<\/figure>\n\n\n\nHere\u2019s a list of Top 10 Super Neat Ways to Clean Data in Excel as follows.<\/p>\n\n\n\n
Neat Ways to Clean Excel Data<\/h2>\n\n\n\n1. Get Rid of Extra Spaces:<\/strong><\/h3>\n\n\n\nExtra Spaces are difficult to spot & correct. Multiple spaces may be easy, but trailing spaces are pretty tough. Trailing spaces are blank spaces at the end of the statement or word which are not followed by any other character. <\/p>\n\n\n\n
Here\u2019s an easy way to spot & eliminate such errors:<\/p>\n\n\n\n
Syntax: TRIM(text)<\/h4>\n\n\n\n
Steps:<\/strong><\/p>\n\n\n\n\n- Consider data with four cells with different spacing errors. <\/li>\n\n\n\n
- Now select a column & type \u201cTRIM(<\/strong>\u201c <\/li>\n\n\n\n
- Now select the cell you want to correct (in matters of spaces). <\/li>\n\n\n\n
- The cell will be corrected. If there are other erroneous cells sequentially aligned, drag the fixed cell till the point, you want to check & correct. <\/li>\n<\/ul>\n\n\n\n
This easy step can save you time!<\/p>\n\n\n\n
2. Select & Treat all blank cells:<\/strong><\/h3>\n\n\n\nBlank cells are troublesome because they often create errors while creating reports. And, people usually want to replace such cells with 0, Not Available or something like that. But replacing each cell manually on a large data table would take hours. Luckily, there\u2019s an easy way to tackle this problem.<\/p>\n\n\n\n
Steps:<\/strong><\/p>\n\n\n\n\n- Select the entire Data (you want to treat) <\/li>\n\n\n\n
- Press F5<\/strong> (on keyboard) <\/li>\n\n\n\n
- A dialogue box will appear > Select \u201cSpecial<\/strong>\u201d <\/li>\n\n\n\n
- Select \u201cBlanks<\/strong>\u201d & click \u201cOK<\/strong>\u201d <\/li>\n\n\n\n
- Now, all blank cells will be highlighted in pale grey color, out of which one cell would be white with a different border. That\u2019s the active cell, type the statement you want to replace in blank cells. <\/li>\n\n\n\n
- Hit \u201cCtrl+Enter<\/strong>\u201d <\/li>\n<\/ul>\n\n\n\n
\nNOTE:<\/strong><\/p>\n\n\n\nAt the last step, if \u201cEnter<\/strong>\u201d only is pressed, then the value will be inserted only in the active cell. So remember to press \u201cCtrl+Enter<\/strong>.\u201d<\/p>\n<\/blockquote>\n\n\n\n3. Convert Numbers Stored as Text into Numbers:<\/strong><\/h3>\n\n\n\nWhen we import data from files, other sources, databases, text, etc. During transit, data might get affected. Also, some have a habit of using an apostrophe before numerical values, which is considered as text in Excel. Such minor data conversion can drastically affect calculations.<\/p>\n\n\n\n
Suppose there are three values \u201c70, \u201970, 80\u201d. When we compare 70 and 80 (70<80), the result is \u201cTRUE.\u201d But when we compare \u201capostrophe 70 & 80\u201d (\u201870<80), the problem starts. Here the result will be FALSE as the text will be rated higher than any number. To eliminate such errors, here\u2019s a trick.<\/p>\n\n\n\n
Steps:<\/strong><\/p>\n\n\n\n\n- Select any blank cell & type 1<\/strong> <\/li>\n\n\n\n
- Select that cell & hit \u201cCtrl+C<\/strong>\u201d <\/li>\n\n\n\n
- Now select your data set & go to Paste > Paste Special <\/strong><\/li>\n\n\n\n
- In Paste Special, select \u201cMultiply<\/strong>\u201d option in the \u201cOperation<\/strong>\u201d category <\/li>\n\n\n\n
- Click \u201cOK<\/strong>\u201d <\/li>\n<\/ul>\n\n\n\n
Here it multiples every single value to \u201c1\u201d. And anything multiplied by 1 is the same number. But this trick also takes care of the apostrophe numerical.<\/p>\n\n\n\n
4. Remove Duplicates:<\/strong><\/h3>\n\n\n\nElimination of duplicate data is necessary for the creation of unique data & less usage of storage. In duplication, you can either highlight it or delete it.<\/p>\n\n\n\n
A. Highlight Duplicates:<\/strong><\/p>\n\n\n\n\n- Select the data & go to Home > Conditional Formatting > Highlight Cell<\/strong> Rules > Duplicate Values<\/strong> <\/li>\n\n\n\n
- A dialogue box will appear (Duplicate Values), Select Duplicate & formatting color<\/strong> <\/li>\n\n\n\n
- Press OK<\/strong> <\/li>\n\n\n\n
- All duplicate values will be highlighted! <\/li>\n<\/ul>\n\n\n\n
B. Delete Duplicates:<\/strong><\/p>\n\n\n\n\n- Select the data & go to DATA > Remove Duplicates<\/strong> <\/li>\n\n\n\n
- A dialogue box will appear (Remove Duplicates), tick columns whose duplicates need to be found. <\/li>\n\n\n\n
- Remember to have a click on \u201cMy data has headers<\/strong>\u201d (if your Data has headers) or else column heads will be considered as data & duplication search will be applied on it too. <\/li>\n\n\n\n
- Click OK! <\/li>\n<\/ul>\n\n\n\n
Duplicate values will be removed! Suppose you select 4 of 4 columns. Then that four columns rows should also match or else; they won\u2019t be considered duplicate.<\/p>\n\n\n\n
5<\/strong>. Highlight Errors:<\/strong><\/h3>\n\n\n\nWhile creating reports or dashboards, you might face a few arithmetical errors (like divisional errors). Such errors are easy to spot if the Data is small. But for big data, it\u2019s complicated. So to get rid of such mistakes, you can go for two ways: Conditional Formatting or Go to Special.<\/p>\n\n\n\n
A. Using Conditional Formatting:<\/strong><\/p>\n\n\n\n\n- Select the Data <\/li>\n\n\n\n
- Go to Home > Conditional Formatting > New Rule<\/strong> <\/li>\n\n\n\n
- Within New Rule, Select \u201cFormat only cells that contain<\/strong>.\u201d <\/li>\n\n\n\n
- In Rules, Select \u201cErrors\u201d & Click on \u201cFormat<\/strong>\u201d <\/li>\n\n\n\n
- Select any color & click OK<\/strong> <\/li>\n\n\n\n
- Hit the final \u201cOK<\/strong>\u201d button <\/li>\n<\/ul>\n\n\n\n
All\nthe cells with errors are highlighted & now are easy to spot.<\/p>\n\n\n\n
B. Go to Special:<\/strong><\/p>\n\n\n\n\n- Select the Data <\/li>\n\n\n\n
- Press F5<\/strong> <\/li>\n\n\n\n
- Click on \u201cSpecial\u201d <\/li>\n\n\n\n
- A dialogue box appears (Go to Special), Select Formulas <\/li>\n\n\n\n
- Now you get four options in Formulas, deselect all options except \u201cErrors\u201d <\/li>\n\n\n\n
- Click OK! Now all errors are selected, you can delete them manually or replace a statement. <\/li>\n\n\n\n
- If you wish to replace, then type the statement at active cell & hit \u201cCTRL+ENTER<\/strong>.\u201d <\/li>\n<\/ul>\n\n\n\n
6. Change Text to Lower\/Upper\/Proper Case:<\/strong><\/h3>\n\n\n\nWhile importing data, we often find names in irregular forms like a lower, upper case, or sometimes mixed. Such errors are not easy to eliminate manually. Here\u2019s a fingertip trick to bring back the consistency.<\/p>\n\n\n\n
\n- LOWER(text)\t<\/li>\n\n\n\n
- UPPER(text)\t<\/li>\n\n\n\n
- PROPER(text)<\/li>\n<\/ul>\n\n\n\n
Steps:<\/strong><\/p>\n\n\n\n\n- Just type the formula you want to use, suppose \u201cLOWER(<\/strong>\u201c and select the cell whose case needs to be changed. <\/li>\n\n\n\n
- Hit \u201cCTRL+ENTER<\/strong>.\u201d <\/li>\n\n\n\n
- The case has been changed & consistent <\/li>\n\n\n\n
- Drag down to do the same for other cells. <\/li>\n\n\n\n
- Similarly for UPPER()<\/strong> & PROPER()<\/strong> <\/li>\n<\/ul>\n\n\n\n
7. Parse Data Using Text to Column:<\/strong><\/h3>\n\n\n\nSometimes the received Data has texts filled in one cell, only separated by punctuations. Usually, the addresses are cramped in one cell separated by a comma. To distinguish values in separate cells, we can use \u201cText to Column.\u201d<\/p>\n\n\n\n
Steps:<\/strong><\/p>\n\n\n\n\n- Select the Data <\/li>\n\n\n\n
- Go to Data> Text to Column<\/strong> <\/li>\n\n\n\n
- A dialogue box will appear (Convert Text to Columns Wizard \u2013 Step 1 of 3), select Delimited or Fixed Width as per your convenience. <\/li>\n\n\n\n
- Delimited is to be selected if the width isn\u2019t fixed, click \u201cNEXT<\/strong>\u201d <\/li>\n\n\n\n
- In Delimiters tick the option which separates your text in the cell. Suppose \u201cNorwich Cathedral, Norwich, UK,\u201d here three values are separated by commas. So we will select \u201cComma\u201d for this example. And, deselect rest options. <\/li>\n\n\n\n
- View the preview & click on \u201cNEXT<\/strong>\u201d <\/li>\n\n\n\n
- Select Column Data Format & destination cell address <\/li>\n\n\n\n
- Click \u201cFINISH<\/strong>\u201d <\/li>\n<\/ul>\n\n\n\n
8. Spell Check:<\/strong><\/h3>\n\n\n\nSpelling mistakes are common in text files & PowerPoint. However, MS points out such errors by underlining it with colorful dashes. And, MS Excel doesn\u2019t have such feature. But you can use it below steps:<\/p>\n\n\n\n
\n- Select the Data <\/li>\n\n\n\n
- Press \u201cF7<\/strong>\u201d <\/li>\n\n\n\n
- A dialogue box appears, which shows you the possible wrong word & it\u2019s the possible correct spelling. Click on \u201cChange<\/strong>,\u201d if you agree with the suggestion. <\/li>\n\n\n\n
- Check & change till it says \u201cSpell check complete. You\u2019re good to go!<\/strong>\u201d <\/li>\n<\/ul>\n\n\n\n
9. Delete all Formatting:<\/strong><\/h3>\n\n\n\nSuppose you want to clear all the formats, including highlights & borders. You can do this by selecting the data & go to HOME > Clear (in editing group) > Clear Formats. It will clear the formats & you get standard content without highlights or borders. Similarly, you can clear Content, Comments, Hyperlink, or entire data (using Clear All).<\/p>\n\n\n\n
10. Use Find & Replace to Clean Data in Excel<\/strong><\/h3>\n\n\n\nA. Changing Cell References:<\/strong><\/p>\n\n\n\n\n- Press \u201cCTRL+H<\/strong>\u201d to open \u201cFind and Replace<\/strong>\u201d <\/li>\n\n\n\n
- Now in Replace > \u201cFind What<\/strong>\u201d (change the reference range too) \u201cReplace With<\/strong>\u201d <\/li>\n\n\n\n
- Suppose Find What: $B to Replace With: $C <\/li>\n\n\n\n
- Click on \u201cReplace All<\/strong>\u201d <\/li>\n\n\n\n
- Similarly finding & replacing using reference range we can clean the Data <\/li>\n<\/ul>\n\n\n\n
B. Find & Change Specific Format:<\/strong><\/p>\n\n\n\n\n- Press \u201cCTRL+H<\/strong>\u201d <\/li>\n\n\n\n
- Select \u201cOptions<\/strong>\u201d <\/li>\n\n\n\n
- Now go to \u201cFormat\u201d of \u201cFind What<\/strong>.\u201d Here you can specify the format or choose a format from the cell. Suppose you select a format. <\/li>\n\n\n\n
- Now it will show you the preview for \u201cFind What<\/strong>.\u201d <\/li>\n\n\n\n
- Click on \u201cFormat<\/strong>\u201d of \u201cReplace With<\/strong>.\u201d Suppose we go for \u201cFormat\u2026\u201d <\/li>\n\n\n\n
- Now select format, example: Number, Alignment, Font, Border, Fill, Protection. <\/li>\n\n\n\n
- Suppose we select Color then select any color to fill the column header cell. <\/li>\n\n\n\n
- Click on Replace All <\/li>\n\n\n\n
- Instantly the format has been changed! <\/li>\n<\/ul>\n\n\n\n
C. Removal of Line Breaks:<\/strong><\/p>\n\n\n\nSuppose\nwe have a data where it is separated by line breaks (same cell but\ndifferent rows). To remove these line breaks, follow the below steps:<\/p>\n\n\n\n
\n- Press \u201cCTRL+H<\/strong>\u201d <\/li>\n\n\n\n
- Find and Replace dialogue box will appear, press \u201cCTRL+J<\/strong>\u201d <\/li>\n\n\n\n
- Go to the replace with box & type a single space <\/li>\n\n\n\n
- Click Replace All <\/li>\n\n\n\n
- All rows will be managed in one row within the same cell! <\/li>\n<\/ul>\n\n\n\n
D. Removal of Parenthesis:<\/strong><\/p>\n\n\n\n\n- Select the Data <\/li>\n\n\n\n
- Press \u201cCTRL+H<\/strong>\u201d <\/li>\n\n\n\n
- Type (*) in \u201cFind What\u201d (This will consider all characters within parenthesis) <\/li>\n\n\n\n
- Leave the Replace With column empty & click Replace <\/li>\n\n\n\n
- Parenthesis characters are removed! <\/li>\n<\/ul>\n\n\n\n
Conclusion:<\/strong><\/h3>\n\n\n\nThese were TOP 10 Super Neat Ways to clean data in Excel. We have discussed the elimination of different types of data errors in simple ways. I hope you liked it!<\/p>\n","protected":false},"excerpt":{"rendered":"
In his day and age, our dependence on data is overwhelming. Thanks to our cellphones and laptop, a halo of data surrounds our life. Data is nothing but a piece of classified information.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1456","post","type-post","status-publish","format-standard","hentry","category-microsoft-excel"],"yoast_head":"
10 Quick Ways to Clean Data in Microsoft Excel Easily<\/title>\n<meta name=\"description\" content=\"View and Share Dynamic Web Training Blog Archives. This is a great source of articles and posts on Computer and IT training, tutorials and insights\" \/>\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.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"10 Quick Ways to Clean Data in Excel Easily\" \/>\n<meta property=\"og:description\" content=\"Explore the 10 effective ways to use MS Excel for efficient data organization, enhancing work ethics and productivity to take you miles ahead\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data\" \/>\n<meta property=\"og:site_name\" content=\"Dynamic Web Training Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DynamicWebTraining\/\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/DynamicWebTraining\/\" \/>\n<meta property=\"article:published_time\" content=\"2019-12-24T02:12:17+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-11-14T03:25:22+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2022\/10\/10-Quick-Ways-to-clean-data-in-Excel-Easily-300x157.jpg\" \/>\n<meta name=\"author\" content=\"Dynamic Web Training\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dynamicwebtrain\" \/>\n<meta name=\"twitter:site\" content=\"@dynamicwebtrain\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Dynamic Web Training\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/quick-ways-to-clean-excel-data#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/quick-ways-to-clean-excel-data\"},\"author\":{\"name\":\"Dynamic Web Training\",\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/#\\\/schema\\\/person\\\/c94653aed4a6decc8e357af0a1082233\"},\"headline\":\"10 Quick Ways to Clean Data in Excel Easily\",\"datePublished\":\"2019-12-24T02:12:17+00:00\",\"dateModified\":\"2024-11-14T03:25:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/quick-ways-to-clean-excel-data\"},\"wordCount\":1551,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/quick-ways-to-clean-excel-data#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/10\\\/10-Quick-Ways-to-clean-data-in-Excel-Easily-300x157.jpg\",\"articleSection\":[\"Microsoft Excel\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/quick-ways-to-clean-excel-data\",\"url\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/quick-ways-to-clean-excel-data\",\"name\":\"10 Quick Ways to Clean Data in Microsoft Excel Easily\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/quick-ways-to-clean-excel-data#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/quick-ways-to-clean-excel-data#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/10\\\/10-Quick-Ways-to-clean-data-in-Excel-Easily-300x157.jpg\",\"datePublished\":\"2019-12-24T02:12:17+00:00\",\"dateModified\":\"2024-11-14T03:25:22+00:00\",\"description\":\"Explore the 10 effective ways to use MS Excel for efficient data organization, enhancing work ethics and productivity to take you miles ahead\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/quick-ways-to-clean-excel-data#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/quick-ways-to-clean-excel-data\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/quick-ways-to-clean-excel-data#primaryimage\",\"url\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/10\\\/10-Quick-Ways-to-clean-data-in-Excel-Easily.jpg\",\"contentUrl\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/10\\\/10-Quick-Ways-to-clean-data-in-Excel-Easily.jpg\",\"width\":680,\"height\":356},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/quick-ways-to-clean-excel-data#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Training Blog\",\"item\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Training\",\"item\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/category\\\/training\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Microsoft Excel\",\"item\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/category\\\/training\\\/microsoft-excel\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"10 Quick Ways to Clean Data in Excel Easily\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/\",\"name\":\"Dynamic Web Training Blog\",\"description\":\"The Ultimate Training Experience.\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/#organization\",\"name\":\"Dynamic Web Training\",\"url\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/wp-content\\\/uploads\\\/2016\\\/02\\\/logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/wp-content\\\/uploads\\\/2016\\\/02\\\/logo.png\",\"width\":361,\"height\":109,\"caption\":\"Dynamic Web Training\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/DynamicWebTraining\\\/\",\"https:\\\/\\\/x.com\\\/dynamicwebtrain\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\\\/blog\\\/#\\\/schema\\\/person\\\/c94653aed4a6decc8e357af0a1082233\",\"name\":\"Dynamic Web Training\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/0a14e92e62ad4eee0843f5cf7da3a00e1df4c9763922d4d20ba3ed2402a6896d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/0a14e92e62ad4eee0843f5cf7da3a00e1df4c9763922d4d20ba3ed2402a6896d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/0a14e92e62ad4eee0843f5cf7da3a00e1df4c9763922d4d20ba3ed2402a6896d?s=96&d=mm&r=g\",\"caption\":\"Dynamic Web Training\"},\"description\":\"Dynamic Web Training is Australia's leading provider of instructor led software training. We offer training courses in Adobe, Web Design, Graphic Design, Photoshop, InDesign, Dreamweaver and many more.\",\"sameAs\":[\"https:\\\/\\\/www.dynamicwebtraining.com.au\",\"https:\\\/\\\/www.facebook.com\\\/DynamicWebTraining\\\/\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dynamic-web-training\",\"https:\\\/\\\/x.com\\\/dynamicwebtrain\"],\"url\":\"https:\\\/\\\/www.dynamicwebtraining.com.au\"}]}<\/script>","yoast_head_json":{"title":"10 Quick Ways to Clean Data in Microsoft Excel Easily","description":"View and Share Dynamic Web Training Blog Archives. This is a great source of articles and posts on Computer and IT training, tutorials and insights","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.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data","og_locale":"en_US","og_type":"article","og_title":"10 Quick Ways to Clean Data in Excel Easily","og_description":"Explore the 10 effective ways to use MS Excel for efficient data organization, enhancing work ethics and productivity to take you miles ahead","og_url":"https:\/\/www.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data","og_site_name":"Dynamic Web Training Blog","article_publisher":"https:\/\/www.facebook.com\/DynamicWebTraining\/","article_author":"https:\/\/www.facebook.com\/DynamicWebTraining\/","article_published_time":"2019-12-24T02:12:17+00:00","article_modified_time":"2024-11-14T03:25:22+00:00","og_image":[{"url":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2022\/10\/10-Quick-Ways-to-clean-data-in-Excel-Easily-300x157.jpg","type":"","width":"","height":""}],"author":"Dynamic Web Training","twitter_card":"summary_large_image","twitter_creator":"@dynamicwebtrain","twitter_site":"@dynamicwebtrain","twitter_misc":{"Written by":"Dynamic Web Training","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data#article","isPartOf":{"@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data"},"author":{"name":"Dynamic Web Training","@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/#\/schema\/person\/c94653aed4a6decc8e357af0a1082233"},"headline":"10 Quick Ways to Clean Data in Excel Easily","datePublished":"2019-12-24T02:12:17+00:00","dateModified":"2024-11-14T03:25:22+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data"},"wordCount":1551,"publisher":{"@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/#organization"},"image":{"@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data#primaryimage"},"thumbnailUrl":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2022\/10\/10-Quick-Ways-to-clean-data-in-Excel-Easily-300x157.jpg","articleSection":["Microsoft Excel"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data","url":"https:\/\/www.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data","name":"10 Quick Ways to Clean Data in Microsoft Excel Easily","isPartOf":{"@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data#primaryimage"},"image":{"@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data#primaryimage"},"thumbnailUrl":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2022\/10\/10-Quick-Ways-to-clean-data-in-Excel-Easily-300x157.jpg","datePublished":"2019-12-24T02:12:17+00:00","dateModified":"2024-11-14T03:25:22+00:00","description":"Explore the 10 effective ways to use MS Excel for efficient data organization, enhancing work ethics and productivity to take you miles ahead","breadcrumb":{"@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data#primaryimage","url":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2022\/10\/10-Quick-Ways-to-clean-data-in-Excel-Easily.jpg","contentUrl":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2022\/10\/10-Quick-Ways-to-clean-data-in-Excel-Easily.jpg","width":680,"height":356},{"@type":"BreadcrumbList","@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/quick-ways-to-clean-excel-data#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Training Blog","item":"https:\/\/www.dynamicwebtraining.com.au\/blog\/"},{"@type":"ListItem","position":2,"name":"Training","item":"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training"},{"@type":"ListItem","position":3,"name":"Microsoft Excel","item":"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\/microsoft-excel"},{"@type":"ListItem","position":4,"name":"10 Quick Ways to Clean Data in Excel Easily"}]},{"@type":"WebSite","@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/#website","url":"https:\/\/www.dynamicwebtraining.com.au\/blog\/","name":"Dynamic Web Training Blog","description":"The Ultimate Training Experience.","publisher":{"@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dynamicwebtraining.com.au\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/#organization","name":"Dynamic Web Training","url":"https:\/\/www.dynamicwebtraining.com.au\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2016\/02\/logo.png","contentUrl":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2016\/02\/logo.png","width":361,"height":109,"caption":"Dynamic Web Training"},"image":{"@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DynamicWebTraining\/","https:\/\/x.com\/dynamicwebtrain"]},{"@type":"Person","@id":"https:\/\/www.dynamicwebtraining.com.au\/blog\/#\/schema\/person\/c94653aed4a6decc8e357af0a1082233","name":"Dynamic Web Training","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/0a14e92e62ad4eee0843f5cf7da3a00e1df4c9763922d4d20ba3ed2402a6896d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/0a14e92e62ad4eee0843f5cf7da3a00e1df4c9763922d4d20ba3ed2402a6896d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0a14e92e62ad4eee0843f5cf7da3a00e1df4c9763922d4d20ba3ed2402a6896d?s=96&d=mm&r=g","caption":"Dynamic Web Training"},"description":"Dynamic Web Training is Australia's leading provider of instructor led software training. We offer training courses in Adobe, Web Design, Graphic Design, Photoshop, InDesign, Dreamweaver and many more.","sameAs":["https:\/\/www.dynamicwebtraining.com.au","https:\/\/www.facebook.com\/DynamicWebTraining\/","https:\/\/www.linkedin.com\/company\/dynamic-web-training","https:\/\/x.com\/dynamicwebtrain"],"url":"https:\/\/www.dynamicwebtraining.com.au"}]}},"_links":{"self":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts\/1456","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/comments?post=1456"}],"version-history":[{"count":18,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts\/1456\/revisions"}],"predecessor-version":[{"id":1975,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts\/1456\/revisions\/1975"}],"wp:attachment":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/media?parent=1456"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/categories?post=1456"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/tags?post=1456"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
Extra Spaces are difficult to spot & correct. Multiple spaces may be easy, but trailing spaces are pretty tough. Trailing spaces are blank spaces at the end of the statement or word which are not followed by any other character. <\/p>\n\n\n\n
Here\u2019s an easy way to spot & eliminate such errors:<\/p>\n\n\n\n
Syntax: TRIM(text)<\/h4>\n\n\n\n
Steps:<\/strong><\/p>\n\n\n\n This easy step can save you time!<\/p>\n\n\n\n Blank cells are troublesome because they often create errors while creating reports. And, people usually want to replace such cells with 0, Not Available or something like that. But replacing each cell manually on a large data table would take hours. Luckily, there\u2019s an easy way to tackle this problem.<\/p>\n\n\n\n Steps:<\/strong><\/p>\n\n\n\n NOTE:<\/strong><\/p>\n\n\n\n At the last step, if \u201cEnter<\/strong>\u201d only is pressed, then the value will be inserted only in the active cell. So remember to press \u201cCtrl+Enter<\/strong>.\u201d<\/p>\n<\/blockquote>\n\n\n\n When we import data from files, other sources, databases, text, etc. During transit, data might get affected. Also, some have a habit of using an apostrophe before numerical values, which is considered as text in Excel. Such minor data conversion can drastically affect calculations.<\/p>\n\n\n\n Suppose there are three values \u201c70, \u201970, 80\u201d. When we compare 70 and 80 (70<80), the result is \u201cTRUE.\u201d But when we compare \u201capostrophe 70 & 80\u201d (\u201870<80), the problem starts. Here the result will be FALSE as the text will be rated higher than any number. To eliminate such errors, here\u2019s a trick.<\/p>\n\n\n\n Steps:<\/strong><\/p>\n\n\n\n Here it multiples every single value to \u201c1\u201d. And anything multiplied by 1 is the same number. But this trick also takes care of the apostrophe numerical.<\/p>\n\n\n\n Elimination of duplicate data is necessary for the creation of unique data & less usage of storage. In duplication, you can either highlight it or delete it.<\/p>\n\n\n\n A. Highlight Duplicates:<\/strong><\/p>\n\n\n\n B. Delete Duplicates:<\/strong><\/p>\n\n\n\n Duplicate values will be removed! Suppose you select 4 of 4 columns. Then that four columns rows should also match or else; they won\u2019t be considered duplicate.<\/p>\n\n\n\n While creating reports or dashboards, you might face a few arithmetical errors (like divisional errors). Such errors are easy to spot if the Data is small. But for big data, it\u2019s complicated. So to get rid of such mistakes, you can go for two ways: Conditional Formatting or Go to Special.<\/p>\n\n\n\n A. Using Conditional Formatting:<\/strong><\/p>\n\n\n\n All\nthe cells with errors are highlighted & now are easy to spot.<\/p>\n\n\n\n B. Go to Special:<\/strong><\/p>\n\n\n\n While importing data, we often find names in irregular forms like a lower, upper case, or sometimes mixed. Such errors are not easy to eliminate manually. Here\u2019s a fingertip trick to bring back the consistency.<\/p>\n\n\n\n Steps:<\/strong><\/p>\n\n\n\n Sometimes the received Data has texts filled in one cell, only separated by punctuations. Usually, the addresses are cramped in one cell separated by a comma. To distinguish values in separate cells, we can use \u201cText to Column.\u201d<\/p>\n\n\n\n Steps:<\/strong><\/p>\n\n\n\n Spelling mistakes are common in text files & PowerPoint. However, MS points out such errors by underlining it with colorful dashes. And, MS Excel doesn\u2019t have such feature. But you can use it below steps:<\/p>\n\n\n\n Suppose you want to clear all the formats, including highlights & borders. You can do this by selecting the data & go to HOME > Clear (in editing group) > Clear Formats. It will clear the formats & you get standard content without highlights or borders. Similarly, you can clear Content, Comments, Hyperlink, or entire data (using Clear All).<\/p>\n\n\n\n A. Changing Cell References:<\/strong><\/p>\n\n\n\n B. Find & Change Specific Format:<\/strong><\/p>\n\n\n\n C. Removal of Line Breaks:<\/strong><\/p>\n\n\n\n Suppose\nwe have a data where it is separated by line breaks (same cell but\ndifferent rows). To remove these line breaks, follow the below steps:<\/p>\n\n\n\n D. Removal of Parenthesis:<\/strong><\/p>\n\n\n\n These were TOP 10 Super Neat Ways to clean data in Excel. We have discussed the elimination of different types of data errors in simple ways. I hope you liked it!<\/p>\n","protected":false},"excerpt":{"rendered":" In his day and age, our dependence on data is overwhelming. Thanks to our cellphones and laptop, a halo of data surrounds our life. Data is nothing but a piece of classified information.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1456","post","type-post","status-publish","format-standard","hentry","category-microsoft-excel"],"yoast_head":"\n
2. Select & Treat all blank cells:<\/strong><\/h3>\n\n\n\n
\n
\n
3. Convert Numbers Stored as Text into Numbers:<\/strong><\/h3>\n\n\n\n
\n
4. Remove Duplicates:<\/strong><\/h3>\n\n\n\n
\n
\n
5<\/strong>. Highlight Errors:<\/strong><\/h3>\n\n\n\n
\n
\n
6. Change Text to Lower\/Upper\/Proper Case:<\/strong><\/h3>\n\n\n\n
\n
\n
7. Parse Data Using Text to Column:<\/strong><\/h3>\n\n\n\n
\n
8. Spell Check:<\/strong><\/h3>\n\n\n\n
\n
9. Delete all Formatting:<\/strong><\/h3>\n\n\n\n
10. Use Find & Replace to Clean Data in Excel<\/strong><\/h3>\n\n\n\n
\n
\n
\n
\n
Conclusion:<\/strong><\/h3>\n\n\n\n