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.
But many business users don’t need such advanced features. If you’re looking for a few quick tricks to clean up your spreadsheets, keep reading. Using a few simple commands, you can eliminate blank cells, merge or split columns, deduplicate data, and more in no time.