8 ways to clean data in excel (part 1)
Posted: Wed Dec 04, 2024 8:06 am
You can't always have control over the format and type of data you import from a database, text file, or web page. Before you can analyze the data, you often need to do a good cleanup.
Fortunately, Excel has many features to get the data in the exact format we want. Sometimes the task is simple and other times it requires more steps. Below we will see 8 ways (4 in this first part) to clean data in Excel .
1. Spelling check
We can use a spell checker not only to find spelling errors, but also to find values new zealand mobile phone number list that are not used consistently, such as product or company names, by adding these values to a custom dictionary.
Use the following instructions to check spelling and grammar in Excel 2007.
Open the Spelling and Grammar Checker. On the Review tab , in the Proofing group, click Spelling . If the program finds spelling errors, the first misspelled word or grammatical error is highlighted. The options you see vary slightly depending on which version you're using and whether the error is a spelling or grammatical error.
Use the following instructions to check spelling and grammar in Excel 2010 and later for Windows.
To start the spelling and grammar check on the file press F7 or follow these steps:
1- Click the Review tab on the ribbon.
2- Click on Spelling or Spelling and Grammar .
3- If the program finds spelling errors, a dialog box will appear with the first misspelled word found by the spell checker.
4- Once you have decided how to resolve the spelling error (omit, change or add to the program's dictionary), the corrector will move on to the next incorrect word.
Use the following instructions to check spelling in Excel for Mac
Remember that you can check spelling, but you cannot check grammar. To check all spelling at once:
On the Review tab , click Spelling .
Note: The spelling dialog box will not open if no spelling errors are detected or if the word you are trying to add already exists in the dictionary.
Do one of the following:
For Perform this procedure
Change the word Under Suggestions , click the word you want to use, then click Change .
Change all occurrences of this word in the document Under Suggestions , click the word you want to use, and then click Change All .
Skip this word and move on to the next misspelled word. Click skip once.
Skip every occurrence of this word in the document and move on to the next misspelled word. Click Skip All.
2. Remove duplicate rows
Duplicate rows are a common problem when importing data. It's a good idea to first filter for unique values to confirm that the results are what you want, before removing duplicate values.
Filtering unique values and removing duplicate values are two similar tasks, as the goal is to present a list of unique values. However, there is one critical difference: when filtering unique values, duplicate values are only temporarily hidden. However, when removing duplicate values, duplicate values are permanently removed.
Check before removing duplicates: Before removing duplicate values, it's a good idea to try filtering out unique values, or conditional formatting, to confirm that you're getting the results you expect.
Filter unique values
Follow these steps:
1- Select the range of cells or make sure the active cell is in a table.
2- Click > Advanced Data (in the Sort & Filter group).
3- In the Advanced Filter pop-up message box , do one of the following:
To filter the range of cells or table instead:
Click on filter the list, in context.
To copy filter results to another location:
Click Copy to another location.
In the Copy to box , type a cell reference.
Alternatively, click Collapse Dialog to temporarily hide the pop-up window, select a cell in the worksheet, and then click Expand .
Check only unique records , and then click OK .
Unique values in the range will be copied to the new location.
Remove duplicate values
When you remove duplicate values, the effect is only on the values in the cell range or table. Other values outside the cell range or table are not changed or moved. When you remove duplicates, the first occurrence of the value in the list is kept, but other identical values are removed.
Because it permanently deletes data, it's a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values.
Fortunately, Excel has many features to get the data in the exact format we want. Sometimes the task is simple and other times it requires more steps. Below we will see 8 ways (4 in this first part) to clean data in Excel .
1. Spelling check
We can use a spell checker not only to find spelling errors, but also to find values new zealand mobile phone number list that are not used consistently, such as product or company names, by adding these values to a custom dictionary.
Use the following instructions to check spelling and grammar in Excel 2007.
Open the Spelling and Grammar Checker. On the Review tab , in the Proofing group, click Spelling . If the program finds spelling errors, the first misspelled word or grammatical error is highlighted. The options you see vary slightly depending on which version you're using and whether the error is a spelling or grammatical error.
Use the following instructions to check spelling and grammar in Excel 2010 and later for Windows.
To start the spelling and grammar check on the file press F7 or follow these steps:
1- Click the Review tab on the ribbon.
2- Click on Spelling or Spelling and Grammar .
3- If the program finds spelling errors, a dialog box will appear with the first misspelled word found by the spell checker.
4- Once you have decided how to resolve the spelling error (omit, change or add to the program's dictionary), the corrector will move on to the next incorrect word.
Use the following instructions to check spelling in Excel for Mac
Remember that you can check spelling, but you cannot check grammar. To check all spelling at once:
On the Review tab , click Spelling .
Note: The spelling dialog box will not open if no spelling errors are detected or if the word you are trying to add already exists in the dictionary.
Do one of the following:
For Perform this procedure
Change the word Under Suggestions , click the word you want to use, then click Change .
Change all occurrences of this word in the document Under Suggestions , click the word you want to use, and then click Change All .
Skip this word and move on to the next misspelled word. Click skip once.
Skip every occurrence of this word in the document and move on to the next misspelled word. Click Skip All.
2. Remove duplicate rows
Duplicate rows are a common problem when importing data. It's a good idea to first filter for unique values to confirm that the results are what you want, before removing duplicate values.
Filtering unique values and removing duplicate values are two similar tasks, as the goal is to present a list of unique values. However, there is one critical difference: when filtering unique values, duplicate values are only temporarily hidden. However, when removing duplicate values, duplicate values are permanently removed.
Check before removing duplicates: Before removing duplicate values, it's a good idea to try filtering out unique values, or conditional formatting, to confirm that you're getting the results you expect.
Filter unique values
Follow these steps:
1- Select the range of cells or make sure the active cell is in a table.
2- Click > Advanced Data (in the Sort & Filter group).
3- In the Advanced Filter pop-up message box , do one of the following:
To filter the range of cells or table instead:
Click on filter the list, in context.
To copy filter results to another location:
Click Copy to another location.
In the Copy to box , type a cell reference.
Alternatively, click Collapse Dialog to temporarily hide the pop-up window, select a cell in the worksheet, and then click Expand .
Check only unique records , and then click OK .
Unique values in the range will be copied to the new location.
Remove duplicate values
When you remove duplicate values, the effect is only on the values in the cell range or table. Other values outside the cell range or table are not changed or moved. When you remove duplicates, the first occurrence of the value in the list is kept, but other identical values are removed.
Because it permanently deletes data, it's a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values.