8 simple ways to clean data with Excel

Data is rarely properly formatted and structured when you import it. Try these eight tips for fast data cleanup in Microsoft Excel.

Data comes in from an increasing number of sources these days. For decades, data stores were primarily built by good old data entry. But today we cull data from a range of sources including IoT devices, social media feeds, email, and other sources outside of traditional database platforms. That means the data isn’t always free of errors, blank spaces, or junk characters, and it may be inconsistently formatted from source to source.

If you work with data, at some point you will have the unenviable task of cleaning it. Data cleaning, or cleansing, is a method to get rid of syntax errors, typographical errors, and broken or fragmented records; remove duplicate records; and/or reformat data so it’s easier to work with. It’s also a means to extract specific bits of data from a larger set.

The good news is you have a tool at your disposal for automating much of the process, and that is Microsoft Excel. One obvious way to clean up data in Excel is to use the Find and Replace function, but Excel does a whole lot more.

Excel 2016 and later include a powerful set of tools for sourcing and managing data called Get & Transform. (These tools are also available for Excel 2010 and 2013 as a downloadable add-in called Power Query.) If you’re serious about importing and working with data, it’s well worth your time to investigate Get & Transform/Power Query.

To continue reading this article register now

9 steps to lock down corporate browsers
  
Shop Tech Products at Amazon