Excel data cleaning is one of the most valuable skills you can master as a working professional. If you’re a working professional, you have most likely had to work on a spreadsheet or two (thousand) at some point during your career. But, like many of us, you may have also avoided them like the plague. It can feel hard to catch up, especially if your coworkers... excel at Excel.
SpireTech is starting a new, ongoing series of educational Excel articles. We’re a Managed IT Services provider and we love making tech approachable for anyone. If you’re curious about how we can help your business, you can book a free and virtual IT consultation.
This series is for the absolute beginners to Excel. As we go on, we will build on this topic and before you know it, you’ll be an Excel wizard too.
Like me, you probably have found that using Excel can be frustrating at times. Other times, however, it can be a major thrill when that formula you have been writing for the last three hours finally works, or maybe you get that one thing right that was previously out of your reach.
The History of Excel
I am of the opinion that learning Excel is a journey that never ends. For me, it has been close to 39 years. And it can still trip me up!
Did you know that the first version of Microsoft Excel was not built for Windows?
The first electronic spreadsheet program was VisiCalc, developed by Dan Bricklin and Bob Frankston. It was released in 1979 for the Apple II computer and became a bestseller, revolutionizing how people handled calculations.
Lotus 1-2-3 was introduced on January 26, 1983, for the IBM PC. It was marketed as a “three-in-one” tool that combined spreadsheet calculations, database functions, and graphical charting. It quickly became the dominant spreadsheet software of the 1980s.
Microsoft Excel was first released in 1985 for the Macintosh, and later in 1987 for Windows. Excel quickly gained popularity because of its intuitive interface and advanced features, eventually surpassing Lotus 1-2-3 in the market.
Evolution of Spreadsheets
| Product | Release Date | Platform(s) | Notable Fact |
|---|---|---|---|
| VisiCalc | 1979 | Apple II | First electronic spreadsheet |
| Lotus 1-2-3 | Jan 26, 1983 | IBM PC | First killer app for IBM PC |
| Microsoft Excel | 1985 (Mac) | Macintosh | First GUI-based spreadsheet |
| Microsoft Excel | 1987 (Win) | Windows | Expanded PC market share |
These milestones mark the evolution from the first spreadsheet on a personal computer to the modern, widely used Excel we know today.
Starting with Data Cleaning (Because Garbage In, Garbage Out)
Today we’re going to overcome the old adage, “Garbage in, garbage out.” Garbage in, clean data out.
If you want to analyze things easily, your data needs to be clean. Unfortunately, imported data can come in messy. There might be extra spaces, inconsistent formatting, duplicates, and mysterious errors.
Here are some ways to quickly fix that.
How to Read Excel Formulas
A quick note on how these formulas work: when you see something like =TRIM(A1), A1 is a cell reference. It points to the cell in column A, row 1 of your spreadsheet. The formula processes whatever is in that one cell. To apply it to a whole column, you'd type the formula in the cell next to your first data point, say B1, then select that cell, grab the small square in the bottom-right corner of the cell, called the fill handle, and drag it down through the rest of your data. Excel automatically adjusts the reference for each row: B2 will process A2, B3 will process A3, and so on. You can also double-click the fill handle and Excel will auto-fill down to the last row of adjacent data.
How to Clean Data in Excel
1. TRIM()
TRIM() removes extra regular spaces. If you’ve copied data from another source and formulas aren’t matching correctly, things like leading spaces, trailing spaces, or repeated spaces between words may be the problem. =TRIM(A1) removes extra regular spaces while keeping single spaces between words.
2. CLEAN()
CLEAN() removes many non-printable characters. Data imported from web pages or legacy systems sometimes carry invisible characters that break formulas. =CLEAN(A1) strips those out. Combine it with TRIM for a solid one-two punch: =TRIM(CLEAN(A1)). That removes many common spacing and non-printing-character problems in one go.
3. SUBSTITUTE()
SUBSTITUTE() fixes inconsistent formatting. If your data has inconsistent dashes, slashes, or other characters, =SUBSTITUTE(A1, "-", "/") replaces every instance of one character with another. This is especially useful for standardizing phone numbers, dates, or account codes.
4. Remove Duplicates
Remove duplicates in seconds. Select your data range, go to Data > Remove Duplicates, and Excel will let you choose which columns to check. This is great for consolidating client lists, transaction records, or inventory data.
5. Text to Columns
Text to Columns splits messy data. If you've got full names in one column, but need first and last names separated, or addresses that need to be broken into components, Text to Columns, under the Data tab, is your friend. You can split by delimiter, such as comma or space, or by fixed width.
6. Flash Fill
Flash Fill learns patterns. Type an example of what you want in the first cell, start typing the second, and Excel will often suggest the rest. Press Enter to accept. Flash Fill can extract, combine, and reformat data without writing a single formula. It's not perfect, but when it works, it feels like magic. You can also press Ctrl + E to trigger Flash Fill.
7. Find & Replace
Find & Replace deals with wildcards. Most people know Ctrl + H for Find & Replace in Excel, but you can use wildcards too. Using * matches any number of characters while ? matches a single character. If you need to search for an actual asterisk or question mark, use a tilde before it, like ~* or ~?.
For example, say your product codes are a mix of PROD-001, PRODUCT-001, and PRD-001. Searching for PR*-001 would match all three. Or if you have client IDs like A1B and A2B and want to find any single-character variation in the middle, searching for A?B would catch them. It’s handy for cleaning up messy product codes or inconsistent naming conventions.
Welcome to How to Use Excel with SpireTech
This is only the first in a series of Excel tutorials. Next month we’ll cover more functions and how to avoid common formula errors in Excel. If you have any business needs, we have solutions. If you have time for a quick consultation, click the button below. We’ll walk through what’s slowing you down and what would actually help.
Additional Excel Learning Resources
- W3Schools | Excel Tutorial
- Easy Excel - Excel Tutorial on the Net | Excel Easy
- Microsoft Excel function list | Microsoft Support
- Mr Excel YouTube Videos | MR-Excel - YouTube
Excel Data Cleaning FAQs
Q: What is the fastest way to clean data in Excel? A: The fastest way is to combine TRIM() and CLEAN() in a single formula: =TRIM(CLEAN(A1)). This removes extra spaces and non-printable characters in one step.
Q: How do I remove duplicates in Excel? A: Select your data, go to Data > Remove Duplicates, choose which columns to check, and click OK.
Q: What's the difference between TRIM() and CLEAN()? A: TRIM() removes extra regular spaces. CLEAN() removes non-printable characters. Use both together for comprehensive cleaning.
Q: Can I use Flash Fill on large datasets? A: Flash Fill can work with large datasets, but performance may decrease in very large workbooks. For larger data, use formulas or the Remove Duplicates feature. For repeatable data-cleaning processes, formulas or Power Query are often better choices.
Q: How do I use wildcards in Find & Replace? A: Use * for multiple characters and ? for a single character. For example, search for PR*-001 to find PROD-001, PRODUCT-001, or PRD-001.