10 Effective Way To Clean Data On Excel

In this 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. Microsoft Excel is one of the most used data handling/analysis software. At the same time, one tiny mistake in analyzing data can cause headaches. Data is the backbone of any analysis that you do. It is an eternal problem and not only in Excel! Here’s a list of top 10 Super Neat Ways to Clean Data in Excel as follows.

1. Get Rid of Extra Spaces

When its come to clean data on excel extra spaces are painfully difficult to spot. While you may somehow spot the extra spaces between words or numbers, trailing spaces are not even visible. Here is a neat way to get rid of these extra spaces.

– Use TRIM Function.

Here a practical examples of using the TRIM function.

Example 1 – Remove Leading, Trailing, and Double Spaces

TRIM function is made to do this.

Below is an example where there are leading, trailing, and double spaces in the cells.

Excel TRIM Function - Data set Example 1

You can easily remove all these extra spaces by using the below TRIM function:

=TRIM(A1)

Copy-paste this into all the cells and you are all set.

2. Select & Treat all blank cells

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’s an easy way to tackle this problem.


Steps:

  • Select the entire Data (you want to treat)
  • Press F5 (on keyboard)
  • A dialogue box will appear > Select “Special
  • Select “Blanks” & click “OK
  • Now, all blank cells will be highlighted in pale grey color, out of which one cell would be white with a different border. That’s the active cell, type the statement you want to replace in blank cells.
  • Hit “Ctrl+Enter

3. Convert Numbers Stored as Text into Numbers

Sometimes when you import data from text files or external databases, numbers get stored as text. Also, some people are in the habit of using an apostrophe (‘) before a number to make it text. This could create serious issues if you are using these cells in calculations. Here is a foolproof way to converts these numbers stored as text back into numbers.

Steps:

  • In any blank cell, type 1
  • Select the cell where you typed 1, and press Control + C
  • Select the cell/range which you want to convert to numbers
  • Select Paste –> Paste Special (KeyBoard Shortcut – Alt + E + S)
  • In the Paste Special Dialogue box, select Multiply (in operations category)
  • Click OK. This converts all the numbers in text format back to numbers.
Clean Data in Excel - Paste Special Multiply

4. Remove Duplicates

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.

A) Highlight Duplicates:

  • Select the data & go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values
  • A dialogue box will appear (Duplicate Values), Select Duplicate & formatting color
  • Press OK
  • All duplicate values will be highlighted!
Clean Data in Excel - Highlight Duplicates

B) Delete Duplicates:

  • Select the data & go to DATA > Remove Duplicates
  • A dialogue box will appear (Remove Duplicates), tick columns whose duplicates need to be found.
  • Remember to have a click on “My data has headers” (if your Data has headers) or else column heads will be considered as data & a duplication search will be applied on it too.
  • Click OK!
Clean Data in Excel - Remove Duplicates select column

Duplicate values will be removed! Suppose you select 4 of 4 columns. Then that four columns rows should also match or else; they won’t be considered a duplicate.

5. Highlight Errors

There are 2 ways you can highlight Errors while clean Data on Excel:

Using Conditional Formatting

  • Select the entire data set
  • Go to Home –> Conditional Formatting –> New Rule
  • In New Formatting Rule Dialogue Box select ‘Format Only Cells that Contain’
  • In the Rule Description, select Errors from the drop down
  • Set the format and click OK. This highlights any error value in the selected dataset
Clean Data in Excel - Highlight Errors

Using Go To Special

  • Select the entire data set
  • Press F5 (this opens the Go To Dialogue box)
  • Click on Special Button at the bottom left
  • Select Formulas and uncheck all options except Errors

This selects all the cells that have an error in it. Now you can manually highlight these, delete it, or type anything into it.

Clean Data in Excel - Select Errors

6. Change Text to Lower/Upper/Proper Case

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’s a fingertip trick to bring back the consistency.

  • LOWER(text)
  • UPPER(text)
  • PROPER(text)

Steps:

  • Just type the formula you want to use, suppose “LOWER(“ and select the cell whose case needs to be changed.
  • Hit “CTRL+ENTER.”
  • The case has been changed & consistent
  • Drag down to do the same for other cells.
  • Similarly for UPPER() & PROPER()

7. Parse Data Using Text to Column

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 “Text to Column.”

Steps:

  • Select the Data
  • Go to Data> Text to Column
  • A dialogue box will appear (Convert Text to Columns Wizard – Step 1 of 3), select Delimited or Fixed Width as per your convenience.
  • Delimited is to be selected if the width isn’t fixed, click “NEXT
  • In Delimiters tick the option which separates your text in the cell. Suppose “Norwich Cathedral, Norwich, UK,” here three values are separated by commas. So we will select “Comma” for this example. And, deselect rest options.
  • View the preview & click on “NEXT
  • Select Column Data Format & destination cell address
  • Click “FINIS

8. Spell Check

Spelling mistakes are common in text files & PowerPoint. However, MS points out such errors by underlining it with colorful dashes. And, MS Excel doesn’t have such feature. But you can use it below steps to clean data on excel.

  • Select the Data
  • Press “F7
  • A dialogue box appears, which shows you the possible wrong word & it’s the possible correct spelling. Click on “Change,” if you agree with the suggestion.
  • Check & change till it says “Spell check complete. You’re good to go!

9. Delete all Formatting

In my job, I used multiple databases to get the data in excel. Every database had it’s own data formatting. When you have all the data in place, here is how you can delete all the formatting at one go:

  1. Select the data set
  2. Go to Home –> Clear –> Clear Formats

Similarly, you can clear Content, Comments, Hyperlink, or entire data (using Clear All).

Clean Data in Excel - Clear Formats

10. Use Find & Replace to Clean Data in Excel

A) Changing Cell References:

  • Press “CTRL+H” to open “Find and Replace
  • Now in Replace > “Find What” (change the reference range too) “Replace With
  • Suppose Find What: $B to Replace With: $C
  • Click on “Replace All
  • Similarly finding & replacing using reference range we can clean the Data

B) Find & Change Specific Format:

  • Press “CTRL+H
  • Select “Options
  • Now go to “Format” of “Find What.” Here you can specify the format or choose a format from the cell. Suppose you select a format.
  • Now it will show you the preview for “Find What.”
  • Click on “Format” of “Replace With.” Suppose we go for “Format…”
  • Now select format, example: Number, Alignment, Font, Border, Fill, Protection.
  • Suppose we select Color then select any color to fill the column header cell.
  • Click on Replace All
  • Instantly the format has been changed!

C) Removal of Line Breaks:

Suppose we have a data where it is separated by line breaks (same cell but different rows). To remove these line breaks, follow the below steps:

  • Press “CTRL+H
  • Find and Replace dialogue box will appear, press “CTRL+J
  • Go to the replace with box & type a single space
  • Click Replace All
  • All rows will be managed in one row within the same cell!

D) Removal of Parenthesis:

  • Select the Data
  • Press “CTRL+H
  • Type (*) in “Find What” (This will consider all characters within parenthesis)
  • Leave the Replace With column empty & click Replace
  • Parenthesis characters are removed!

Now we are on Patreon: https://www.patreon.com/pyoflife

Spread the love

55 thoughts on “10 Effective Way To Clean Data On Excel”

  1. Thank you so much regarding giving me an update on this subject on your website.
    Please be aware that if a completely new post becomes available or when any alterations
    occur about the current write-up, I would consider reading a lot
    more and learning how to make good use of those techniques you share.

    Thanks for your time and consideration of other folks by making
    your blog available.

    Feel free to visit my page :: Nouvee Skin Cream – https://yclas380.00web.net/

    Reply
  2. Excellent post. Keep posting such kind of information on your site.
    Im really impressed by it.[X-N-E-W-L-I-N-S-P-I-N-X]Hey there, You’ve done a great job.
    I’ll certainly digg it and individually suggest
    to my friends. I am sure they’ll be benefited from this web site.

    my blog post … Green Roads CBD (Mohammed)

    Reply
  3. Hey there! Quick question that’s totally off topic. Do you know how to make your site mobile friendly?
    My website looks weird when viewing from my apple
    iphone. I’m trying to find a theme or plugin that might be able to correct this issue.
    If you have any suggestions, please share. Thank you!

    My homepage :: http://www.mi77b.cn

    Reply
  4. I would like to thank you for the efforts you have put in writing this website.
    I am hoping to view the same high-grade content from you in the future as
    well. In fact, your creative writing abilities has motivated
    me to get my very own website now 😉

    My blog :: http://ibsfc.org

    Reply
  5. Right here is the right webpage for anybody who hopes to find out about this
    topic. You understand so much its almost tough to argue with you (not
    that I really will need to?HaHa). You definitely put a brand new spin on a topic which has been discussed
    for many years. Wonderful stuff, just great!

    Here is my web-site – sex technique

    Reply
  6. You truly make it seem very easy with your display screen but The other half
    and i also find this kind of matter staying actually
    something which I think We may never get pleasure from.
    It seems also complex and in addition broad in my experience.
    I am looking forward on your impending post, I will try to get
    used to it!

    Reply
  7. Nossa, que bacana hein! Tú parece ser 1 pessoa que sabe bem do tema, amei particularmente
    do que Você falou pela metade do artigo, adorei mesmo. Eu estava procurando um
    site sobre o assunto nesse tema afim de começar
    a seguir. Agradeço!

    Reply
  8. Right here is the right blog for everyone who wishes to find out about this topic.
    You know a whole lot its almost tough to argue with
    you (not that I personally would want to…HaHa).
    You definitely put a new spin on a subject that’s been written about for
    decades. Wonderful stuff, just wonderful!

    Here is my page; informatycy.info

    Reply
  9. Hi there i am kavin, its my first occasion to commenting
    anywhere, when i read this paragraph i thought i could also
    create comment due to this good paragraph.

    Reply
  10. You actually make it appear really easy along with
    your presentation however I to find this matter
    to be really something that I feel I might never
    understand. It seems too complicated and very large for me.

    I’m looking ahead for your subsequent publish, I’ll try how to burn fat get the cling of it!

    Reply
  11. Hmm it looks like your blog ate my first comment (it was extremely long) so I guess
    I’ll just sum it up what I wrote and say, I’m thoroughly enjoying
    your blog. I too am an aspiring blog writer
    but I’m still new to everything. Do you have any tips for novice blog writers?
    I’d definitely appreciate it.

    Reply
  12. We are a gaggle of volunteers and starting a brand new
    scheme in our community. Your website offered us with useful info to work on. You’ve performed a formidable process and our whole community can be grateful to you.

    Reply
  13. you are actually a just right webmaster. The
    site loading velocity is incredible. It sort of feels that you’re doing any distinctive trick.
    Moreover, The contents are masterpiece. you’ve performed a magnificent process in this topic!

    Reply
  14. I am curious to find out what blog system you have been using?
    I’m experiencing some small security problems with my latest website
    and I would like to find something more safe.
    Do you have any suggestions?

    Reply
  15. Someone necessarily lend a hand to make critically posts I’d state.
    That is the first time I frequented your website page and
    to this point? I amazed with the research you made to create this particular
    submit incredible. Great activity!

    Reply
  16. Hello very nice site!! Guy .. Excellent .. Amazing .. I will bookmark your site and take the feeds additionally? I’m glad to find a lot of helpful information here within the publish, we want work out extra strategies in this regard,thanks for sharing.

    Reply

Leave a Comment