How to Clean Up Raw Data in Excel (10 Suitable Ways)

In Excel, it is a common thing that you need to clean up your raw data. You might need to remove duplicates or find and replace some specific cells or change the casings of your letters and so on. In this article, I will show you the 10 coolest and easiest ways to clean up raw data in Excel.


Practice Workbook

You can download and practice from our workbook here.


10 Ways to Clean Up Raw Data in Excel

Go through the full article below to learn and apply 10 methods for cleaning up raw data in Excel. ๐Ÿ‘‡

1. Remove or Highlight Duplicate Cells

Say, you have a dataset of 10 personsโ€™ names and favorite sports. Now, there might be some duplicate values in your dataset. You can remove these duplicate cells in two ways described below. ๐Ÿ‘‡

Clean Up Raw Data in Excel by Removal of Duplicates


1.1 Remove Duplicates

You can directly remove duplicate cells using the Remove Duplicates tool. Follow the steps below to do this. ๐Ÿ‘‡

๐Ÿ“Œ Steps:

  • First and foremost, select all the cells that you want to check if any duplicate values are present.

Selecting the Dataset to Remove Duplicates

  • Next, go to the Data tab >> Data Tools group >> Remove Duplicates tool.

Remove Duplicates to Clean Up Raw Data in Excel

  • At this time, the Remove Duplicates dialogue box will appear. Now, click on the Select All button and subsequently, click on the OK button.

Remove Duplicates Window

  • Now, you can see a Microsoft Excel dialogue box will appear notifying you of the changes. Click on the OK button.

Microsoft Excel Dialogue Box

Finally, all your duplicate values are removed. And, the result sheet should look like this. ๐Ÿ‘‡

Removed Duplicates


1.2 Highlight Duplicates

You can also highlight duplicates using conditional formatting rather than deleting them. Go through the steps below to do this. ๐Ÿ‘‡

๐Ÿ“Œ Steps:

  • Initially, select your dataset. Subsequently, go to the Home tab >> Styles group >> Conditional Formatting tool >> Highlight Cells Rules >> Duplicate Valuesโ€ฆ

Access Conditional Formatting

  • As a result, the Duplicate Values dialogue box will appear. Here, you can choose the format in which you want to see the duplicate values. For doing this, click on the downward arrow beside the values withย option. Letโ€™s choose the Red text option from the listed options.

Set Condition and Format

  • Finally, click on the OK button.

Apply Conditional Formatting for Duplicate Values

Thus, you can see your duplicate values are formatted as red text now. For instance, the result sheet should look like this. ๐Ÿ‘‡

Highlighted Duplicate Values


2. Find and Fill All Blank Cells

You can find and fill all the blank cells using the Find & Replace tool in Excel. Suppose, you have a data set where you have 10 personโ€™s names and respective favorite sports. Two of the cells are blank and you want to fill these cells with the appropriate values. Follow the steps below to accomplish this. ๐Ÿ‘‡

Dataset Containing Blank Cells

๐Ÿ“Œ Steps:

  • Firstly, select your dataset. Following, go to the Home tab >> Editing group >> Find & Select tool >> Go to Specialโ€ฆ option from the dropdown list.

Access Go To Special Window

  • At this time, the Go to Special dialogue box will appear. Put the radio button on the Blanks option and click on the OK button.

Find Blank Cells

  • As a result, all the blank cells of your dataset will be selected.

Selected Blank Cells of the Dataset

  • Now, write the value that you want to insert inside the blank cells. Press Ctrl+Enter.

Fill Blank Cells to Clean Up Raw Data in Excel

Thus, all the blank cells will have the same value now. And, the result sheet should look like this. ๐Ÿ‘‡

Filled Blank Cells

Note:

If you press the Enter button instead of the Ctrl + Enter, then you have to write values in each individual blank cell. Here, only the active cell will have the value at each Enter button press.


3. Find & Replace in Specific Cells

3.1 Find & Replace a Character (e.g. Parentheses)

Now, you can see, that you have another column with the previous columns which is Profession. But, there is no valid profession in the column cells, rather there are parentheses by default. Now, you can remove these parentheses very easily by following the simple steps below. ๐Ÿ‘‡

Dataset Containing Parentheses: Clean Up Raw Data

๐Ÿ“Œ Steps:

  • First, select your dataset. Subsequently, go to the Home tab >> Editing group >> Find & Select tool >> Replaceโ€ฆ option from the listed options.

Access Find & Replace Tool

  • As a result, the Find and Replace window will appear now. Subsequently, insert (*) inside the Find what: text box and insert a space inside the Replace with: text box. Finally, click on the Replace All button.

Find and Replace Parentheses to Clean Up Raw Data in Excel

  • At this time, you will see a Microsoft Excel dialogue box pops up and notifying you of the changes made. Click on the OK button.

Microsoft Excel Dialogue Box

Finally, you can see that all the parentheses of your dataset have been substituted by a space. For example, the result sheet would look like this. ๐Ÿ‘‡

Parentheses Removed from the Dataset


3.2 Find & Replace Cells Format

Now, suppose you have a dataset of 10 peopleโ€™s Names, Favourite Sports, Professions, and Employment Status columns. Some data are in a 12-sizeย font and in bold format. Now, if you want to find the formatted cells and replace the format with some other format, follow the steps below. ๐Ÿ‘‡

๐Ÿ“Œ Steps:

  • First and foremost, select your dataset. Next, press the Ctrl + H button. This will bring up the Find and Replace dialogue box. Following, click on the Options > > button from the dialogue box.

Access Find & Replace Tool

  • At this time, choose the format that you want to find by accessing the Find what: optionโ€™s Formatโ€ฆ button. And, select your desired format from the Replace with: options Formatโ€ฆ button.

Find and Replace Window

  • As we want to find the 12-size Bold format text, we chose the following options from the Find Format window.

Set Format to Find and Replace

  • Last but not least, after choosing the find format and replace format, click on the Replace All button.

Replace the Format to Clean Up Raw Data in Excel

  • At this time, you will see that a Microsoft Excel dialogue box will appear. It will notify you about the changes that have been made. Now, click on the OK button.

Microsoft Excel Dialogue Box

Thus, you can find and replace the formats of your dataset. The final outcome should look like this. ๐Ÿ‘‡

Replaced Cell Format to Clean Up Raw Data in Excel


4. Remove Extra Spaces

Another way to clean up raw data in Excel is to remove the extra spaces. Sometimes, it might happen that you have faced unnecessary spaces in your cell. Suppose, in your dataset, there are unwanted spaces in the Name column. Now, you can remove these extra spaces using the TRIM functionย following the steps given below. ๐Ÿ‘‡

Dataset with Extra Spaces in Cells

The TRIM function is an Excel function that is used to remove extra spaces from a cell. It keeps only one space between each word. It has only one argument: the text. In this argument, you need to put in the text or the cell reference at which you want to remove extra space.

๐Ÿ“Œ Steps:

  • Firstly, select the D5 cell and write the following formula.
=TRIM(B5)

Insert Formula to Remove Extra Spaces and Clean Up Raw Data in Excel

  • As a result, the unnecessary spaces of the B5 column will be removed. Now, put your cursor in the bottom-right position of your cell. Consequently, a fill handle will appear. Drag it downward.

Drag Fill Handle to Copy Formula

As a result, all the extra spaces of B5:B14 cells will be removed in the D5:D14 cells. For a quick look, the result sheet will look like this. ๐Ÿ‘‡

Removed Extra Spaces to Clean Up Raw Data in Excel


5. Clear All Formatting

Now, suppose you have a default format for your dataset, but you want to clear all this default formatting and make a new format for the dataset according to your desire. To clear the formatting, follow the steps below. ๐Ÿ‘‡

Dataset with a Preset Format

๐Ÿ“Œ Steps:

  • At the very beginning, select all the cells of your dataset. Subsequently, go to the Home tab >> Editing group >> Clear tool >> Clear Formats option.

Clear Formatting to Clean Up Raw Data in Excel

Thus, you can see now that there is no formatting of your dataset anymore. It has gone back to Excelโ€™s default format of a dataset and the result sheet will look like this. ๐Ÿ‘‡

Cleared Formatting of Dataset to Clean Raw Data in Excel


6. Check and Identify Errors

To clean up raw data in Excel, another thing might happen. That is, suppose, you have a large dataset and you have some errors in your dataset. Now, you can find the errors in the following ways. ๐Ÿ‘‡

6.1 Use Conditional Formatting

You can use conditional formattingย to accomplish this. Follow the steps below to do this. ๐Ÿ‘‡

๐Ÿ“Œ Steps:

  • First and foremost, select your dataset. Next, go to the Home tab >> Conditional Formatting tool >> New Ruleโ€ฆ option.

Access Conditional Formatting Tool

  • Asa result, the New Formatting Rule window will appear. Select the option Format only cells that contain. Following, choose the option Errors from the Format only cells with: dropdown list. Next, click on the Formatโ€ฆ button.

Set Rule for Conditional Formatting

  • At this time, the Format Cells window will appear. Here, go to the Fill tab and choose your desired color for your error cell highlighter. Click on the OK button.

Choose Format for Error Cells

  • Now, you will see the New Formatting Rule window has come again and it is showing you the Preview of your error cell format. Finally, click on the OK button.

Apply Conditional Formatting to Clean Up Raw Data in Excel

Thus, the errors of your dataset are highlighted now successfully. And, It should look like this now. ๐Ÿ‘‡

Dataset with Highlighted Errors to Clean Up Raw Data in Excel


6.2 Use Go To Special Dialogue Box

Besides, you can also use the Go to Special dialogue box to find your errors. Just follow the steps below to achieve this. ๐Ÿ‘‡

๐Ÿ“Œ Steps:

  • ย First, select your dataset. Subsequently, go to the Home tab >> Editing group >> Find & Select tool >> Go To Specialโ€ฆ option from the dropdown list.

Access Go To Special Tool

  • At this time, the Go To Special dialogue box will appear. Next, put the radio button on the Formulas option. Following, deselect all the options without the Errors option. Finally, click on the OK button.

Find Errors Using Go To Special Window to Clean Up Raw Data in Excel

As a result, you can find all the errors in your dataset as you can see that all of them are selected at a time. For instance, the outcome will look like this. ๐Ÿ‘‡

Dataset with Selected Error Cells


7. Check Spelling

You can also check if you have any spelling errors in your dataset. Follow the steps below to achieve this. ๐Ÿ‘‡

๐Ÿ“Œ Steps:

  • Initially, select the dataset. Next, go to the Review tab >> Spelling tool.

Find Spelling Error of Dataset to Clean Up Raw Data in Excel

  • As a result, the Spelling window will appear. It will show your spelling error and give you suggestions for the solution. Following, select the suggestion that you actually are looking for and click on the Change button.

Spelling Errors and Suggestions

  • After a full spelling check of your dataset, a Microsoft Excel dialogue box will appear showing you the message of spelling check completion. Click on the OK button.

Microsoft Excel Dialogue Box

Thus, all cells of your dataset are free from spelling errors now. Your outcome should look like this. ๐Ÿ‘‡

Spelling Error Free Dataset

Spell Checking Shortcut: F7


8. Change Case of Letters

In order to clean up raw data in Excel, you can change the cases of your letters in your dataset too.

8.1 Change to Uppercase

To make your letters uppercase, you can use the UPPER function. Follow the steps below to achieve this. ๐Ÿ‘‡

The UPPER function is an Excel function that uppercases the letters of a cell. It has only one argument: the text. It takes in the text and makes it uppercased.

UPPER Function Syntax

๐Ÿ“Œ Steps:

  • Firstly, select the D5 cell where you want to put the uppercased name. Following, write the formula below.
=UPPER(B5)

Insert Formula to Uppercase Text to clean Up Raw Data in Excel

  • As a result, you will get the uppercased text of the B5 cell. Now, put your cursor in the bottom right position of your cell, and consequently, a black fill handle will appear. Drag it downward to copy the formula for all the cells.

Drag Fill Handle to Copy Formula

Thus, all your names will be in uppercase now. For instance, the outcome should look like this. ๐Ÿ‘‡

Dataset with Uppercase Texts


8.2 Change to Lowercase

Besides, you can lowercase the letters of a cell using the LOWER function. Use the steps below to do this. ๐Ÿ‘‡

The LOWER function is an Excel function that lowercase the letters of a text. It has only one argument, that is text. It takes the text and makes it lowercase.

LOWER Function Syntax

๐Ÿ“Œ Steps:

  • Initially, click on the D5 cell and insert the following formula.
=LOWER(B5)

Insert Formula to Lowercase Text to Clean Up Raw Data in Excel

  • Now, you will get the B5 cell as lowercase text. Following, place your cursor in the bottom right position of your cell. Consequently, the fill handle will appear. Now, drag it downward to copy the formula for all the cells below.

Drag Fill Handle Below to Copy Formula

Consequently, you can lowercase the names of your dataset. And, the outcome should look like this. ๐Ÿ‘‡

Dataset with Lowercase Texts


8.3 Change to Propercase

Moreover, you can propercase your dataset using the PROPER function. Go through the following steps to do this. ๐Ÿ‘‡

The PROPER function is an Excel function propercases words of a text. It requires only one argument: text. IN this argument you need to give the cell reference or text that you want to propercase.

PROPER Function Syntax

๐Ÿ“Œ Steps:

  • Firstly, select cell D5 and write the following formula in the formula bar.
=PROPER(B5)

Insert Formula to Propercase Text to Clean Up Raw Data in Excel

  • As a result, you can get the propercase name of the first person. Next, place your cursor in the bottom right position of your cell. You can see a fill handle will appear. Drag it downward to copy the formula for all the cells.

Drag Fill handle Below to Copy Formula

Thus, you can propercase every name of your dataset. For example, it would look like this. ๐Ÿ‘‡

Dataset with Propercase Texts


9. Split Text in One Cell to Multiple Columns

Another thing, you can split your text to columns for various purposes. It is very handy sometimes. Say, you have a dataset of the full names of several persons. Now, you can split text to column and so get their first and last name in different columns.

Split Text To COlumns to Clean Up Raw Data in Excel

๐Ÿ“ŒSteps:

  • First and foremost, select all the names of your dataset that is B5:B14 here. Subsequently, go to the Data tab >> Data Tools group >> Text to Columns tool.

Access the Split Text To Columns Tool

  • As a result, the Convert Text to Columns Wizard dialogue box will appear. Put the radio button on the Delimited option and click on the Next button.

Adjust Settings for Splitting Text

  • Following, another Convert Text to Columns Wizard dialogue box will appear. Here, tick only the option Space and click on the Next button afterward.

Select Where to Split Texts

  • Last but not least, another Convert Text to Columns Wizard dialogue box will appear. Choose the Column data format as General and insert your destination at the Destination text box. Finally, click on the Finish button.

Set Destination to Place Split Text to Clean Up Raw Data in Excel

Thus, you can see that you have split the B column cell text to D and E columns. And the result sheet will look like this. ๐Ÿ‘‡

Split Text to Columns to Clean Up Raw data in Excel


10. Convert Numbers in Text Format Back to Numbers

Now, suppose you have the dataset as Name, Favorite Sports, and Annual Income Columns. But, the Annual Income columnโ€™s data are left-aligned which means they are in the text format. It will create problems when calculating with these cells. Now, you can convert these numbers back to number format through the following steps. ๐Ÿ‘‡

Dataset with Numbers in Text Format

๐Ÿ“Œ Steps:

  • Initially, click on a random cell and write 1. Say, we select the F4 cell and write 1.

Fill a Random Cell with 1

  • Now, copy the F4 cell by pressing the Ctrl+C.

Copy the Cell

  • Next, select the D5:D14 cell where you want your conversion. Subsequently, right-click on your mouse and choose the Paste Specialโ€ฆ option from the context menu.

Access Paste Special Window

  • As a result, the Paste Special window will appear. Following, choose the Multiply option from the Operation group. Finally, click on the OK button.

Convert Numbers back to Number Format to Clean Up Raw Data in Excel

Thus, you can see that the Annual Income cell values are right-aligned now. So, you can tell they are in number format now. As an example, the result sheet would look like this. ๐Ÿ‘‡

Dataset with Number Format


Conclusion

In brief, in this article, I have shown you the 10 most effective and necessary ways to clean up raw data in Excel with examples. I would suggest you read the full article and practice on your own from our given practice workbook. It would definitely enhance your knowledge and expertise regarding this objective. I hope you find this article helpful and informative. Furthermore, if you have any queries or recommendations, feel free to comment here.

And, visit ExcelDemy for many more articles like this. Thank you!

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo