Clean Data in Excel (11 Quick Methods)

In Microsoft Excel, some changes occur automatically after downloading or converting a spreadsheet, like structure, extra space, font size, formats, etc. Thus editing or cleaning the dataset becomes difficult. Today in this article, I will share some easy tricks to clean data in excel.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

 

 

11 Simple Methods to Clean Data in Excel

In the following, I have shared 11 easy and simple methods to clean data in excel.

1.   Remove Duplicates to Clean Data

Suppose we have a dataset of some Car Company Names and their total Sales Volume just like the following screenshot.

 

Although you will find some duplicate names in the dataset. Here I have marked those duplicates for better understanding. Now you might need to remove those duplicates to make a proper formal data table. In this method, I will show you how to remove those duplicates in excel to clean data.

Steps:

  • First, select the whole dataset and click the “Remove Duplicates” feature from the “Data Tools” option.

 

  • Second, from the appearing new window choose “My data has headers” and press OK.
  • In a simple way, all the duplicates will be removed with a few clicks.

 

 

 

 

2.   Clean Extra Spaces to Clean Data

Sometimes you will find extra spaces before or between texts providing a disgusting look to your dataset.

In order to clean data, you might need to remove extra spaces. For that, you can use the TRIM function which deletes the extra spaces from a given text string.

Steps:

  • Starting with, choosing a cell (E5) and write the formula down-

=TRIM(B5)

 

  • Hit Enter button and your data will be cleaned by removing all the extra spaces.

 

 

 

3.   Fill All Blank Cells to Clean Data

After downloading a dataset sometimes you will find blank cells which are not desired.

 

For that, I have a simple solution to clean your data. Just select the whole data table and press the F5 key.

  • At a glimpse, a new “Go to” window will appear.
  • From the new window choose “Special”.

 

 

  • Then, select “Blanks” and click OK.
  • After completing the previous steps properly, you will see all the blank cells will be selected to get your next order.
  • Hence, if you want to fill the cells with text or numbers, you just have to type it and press Ctrl+Enter to put it inside all the blank cells.
  • Here, I have put “0” inside all the blank cells.

 

 

  • Finally, we have successfully filled all the blank cell cleaning data in our excel workbook.

 

 

 

4.   Highlight Errors to Clean Data

Due to a lack of proper formula or string or values, the spreadsheet will show errors. If you start searching them one by one it will take a lot of time. Below I have explained an easy technique to find errors and highlight them so that you can find them easily.

Suppose we have a dataset with some errors in some cells. Now we will highlight them with conditional formatting and the “Go to Special” feature of excel.

 

Step 1:

  • Above all, select the whole dataset and click “New Rule” from the “Conditional Formatting” option.

 

  • Therefore, in the rule type choose “Format Only cells that contain” and then select “Errors” from the below drop-down list and press OK.

 

  • As you can see all the errors in the table are highlighted.

 

Step 2:

  • You can highlight your error cells with the “Go to Special” feature of excel. In order to open the feature click the F5 button from the keyboard selecting the whole list.
  • Simply, press “Special” for the popped-up window.

 

 

  • Then, check mark the “Errors” option and hit OK to continue.

 

  • Finally, all the cells with errors are selected so that you can clean your data quickly.

 

 

 

 

5.   Split Data Using Text to Column

In your spreadsheet cleaning data means proper data in a perfect position so that you can edit and apply formulas. Suppose we have a dataset with some Car Company Name and their Total Sales Volume in the same cell. Maybe you want to calculate the total sales of the whole car industry. Problem is that you can’t. But if you put only the values in a new cell then you can easily apply formulas to reach your destination. In this method, I will show you how to split data in a worksheet.

Steps:

  • In the first place, start with selecting the cells (B5:B12) and choosing “Text to Columns” from the “Data Tools” option in the “Data” tab.
  • Next, choose “Delimited” and press “Next” to go through other processes.

 

  • For instance, a second dialog box will appear.
  • In addition, select “Other” and put a hyphen sign (-) as our data is divided with a hyphen sign.
  • Gently press “Next”.

 

  • In this last step, you will see the data is divided into two columns in the “Data Preview” section.
  • Hereafter complete the task by pressing “Finish”.
  • Finally, our list is split into two columns without any hesitation.

 

 

 

 

6.   Check Spelling to Clean Data

Sometimes you need to clean your data with a spelling check of all the texts in the table so that your reporting boss apprises you for your perfect report. Excel does have a built-in feature to check spelling in your list of texts.

Suppose we have a dataset of some Car Company Name but the names are spelled error. Now we will not check manually all the spells but we will select the whole list of texts and run the spell check to save our time.

Steps:

  • Begin with, selecting the texts and press F7 to open the spell check option.
  • Directly, a new window will open named “Spelling”.
  • Consequently, press “Change All” several times to correct all the spelling.

 

 

  • After correcting all the spelling a confirmation box will appear confirming the changes.
  • In summary, press OK and here you have your clean data with correction of spelling within seconds.

 

 

 

7.   Remove Formatting to Clean Data in Excel

In some cases, you will find the cells formatted with colors. Fortunately, you can remove those formats quickly with a handy trick to clean your data.

Imagine you have a dataset with some color formatting in your spreadsheet. Here we will remove those colors to clean data in excel.

 

Steps:

  • In general, the data which you want to remove format select those and choose “Clear Rules from Selected Cells” from the “Clear Rules” drop-down list of “Conditional Formatting”.

 

  • On the contrary, all the cells will be cleaned removing all the formats from the cells. Now you can enjoy your clean data.

 

 

 

8.   Utilize Find and Replace to Clean Data

Instead, a spreadsheet does have some blank cells which provide a bad look to your table. Here we have a table with some blank cells inside it. Facing this we will fill all the blank cells to complete our table.

Steps:

  • In the similar fashion, choose cells (B5:C13) to find and fill all the blank cells in the range.
  • Secondly, press Ctrl+H to open the “Find and Replace” window.

 

  • Later, type your choice of words or numbers in the “Replace with” section to fill in all the blanks.
  • Thereafter, click the “Replace All” option.

 

 

  • In conclusion, the cells are filled with “Nil” as we replaced all the blank cells with the word ”Nil” maintaining clean data.

 

 

 

9.   Change Text Case to Lower, Upper, and Proper Case

Eventually, after converting a spreadsheet from other format of files some words changes automatically just like the following screenshot. If you are facing a problem with this type of problem then you don’t have to worry at all. You can use formulas to get the proper upper, lower and proper cases.

If you want to change all text to lowercase then you can try the below formula. Here I have used the LOWER function which converts all the texts to lowercase provided in the string.

Step 1:

  • Formerly, select a cell (C5) and write the below formula down-

=LOWER(B5)

 

 

  • Hit the Enter key and drag down the “fill handle” to fill all the cells.
  • On the whole, we have all the texts in a new column changing the texts to lowercase.

During working with a dataset you will also find lower and upper case words inside texts just like the below screenshot. You can change them in short.

 

Earlier we converted all the texts to lower cases but this time let’s convert texts to upper cases with the simple UPPER function.

Step 2:

  • To begin with, write the following formula in the selected cell (C5)-

=UPPER(B5)

Where,

  • The UPPER function returns upper case texts for a cell included in the string.
  • Without wasting time press Enter and pull the “fill handle” down to fill the whole column with your desired result.
  • Thus we can get our precious result by changing all the texts to upper case.

 

Presently imagine a table of texts in your spreadsheet with lots of lower and upper case words at the start, middle, and end. Don’t get frustrated! Just follow my instructions to correct all the words to the proper case within a blink of an eye.

 

Step 3:

  • First, choose a cell of your choice from your choice of row or column. Here I have selected cell (C5).
  • Now put the formula down-

=PROPER(B5)

Where,

  • The PROPER function changes all the texts to the proper case provided in the string.

 

 

  • Thereafter, click the Enter key and drag the “fill handle” down to get the column filled with your desired output.
  • Without changing manually one by one we have cleaned our data with proper cases in seconds. Simple isn’t it?

 

 

 

10.                Fix Time and Date to Clean Data

We all use Microsoft Excel to store our data in a likely manner just the way we want. But unfortunately, you will see sometimes we are not lucky with getting clean data in excel. In most cases, I found time and date are not in a proper format just like the below screenshot. In the screenshot, you will see all the dates and times are in text format.

Thus when we copy all those times and dates from our table to another it changes instantly. Yet I have a quick solution for this. Follow the steps properly

Step 1:

  • Similarly, put the formula from the next line in a cell (C5).

=DATEVALUE(B5)

Where,

  • The DATEVALUE function is converting the date to a serial number which is stored as a text format in cell (C5).

 

 

  • To finish with the formula, click the Enter button and drag the “fill handle” down.
  • Now you will get to see some serial numbers in the new column which is not our expected result. No worries! We just need to change the format only.
  • For this reason, choose the output from cells (C5:C12) and press Ctrl+1 to open the “Format Cells” window.

 

  • Then, select “Date” and choose the type of choice which you want to display in the output.
  • To finish, press OK.

 

  • Here, we have successfully fixed our time and date representing clean data in excel.

You might also face problems with not converting to your expected conversion output.

Suppose we have a dataset for some Works and Completion Time in Days. But you want to convert the days to hours so that anyone with the worksheet can understand the time barrier to completing those tasks. So what will you do? You just have to use a simple formula to get your proper output.

 

 

Step 2:

  • Inside the worksheet choose a cell (D5) and type the formula-

=CONVERT(C5,”day”,”hr”)

Where,

  • The CONVERT function converts one measurement to another measurement.
  • As we have chosen to convert our output to hours, thus we got the cell values in hours within a short time.

 

 

 

 

11.                Switch Cells to Clean Data

While working in excel you might want to change the position of the cell to a column or row-wise to make it more lucrative. But selecting every cell and copying it to a different location is a horrible decision if you are doing so. The TRANSPOSE function is the rescuer in this situation.

Suppose we have a dataset with some Monthly Sales Volume. Now we will switch and rearrange the table row-wise.

Steps:

  • Select a cell (E4) of choice and apply the formula-

=TRANSPOSE(B4:C10)

 

 

  • Hit the Enter button and the cells will be switched to row-wise from the column-wise position.

 

 

 

Remove Data Validation to Clean Data

We want to edit a cell and the editing is not happening as the cell is filled with data validation.

Suppose we have a dataset with inserted data validation so that it can’t be edited properly. Now we will remove the data validation to change the data as we want.

 

Steps:

  • Just select the cells (C5:C12) with data validation inserted and go to the “Data Validation” option from the “Data Tools” feature.

 

  • Hence, click “Clear All” to remove all the validation and press OK.
  • Finally, the validation will be removed and you can edit any cell from the range you want.

 

 

 

 

 

 

 

 

 

Things to Remember

  • Instead of using the shortcut F7 key, you can go to the spell check option from the review tab available in the worksheet of Microsoft Excel.

Conclusion

In this article, I have tried to cover all the methods to clean data in excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.

 

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo