{"id":10273,"date":"2019-06-03T08:30:49","date_gmt":"2019-06-03T08:30:49","guid":{"rendered":"http:\/\/www.digitalresponse.es\/?p=10273"},"modified":"2019-06-03T08:30:49","modified_gmt":"2019-06-03T08:30:49","slug":"8-formas-de-limpiar-datos-en-excel-parte-1","status":"publish","type":"post","link":"https:\/\/www.digitalresponse.es\/en\/8-formas-de-limpiar-datos-en-excel-parte-1\/","title":{"rendered":"8 ways to clean data in excel (part 1)"},"content":{"rendered":"
You cannot always have control over the format and type of data you import from a database, a text file or a web page. Before we can analyse the data, we often need to do a good cleaning. <\/p>\n\n\n\n
Fortunately, Excel has many features to get the data in the exact format we want. Sometimes the task is simple and sometimes it requires more steps. In the following we will take a look at 8 ways (4 in this first part) to clean data in Excel<\/strong>.<\/p>\n\n\n\n
1. Spelling check<\/strong><\/h2>\n\n\n\n
We can use a spell checker not only to find misspellings, but also to look up values that are not used consistently, such as product or company names, by adding these values to a custom dictionary.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Use the following instructions to check spelling and grammar in Excel 2007. <\/strong><\/li><\/ul>\n\n\n\n
Open the spelling and grammar checker. In the Check<\/strong>in the review group, click on Spelling<\/strong>. If the programme finds spelling errors, the first misspelled word or grammatical error is highlighted. The options you see vary slightly depending on which version you are using and whether the error is a spelling or grammatical mistake.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Use the following instructions to check spelling and grammar in Excel 2010 and later for Windows.<\/strong><\/li><\/ul>\n\n\n\n
To start the spelling and grammar check on the file press F7 or follow these steps:<\/p>\n\n\n\n
<\/p>\n\n\n\n
1- Click on the tab\u00a0Check<\/strong>\u00a0from the ribbon.<\/p>\n\n\n\n
<\/p>\n\n\n\n
2- Click on Spelling or Spelling and grammar<\/strong>. <\/p>\n\n\n\n
<\/figure>\n\n\n\n
<\/p>\n\n\n\n
3- If the programme finds spelling mistakes, a dialogue box will appear with the first misspelled word found by the spellchecker.<\/p>\n\n\n\n
<\/p>\n\n\n\n
4- When you have decided how to resolve the spelling mistake (omit, change or add to the programme's dictionary), the checker will move on to the next incorrect word.
<\/p>\n\n\n\n
Use the following instructions to spell check in Excel for Mac<\/strong><\/li><\/ul>\n\n\n\n
Remember that you can check spelling, but you cannot check grammar. To check all spelling at once:<\/p>\n\n\n\n
<\/p>\n\n\n\n
In the tab Check<\/strong>click on Spelling<\/strong>.<\/li><\/ol>\n\n\n\n
Note: <\/strong>The dialogue box spelling<\/strong> will not open if no spelling errors are detected or if the word you are trying to add already exists in the dictionary. <\/p>\n\n\n\n
<\/p>\n\n\n\n
Do one of the following:<\/p>\n\n\n\n
For<\/strong><\/td>
Perform this procedure<\/strong><\/td><\/tr>
Change the word<\/td>
At Suggestions<\/strong>Click on the word you want to use and then click on Change<\/strong>.<\/td><\/tr>
Change all occurrences of this word in the document<\/td>
At Suggestions<\/strong>Click on the word you want to use, and then click on Change everything<\/strong>.<\/td><\/tr>
Omit this word and move on to the next misspelled word.<\/td>
Click on omit once.<\/strong><\/td><\/tr>
Omit each occurrence of this word in the document and move on to the next misspelled word.<\/td>
Click on Omit everything.<\/strong><\/td><\/tr><\/tbody><\/table>\n\n\n\n
2. Remove duplicate rows<\/strong><\/h2>\n\n\n\n
Duplicate rows are a common problem when importing data. It is a good idea to first filter by unique values<\/strong> to confirm that the results are what you want, before removing duplicate values.<\/p>\n\n\n\n
Filter out single values<\/strong> y remove duplicate values<\/strong> are two similar tasks, since the objective is to present a list of unique values. However, there is a critical difference: when filtering unique values, duplicate values are only temporarily hidden. However, when removing duplicate values, duplicate values are permanently removed.<\/p>\n\n\n\n
Check before removing duplicates:<\/strong> Before removing duplicate values, it is a good idea to try filtering for unique values, or conditional formatting, to confirm that you get the results you expect.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Filter out single values<\/strong><\/li><\/ul>\n\n\n\n
Follow these steps:<\/p>\n\n\n\n\n\n
1- Select the range of cells or make sure that the active cell is in a table.<\/p>\n\n\n\n<\/figure>\n\n\n\n
<\/p>\n\n\n\n
2- Click on > advanced data (in the group sort & filter). <\/p>\n\n\n\n
<\/p>\n\n\n\n
3- In the pop-up message box advanced filter<\/strong>If you do not do so, do one of the following:<\/p>\n\n\n\n
To filter the range of cells or the table instead:<\/strong><\/p>\n\n\n\n
Click on filter the list, in context. <\/li><\/ul>\n\n\n\n
To copy the filter results to another location:<\/strong><\/p>\n\n\n\n
Click Copy to another location.<\/li>
In the table Copy to<\/strong>enter a cell reference.<\/li><\/ul>\n\n\n\n
Alternatively, click collapse from dialogue<\/strong> to temporarily hide the pop-up window, select a cell in the spreadsheet, and then click on expand<\/strong>.<\/li>
Check only unique records<\/strong> and then click on Accept<\/strong>.<\/li><\/ul>\n\n\n\n
The unique values of the range will be copied to the new location.<\/p>\n\n\n\n
When removing duplicate values, the only effect applies to values in the range of cells or table. Other values outside the range of cells or table do not change or move. When duplicates are removed, the first occurrence of the value in the list is retained, but other identical values are removed. <\/p>\n\n\n\n
Because it permanently deletes data, it is a good idea to copy the original range of cells or table to another spreadsheet or workbook before removing duplicate values.<\/p>\n\n\n\n
Follow these steps:<\/p>\n\n\n\n
<\/p>\n\n\n\n
1- Select the range of cells or make sure that the active cell is in a table.<\/p>\n\n\n\n
<\/p>\n\n\n\n
2- In the tab data<\/strong>click on remove duplicates<\/strong> (in the tools group of data<\/strong>). <\/p>\n\n\n\n<\/figure>\n\n\n\n
<\/p>\n\n\n\n
3- Carry out one or more of the following procedures:<\/p>\n\n\n\n
At Columns<\/strong>select one or more columns.<\/li>
To quickly select all the columns, click on Select all<\/strong>.<\/li>
To quickly delete all the columns, click on Unselect<\/strong>. If the range of cells or table contains many columns and you only want to select some of them, you will find it easier to click on Unselect<\/strong> and then in Columns<\/strong>select these columns. <\/li><\/ul>\n\n\n\n
<\/p>\n\n\n\n
Note:<\/strong> The data will be removed from all columns, even if you do not select all columns in this step. For example, if you select Column1 and Column2, but not Column3, the \"key\" used to search for duplicates is the column value Column1 & Column2. If a duplicate is found in those columns, the entire row will be removed, including other columns in the table or range.<\/p>\n\n\n\n
<\/p>\n\n\n\n
4- Click on Accept<\/strong> and a message will appear to indicate how many duplicate values have been removed or how many unique values are retained. Click on Accept<\/strong> to close the message.<\/p>\n\n\n\n
<\/p>\n\n\n\n
5- If you do so, click unDo (or press Ctrl + Z on your keyboard).<\/p>\n\n\n\n
3. Find and replace text<\/strong><\/h2>\n\n\n\n
You may want to remove a common initial string, such as a tag followed by a colon and a space, or a suffix, such as a secondary phrase at the end of the string that is obsolete or unnecessary. To do this, you can search for instances of such text, and then replace it with no text or other text.<\/p>\n\n\n\n
Use the features Search and replace<\/strong> in Excel to search for items in your workbook, such as a particular number or text string.<\/p>\n\n\n\n
<\/p>\n\n\n\n
1- In the tab Home<\/strong> in the group Edition<\/strong>click on Search and select<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\n
<\/p>\n\n\n\n
2- Follow one of these procedures:<\/p>\n\n\n\n
To search for text or numbers, click on Search<\/strong>.<\/li>
To search for and replace text or numbers, click on Replace<\/strong>.<\/li><\/ul>\n\n\n\n
<\/p>\n\n\n\n
3- In the table Search<\/strong>If you want to search, type in the text or numbers you want to search for, or click on the arrow in the box. Search<\/strong> and then click on a recent search in the list.<\/p>\n\n\n\n
You can use wildcard characters, such as an asterisk (*) or a question mark (?), in your search criteria:<\/p>\n\n\n\n
Use the asterisk to search for any character string. For example, s*l will return both \"salt\" and \"signal\".<\/li>
Use the question mark to search for a single character. For example, it will return \"salt\" and \"sun\". Suggestion<\/strong>You can search for asterisks, question marks and tilde characters (~) in spreadsheet data by prefixing a tilde character in the Search box. For example, to search for data containing \"?\", you would type ~? as the search criteria.<\/li><\/ul>\n\n\n\n
<\/p>\n\n\n\n
4- Click on Options<\/strong> to further define the search if necessary:<\/p>\n\n\n\n
To search for data in a spreadsheet or an entire workbook, in the Within box, select Sheet or Book<\/strong>.<\/li>
To search for data in rows or columns, in the Find box, click on By rows or By columns<\/strong>.<\/li>
To search for data with specific details, in the Search within box, click on Formulas, Values or Comments<\/strong>.<\/li><\/ul>\n\n\n\n
<\/p>\n\n\n\n
Note:<\/strong> Formulas, values, notes and comments are only available on the Find tab; only formulas are available on the Replace tab.<\/p>\n\n\n\n
<\/p>\n\n\n\n
To search for case-sensitive data, select the checkbox Matching upper and lower case letters<\/strong>.<\/li>
To search for cells that contain only the characters you typed in the Find box, select the Match contents of the whole cell<\/strong>.<\/li><\/ul>\n\n\n\n
<\/p>\n\n\n\n
5- To replace text or numbers, type in the new text or number in the box\u00a0Replace with<\/strong>\u00a0(or leave the box blank so as not to replace the characters with anything), and then click on\u00a0Search<\/strong>\u00a0o\u00a0Search all<\/strong>. Note:\u00a0<\/strong>If the table\u00a0Replace with<\/strong>\u00a0is not available, click on the tab\u00a0Replace<\/strong>.
If you wish, you can cancel an ongoing search by pressing ESC. <\/p>\n\n\n\n
<\/p>\n\n\n\n
6- To replace the highlighted match or all the matches found, click on Replace<\/strong> o Replace all<\/strong>.
<\/p>\n\n\n\n
4. Changing the capitalisation of text<\/strong><\/h2>\n\n\n\n
Sometimes text is a mixture, especially when case is concerned. By using one or more of the three case functions, you can convert text to lower case, such as email addresses, upper case, such as codes, or upper or lower case, such as first or last names.<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.digitalresponse.es\/wp-content\/uploads\/2019\/06\/Ortografia-u-Ortografia-y-gramatica.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.digitalresponse.es\/wp-content\/uploads\/2019\/06\/ordenar-y-filtrar.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.digitalresponse.es\/wp-content\/uploads\/2019\/06\/datos_quitar_duplicados.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.digitalresponse.es\/wp-content\/uploads\/2019\/06\/buscar-y-seleccionar.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.digitalresponse.es\/wp-content\/uploads\/2019\/06\/columna_b.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.digitalresponse.es\/wp-content\/uploads\/2019\/06\/nomprop-a2.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.digitalresponse.es\/wp-content\/uploads\/2019\/06\/rellenar_hacia_abajo.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.digitalresponse.es\/wp-content\/uploads\/2019\/06\/seleccionar_valores_nueva_columna.png\")
<\/figure>\n\n\n\n