Clean Data in Excel to ensure the accuracy, clarity, and effectiveness of your data analysis. This essential process not only improves the quality of your information but also enhances your decision-making capabilities. By dedicating time to cleaning your data, you eliminate errors, standardize formats, and remove redundancies, paving the way for more reliable and insightful outcomes. Embrace the practice of data cleaning to transform your Excel spreadsheets into powerful, precise, and trustworthy tools for any analytical task.
This Tutorial Covers:
- Way 1- Get Rid of Extra Spaces
- Way 2- Select and Treat All Blank Cells
- Way 3- Convert Numbers Stored as Text into Numbers
- Way 4- Remove Duplicates
- Way 5- Highlight Errors
- Way 6- Change Text to Lower/Upper/Proper Case
- Way 7- Parse Data Using Text to Column
- Way 8- Spell Check
- Way 9- Delete all Formatting
- Way 10- Use Find and Replace to Clean Data in Excel
1. Way 1- Get Rid of Extra Spaces:
It is very tough to find empty areas. The extra spaces between words or numbers may be noticeable, but the trailing spaces are not. Use the TRIM function for a clever solution to remove extra space.
Syntax:
=TRIM(text)
The cell reference (or text) is the input for the Excel TRIM function. It eliminates the extra spaces between words as well as the leading and trailing spaces (except single spaces).
2. Way 2- Select and Treat All Blank Cells
If untreated, blank cells have the potential to cause chaos. A data set that is utilized to generate reports and dashboards frequently has blank cells, which causes me problems.
You could wish to simply highlight it or fill in all empty cells with “0” or “Not Available.” This could take hours to complete manually if there is a large data set. Fortunately, there is a method for selecting every blank cell at once.
You can use the “Go to Special” technique to quickly select blank cells in Excel and highlight them.
Procedure of selecting and highlighting blank cells in Excel:
Step 1: Simply select the data to start, outlined in Red below.
Step 2: Hit the F5 key on the keyboard. The “Go To” dialog box will appear as a result. After that select the “Special” button in the “Go To” dialog box, outlined in Red below.
Step 3: Choose Blanks from the “Go To Special” dialog box and the click ok, outlined in Red below.
After clicking ok, with all the blank cells selected and the result looks like below.
This chooses all of your data set’s empty cells. Simply type “0” or “Not Available” in each of these boxes and click Control + Enter (remember if you press only enter, the value is inserted only in the active cell).
3. Way 3- Convert Numbers Stored as Text into Numbers:
Numbers may occasionally be stored as text when you import data from text files or external databases. Additionally, some people have a practice of prefacing numbers with an apostrophe (‘) to make them text. If you use these cells in calculations, this could lead to major problems.
Here is a foolproof method to turn these text-based numbers back into numbers.
Step 1: Fill in 1 in any empty cell. Press Control + C to copy the contents of the cell where you wrote 1.
Step 2: Choose the cell or range that you want to turn into numbers.
Step 3: To access the “Paste Special” dialog box, use the keyboard shortcuts “Alt + E + S.” Select Multiply in the Paste Special Dialogue box (in operations category). Select OK.
This changes all of the text-based numbers back to numbers.
4. Way 4- Remove Duplicates
If you have duplicate data, you can either highlight it or delete it.
Finding and spotting duplicate text is made simpler by conditional formatting. However, Excel is unable to draw attention to duplicate entries in the PivotTable report’s Values section.
The procedure of identifying and removing duplicates using the conditional format in Excel:
Step 1: Start by choosing the cells whose values you want to compare for duplication.
Step 2: Go to “Home” tab, then select “Conditional Formatting”, the go to “Highlight Cells Rules’ and then select “Duplicate Values”, outlined in Red below.
Step 3: The Duplicate Values option will prompt you with a popup box once you click it.
Select the formatting for the duplicate values from the drop-down menu in the box next to the “values with” option. then press OK, outlined in Red below.
Step 4: After clicking ok, the result looks like below.
Step 5: Now, click on any cell or a certain range in the dataset that contains duplicates that you wish to eliminate.
Remember that all duplicate data will be permanently erased before using the remove duplicates tool. It is therefore advised to duplicate the original data to a new worksheet in order to prevent the unintended loss of any information.
Step 6: Under “Data” under the Developer tab, select “Remove Duplicates”, outlined in Red below.
Step 7: As demonstrated below, a dialog window displays. The columns you want to compare and look for duplicate data in can be chosen. Check or uncheck the columns where you want to eliminate duplicates under Columns.
If your data contains column headers, check the box next to “My data has headers,” then click OK, outlined in Red below.
The first row will not be considered for removing duplicate values when the header option is checked.
Step 8: The duplicate rows will now be deleted by Excel, and a dialog box will appear. Along with the count of unique values, the dialog box displays a summary of how many duplicate values were discovered and eliminated.
Step 9: After clicking ok, observably, the duplicate records have been eliminated.
5. Way 5- Highlight Errors
Errors in Data in Excel can be highlighted in two different ways:
Using Conditional formatting:
Step 1: Choose the whole set of data.
Step 2: Click “Conditional Formatting” under “Home” tab. Afterward, select “New Rule.”
Step 3: Select “Format Only Cells that Contain” in the dialog box for a unique formatting rule.
Select Errors from the drop-down menu in the Rule Description, then set the format and click OK. This draws attention to any incorrect values in the chosen dataset.
The result looks like below:
Using Go To Special:
Step 1: Choose the whole set of data.
Step 2: “F5” will bring up the Go To dialogue box. Select “Special…” Button by clicking there.
Step 3: All but “Errors” should be unchecked when choosing “Formulas”.
This chooses every cell that contains an error. You can now manually highlight them, remove it, or enter any text.
6. Way 6- Change Text to Lower/Upper/Proper Case
There are frequently inconsistent names or titles when you inherit a worksheet or import data from text files. The text may occasionally be all lowercase, all uppercase, or a combination of both. Utilizing these three features, you can simply ensure consistency throughout:
LOWER() – Converts all text into Lower Case.
UPPER() – Converts all text into Upper Case.
PROPER() – Converts all Text into Proper Case.
7. Way 7- Parse Data Using Text to Column
It’s possible that all the text is crammed into one cell when you import data from a text file or a database. Using Excel’s Text to Column feature, you can divide this text into a number of cells.
Step 1: Choose the text or data you want to parse.
Step 2: Toggle to the “Data” tab. Select the “Text to Columns” option under the “Data Tools” group.
Step 3: Choosing the data type (select Delimited if your data in not equally spaced, and is separated by characters such as comma, hyphen, dot…). Select Next
Step 4: Choose Delimiter (the character that separates your data). Using the Other option, you can choose a pre-defined delimiter or anything else.
Step 5: Decide on a data format. Additionally pick the desired cell. The current cell is rewritten if the destination cell is not chosen.
The result looks like below:
8. Way 8- Spell Check
A spelling error degrades the credibility of your work more than anything.
Run a spell check on your data set by pressing the F7 shortcut on the keyboard.
The steps to spell check are described below:
Step 1: Select the dataset.
Step 2: Press F7 on your keyboard to run spell check. If it has error, then it will show like below.
To correct the error, choose your suggestion and click “Change” to correct word from incorrect word.
If it has no error, then it will show like below:
9. Way 9- Delete all Formatting
In my line of work, I obtained the data in excel by using several databases. Data formatting varied from database to database. Here’s way to remove all the formatting at once after you have all the data in place:
The steps to remove all formatting in Excel are described below:
Step 1: Select the data set.
Step 2: Click “Clear” icon under “Editing” section after switching to the “Home” tab. Select “Clear Formats” after that.
You can also choose to only delete comments, links, or content.
After that, the result looks like below:
10. Way 10- Use Find and Replace to Clean Data in Excel
When it comes to cleaning up data, find and replace is essential. For instance, you can identify and adjust formatting, find and eliminate all zeros, change references in formulas, and more.
I demonstrate the “Find and Replace” tool in this tutorial.
Following are the instructions for adding a line break and removing the comma from the address using “Find and Replace”:
Step 1: Pick out the cells where you wish to insert a line break in place of the comma.
Step 2: Select the “Home” tab. Click “Find and Select” in the “Editing” group, then click “Replace” (you may also use the keyboard shortcut Control + H to do this). The “Find and Replace” dialog box will then be displayed.
Step 3: In the “Find What” field of the “Find and Replace” dialog box, type a comma (,).
Step 4: Use the keyboard shortcut CONTROL + J while the cursor is in the “Replace Field” (hold the Control key and then press J). In the field, this will insert a line feed. After pressing Control + J, the field can show a blinking dot.
Step 5: Select Replace AL and then click OK. Make sure that Wrap text is turned on.
The comma is removed in the procedure above, and it is replaced with a line feed.
These are the top 10 methods I use to clean data in Excel. I’m hoping this tutorial has taught you something useful.
Clean Data in Excel
Application of Clean Data in Excel
For ready-to-use Dashboard Templates:
