Use Find & Replace To Clean Data In Excel
A) Changing Cell References:
- Press CTRL+H to open Find and Replace
- Now in Replace > Find What Replace With
- Suppose Find What: $B to Replace With: $C
- Click on Replace All
- Similarly finding & replacing using reference range we can clean the Data
B) Find & Change Specific Format:
- Select Options
- Now go to Format of Find What. Here you can specify the format or choose a format from the cell. Suppose you select a format.
- Now it will show you the preview for Find What.
- Click on Format of Replace With. Suppose we go for Format
- Now select format, example: Number, Alignment, Font, Border, Fill, Protection.
- Suppose we select Color then select any color to fill the column header cell.
- Instantly the format has been changed!
C) Removal of Line Breaks:
Suppose we have a data where it is separated by line breaks . To remove these line breaks, follow the below steps:
Ibm Infosphere Quality Stage
IBM Infosphere Quality Stage is one of a broader selection of data management tools from IBM. It focusesas the name suggestson data quality and governance. While it deals with the usual suspects it is specifically designed to clean big data for business intelligence purposes. For this purpose, it has about 200 in-built data quality rules, saving analysts tonnes of time managing these tasks manually with scripts. Whats more, its key features all support otherwise labor-intensive tasks such as data warehousing, master data management, and migration. Deployed either in-house or in the cloud, the tool also offers a deep level of data profiling. You can use it to explore the content, quality, and structure of data from a broad database view, or drill down to granular details, analyzing individual columns, for instance. While it might not be the best tool for those without some technical know-how, it does offer a useful data quality scores feature. This allows any user to get a general sense of a datasets integrity. This is a very useful feature for executive-level stakeholders.
In This Tutorial You Will Learn The Top 8 Techniques On Data Cleaning In Excel
So what is data cleansing?
In the words of Oz du Soleil, Excel MVP, it is like Hercules being sent out to capture the three-headed dog that guards the entrance to hell!
So you can see that Oz sees that data cleansing is hard work but one that needs to be done in order to succeed in Excel.
We get thrown data at us from external accounting systems, ERPs or downloads from the company server and most of the time it is not imported in an Excel friendly way. This is where you and I come in to clean the data! You can listen to our podcast episode which was highly entertaining and full of golden nuggets by clicking on belows image and further down I will show you the My Top Data Cleansing Techniques, which include the ones that Oz uses:
Read Also: Best Cleanser For Mature Sensitive Skin
Rotate Or Switch Cells
Sometimes while importing or exporting data in Excel, structured data in tabular format changes to unstructured data in nontabular format.
In order to transform data from columns to rows and rows to columns, use the =TRANSPOSE function. Follow the below steps:
In this way, you can easily rearrange or transform columns and rows in your Excel.
Data Cleaning Tools In Microsoft Excel
Microsoft MVP | Power BI, Excel, VBA, Financial Modelling, Data Analytics, Corporate Finance & Valuation Expert
Yesterday, I held a webinar on Data Cleaning in Excel. Well, there were some technical glitches but I think I have managed to find out the root cause and way around the issues to prevent similar glitch in future ones.
You can watch the video of the webinar here, might want to fast forward the glitched part.
As a compensation, I will be taking you through the crux of what I shared in the webinar with plenty easy to follow screenshots.
1. Text to Column
Sometimes, you copy data from other applications into Excel and they don’t get arranged the way you want. Maybe you copied customer data records with first name, last name and email and instead of Excel putting them in different columns/fields, you get them sandwiched into one field. How do you easily remedy that?
Well, I am happy to introduce you to Text to Column.
To fix this, go to Data Menu, Text to Column and in a matter of 3, 4 clicks you are done. Even for a hundred thousand records table.
2. TRIM
Got text data with the spacing irregularly done? No worries, meet TRIM.
It can turn records that look like this:
Into this pleasant looking one:
3. PROPER and UPPER
Got text or names written with all lower case or terrible mix of the cases? Time to fix them looking Proper or all in UPPER.
4. Go To Special, Blanks
There is something definitely special about Go To Special beyond its name.
5. Removing Duplicates
Recommended Reading: Gut Cleanse For Weight Loss
Winpure Clean & Match
A bit like Trifacta Wrangler, the award-winning Winpure Clean & Match allows you to clean, de-dupe, and cross-match data, all via its intuitive user interface. Being locally installed, you dont have to worry about data security unless youre uploading your dataset to the cloud. This is an especially important feature for Winpure, which is specifically designed for cleaning business and customer data . Winpure Clean & Match also interoperates with a very wide variety of databases and spreadsheets, from CSV files to SQL Server, Salesforce, and Oracle. Other useful features include fuzzy matching and rule-based cleaning that you can program yourself. Its available in four different languages, too: German, English, Portuguese, and Spanish. The free version offers a good number of features, making it an ideal option for small businesses. Maybe one to recommend to your boss!
Flash Fill Pack Size From Product Name
Flash fill is a powerful predictive functionality in Excel. It can sense a logical pattern that you are trying to do and automatically apply the pattern to an entire column.
To demonstrate this, suppose we would like to extract the pack sizes out of the product names in column F. We can see that the pack sizes, for example, 175g, 150g, 210g and so on come at the very end of each product name. Therefore, we can leverage flash fill to recognise this pattern and subsequently fill a new column with the pack sizes from the corresponding product.
Practically speaking, to initiate flash fill, we have to first type in the first pack size which in this case is 175g and proceed to the next row. As you start typing 150g for the next product name, you should see an option pop up where Excel is trying to fill the whole column with all the pack sizes. Simply press Enter on your keyboard to apply the flash fill.
Alternatively, if the option doesnt come up, press Ctrl+E, which is the keyboard shortcut for flash fill.
Recommended Reading: Lancer The Method Polish Cleanse & Nourish 3 Piece Kit
Use Trim For Data Cleaning In Excel
- Lets continue with the above example, so you have additional space in Cell A, and suppose you are looking to get the cleaned data in next cell. Which is Cell B in this scenario. So all you have to do is to add the formula =TRIM on the next cell.
- Next to implement this function for multiple cell at once. You dont have to write the function on each and every cell, you just have to select the cell where function already added. As per this example this is Cell#B. So select Cell#B and drag the fill handle down upto the range you need. Bellow is an example what you can expect after using this function.
Extract The First Word From Product Name
Suppose we dont really care about the flavour of the product but instead, we only want the brand name. The easiest way to do this is to extract the first word out of each product name. To action this, we need two separate functions: left and find.
Left returns the specified number of characters from the start of a text string. Find, on the other hand, returns the starting position of one text string within another text string.
The formula which combines the use of these two functions is as follows:
LEFT-1)
In plain English, the formula basically says find the position before the first space character, and return all the characters from product name up until that position. As a result, we get the following:
Recommended Reading: Glymed Plus Cell Science Mega Purifying Cleanser
How To Clean Data In Excel Part : Fixing Each Column
We can fix the ZIP Code formatting by selecting the whole column with Shift + Ctrl + Down, pressing Ctrl + 1, and then going to Number, Special, and Zip Code:
The City and State columns have extra spaces at the beginning and end, as well as improper capitalization. To fix these issues, we can use the PROPER and TRIM functions for the City and UPPER and TRIM for the State:
We can then copy both these formulas, select to the bottom with Shift + Ctrl and the arrow keys, and then use Alt, E, S, F to paste these as formulas:
Now, we want to copy and paste these asvalues this is because we want to delete the old data.
When we delete that old data, these formulas will stop working, so we need to paste everything as values first so that our spreadsheet saves these new, correct values.
Then, we can cut and paste these over the original, poorly formatted data with Ctrl + X and Ctrl + V:
Next, we can select these three columns with Shift + Ctrl + arrow keys and then use the Alt, O, C, A shortcut to auto-fit the column widths based on the widest item in each column:
Finally, to fix the Street Address column, we can use the PROPER function to capitalize each word correctly. We do not need TRIM because these names do not have extra spaces.
Once again, we enter PROPER, copy and paste the formula down with Ctrl + C and Alt, E, S, F, and then copy and paste values with Alt, E, S, V:
Highlight Errors On Excel Based On Condition
On the others methods too we have use conditional formatting, even for this case we have to use conditional formatting to format cell or text based on condition.
- Select a specific range of data or the entire sheet.
- Navigate to Home> Conditional Formatting> New Rule
- Now choose Format Only Cells that Contain in the dialog box.
- Select Errors from the dropdown in the Format only cells with.
- Now all you can set the format by clicking on the Format button.
- Finally click OK. Now any error on the sheet will be highlighted.
Recommended Reading: Yes To Tomatoes Detoxifying Charcoal Cleanser
Csv: Comma Separated Value
CSV files may be the most widely distributed data files from governments and can be read into almost every spreadsheet application. The data is a text file where each cell has been separated by a comma and each row ends in a paragraph return. The comma is a delimiter. The file above would look like this:
Data Parsing From Text To Column
Sometimes, there is a possibility that one cell might have multiple data elements separated by a data delimiter like a comma. For example, consider that there is one column that stores address information.
The address column stores the street, district, state, and nation. Commas separate all the data elements. You must now divide the street, district, state, and nation from the address columns into separate columns.
Excel’s inbuilt functionality called “text to column” can achieve this. Now, try an example for the same.
Here, you have the car manufacturer and the car model name separated by space as the data delimiter. The tabular data is shown below.
Select the data, click on the data option in the toolbar and then select “Text to Column”, as shown below.
A new window will pop up on the screen, as shown below. Select the delimiter option and click on “next”. In the next window, you will see another dialogue box.
In the new page dialogue box, you will see an option to select the type of delimiter your data has. In this case, you need to select the “space” as a delimiter, as shown below.
In the last dialogue box, select the column data format as “General”, and the next step should be to click on the finish, as shown in the following image.
The final resultant data will be available, as shown below.
Followed by Data parsing, in this tutorial about Excel Data Cleaning, you will learn how to delete all formatting.
Business Analyst Master’s Program
Also Check: Avenova Lid And Lash Cleanser
Power Query Tools And Features
Here are a few of the tools and features in Power Query that are better than their counterparts in Excel.
Fill Down: If you have a column thats in outline formfor example, East Region is in cell A2 followed by blank cells in that column until the start of Central Region in row 200, followed by more blanks until West Region in row 500you can quickly fill in the blanks using Fill Down.
Remove Rows: If the incoming data is double-spaced, the Remove Rows menu has an option to Remove Alternate Rows. The command is flexible, allowing you to specify something such as keep row 2, delete the next three rows, then keep one row, and follow that pattern through the data.
Split Column: This command allows you to specify that the split should happen at the change from text to numeric characters or vice versa. If you have part numbers like A123, BC234, or DEF111, this built-in split command will handle it easily.
Extract menu: This menu lets you choose all text after a delimiter, before a delimiter, or between two delimiters.
Information menu: Excel has functions to detect ISEVEN, ISODD, and SIGN, but youll find easier-to-use tools in the Information menu to perform the same actions.
Index column: You can quickly add an index column starting from either 1 or 0.
Some other useful commands not shown in Figure 2 are the unpivot command as well as the abilities to split a cell at each delimiter and to split the data to new rows.
Find And Replace Text
Addressing blank cells is another common objective when cleaning data in Excel. If not addressed, these many read as errors or will not be included in your end analysis if they were originally intended to signify 0.
Read Also: Kidney Cleanse Global Healing Center
Ways To Clean Data In Excel
Our definitive guide will show you 15 ways to clean data in Excel. Learn more about data cleansing through useful examples.
Clean data and string manipulations in Excel are critical! Besides the well-known techniques, we will introduce special VBA codes to make the process quicker and more effective.
Select Fields To Cleanse
Select the fields to cleanse with the configuration options below. Use the All link to select all fields and use the None link to deselect all fields.
String Data Types
All options, except for Replace Nulls with 0, apply to string data types. To specify different options for different fields, use multiple Data Cleansing tools in your workflow.
Also Check: Beach Body 21 Day Cleanse
Based On A Range Of Cell Select All Blank Cells
Does your excel database contain blank or empty cell? And you are filling those blank cell manually? Checkout this tricks to select all blank cell at a time, and you can fill all of those blank cell with 0 or Not Available or just to highlight with a colour background.
- Either you can select any specific data range in excel or You can select the entire worksheet based on your requirement)
- Now open the Go To dialog box, press F5 to open this.
- See the bottom left side on the dialog box click on Special button. It will take you to Go To Special dialog box.
- Choose Blanks and click on OK.
- After completion Step#4, all of the blank cell will be in select mode. Now if you want to add any specific text or numeric number on all of the selected cell, just type it and press Ctrl + Enter Key
Remove Duplicates To Clean Data
Excel supports many options to eliminate duplicates. For example, two tightly joined operations filter unique values and remove duplicated data. The result is the same in both cases: a list of unique values.
To filter for unique values , use the Advanced command on the Data tab in the Sort & Filter group.
Select the range of cells! Then go to the Data tab! In the Sort & Filter group, click Advanced. Next, choose the Unique records only check box and .
Explanation: However, there is one significant difference that we cannot ignore. In the process of filtering unique values, it temporarily hides the duplicated values. You can back to your original list to undo this operation.
Youll permanently delete duplicates if you use the Remove Duplicates tool on the ribbon! Pay attention to this! The removed, deleted items will last after saving the Workbook. So be very careful.
Let us see how the duplicate removal values tool works! First, select the range. Then, go to the Data tab! Then, in the Data Tools group, click Remove Duplicates.
Select one or more columns, then click OK. A message box will appear and shows how many duplicated values are removed and how many unique ones remain.
Finally, read this definitive guide to learn more about removing duplicates.
Read Also: Fresh Start 21 Day Cleanse