Just a heads up, if you buy something through our links, we may get a small share of the sale. It’s one of the ways we keep the lights on here. Click here for more.
You probably know poorly prepared information can lead to inaccurate results, making it more difficult for you to trust your data or urge others to do so. Data cleaning means correcting any errors and removing instances of incomplete data.
You should always clean data carefully, but it need not be an extremely drawn-out process in every case. Here are six tricks you can try to clean your data efficiently.
Clean whole columns in a separate sheet
Many people break down large spreadsheets into manageable chunks to make the data cleaning process less overwhelming and help them focus on certain sections. If you decide to work on one column at a time, make a new sheet first. Then, copy one column into the new sheet and clean the data.
After cleaning it, you can copy the column back into the initial sheet and replace the old column when you do.
This tip allows you to avoid accidentally using a function and applying the results to the entire spreadsheet instead of only the column you’re working on presently. If you’re working in Excel, insert a worksheet by clicking the plus (+) icon at the bottom of the screen, or select the Insert menu on the Home tab and choose Insert Sheet.
Change the date format in Google Sheets
In Europe, the preferred date format is DD/MM/YY, but in the United States, people more commonly write dates in the MM/DD/YY format. You can imagine how confusing it could be if people accustomed to more than one date format work on a shared spreadsheet in Google Sheets without agreeing on a system first.
Fortunately, Google Sheets allows you to change the date format, preventing the need to make manual alterations. By default, the date format is the one typically used in the spreadsheet’s locale. But, you can set a custom date format instead.
Highlight the data to format first, then go to Format > Numbers > More Formats. Finally, choose More Date and Time Formats. From there, you can either pick from the formats shown or make your own.
Tidy up data imported into Tableau from other programs
If Tableau is your data visualization program of choice, there’s good news when dealing with information imported from elsewhere, such as Google Sheets or Excel. A Data Interpreter feature captures the data from the imported program and examines it for things that need cleaning, such as empty cells.
Use the Connect pane in Tableau to locate data in a supported format, such as .csv, .pdf or Google Sheets. Drag the table over to the canvas if needed, then look for the Data Source area on the left side of the screen. Then, in the Sheets section of the Data Source section, put a check in the Use Data Interpreter box.
You can see the outcome in the Data pane by choosing Review the Results. Doing that opens the information in Excel, including a key for interpreting how Data Interpreter handled the data.
Remove old data in Excel
It’s crucial to keep your data as current as possible, especially if you rely on lead lists while engaging customers. One of the easiest things to do is get rid of incorrect emails or subscribers who no longer engage with your brand.
You can delete entire rows of old data by right-clicking on the respective row and selecting Delete. Or, click on a cell, then use the Ctrl + A keyboard command to highlight the data. Then, press the Delete key to clear the cell and enter updated material.
Delete extra spaces in Excel with the Trim formula
Excel has various formulas that help you avoid lengthy processes. One of them is called Trim. It takes out all spaces except the ones between words. Paste =TRIM(text) into the Formula box at the top of the Excel screen.
Next, be sure to replace the word “text” in parentheses with the content to trim. You can also insert a column and row combination containing the text, such as B3.
Screen for misspelled words with Google’s Spell-Check feature
When you work with data in Google Sheets or Google Docs, it’s a good idea to look for typos. Even the most careful data entry professionals have accuracy lapses due to things like tiredness or trying to work too fast.
You can look for typos from the Tools menu by selecting the Spelling option. The interface then gives you the option to ignore possible spelling errors or fix them by using suggested replacement words. There is also an Add to Dictionary option that works well for words you use often that Google incorrectly flags as mistakes.
Make sure not to put blind trust in the spell checker, especially within the context of your project. For example, the tool may suggest you change a cell containing the word “Foxe” to “fox,” but that may not be the right thing to do if the word is a person’s surname.
Stick to Your System and Share It With Others When Applicable
These six tips will get you off to a good start with cleaning your data. Once you come up with procedures that work for you, it’s smart to adhere to them every time you need to take a closer look at your data and clean it for correctness.
If several people have access to the data and may need to prepare it for future uses too, provide them with the techniques you use so you’re all using the same processes.
Plan on using any of these tips? Have any to add? Let us know down below in the comments or carry the discussion over to our Twitter or Facebook.
Editors’ Recommendations:
- Common problems with Android updates and how to fix them
- Here’s how to update Google Chrome on basically any device
- Walmart is testing an AI-powered superstore in New York
- Lyft will get you to the grocery store in more cities for just $2.50, roughly
- Verizon opens up pre-orders for Samsung’s Galaxy S10 5G, plus a 20-city 5G rollout
Follow us on Flipboard, Google News, or Apple News
