Data Cleaning

One extra zero or a misplaced decimal can skew an entire report. Learn to find outliers and data entry errors with formulas, conditional formatting and data validation.

A trailing space or a hidden character is enough to make VLOOKUP fail or let duplicates slip through. Learn how to find and remove them, with and without formulas.

If SUM returns 0 or VLOOKUP can’t find a code you can see on screen, you probably have numbers stored as text. Here’s how to spot and convert them.

Learn how to use Power Query in Excel to clean tables, merge files and refresh reports with one click, without writing formulas.

Three ways to split names, addresses and codes into several columns: Text to Columns, Flash Fill and formulas such as TEXTBEFORE or TEXTSPLIT.

Learn to find and remove duplicates in Excel with the built-in feature, conditional formatting, formulas and online tools, and how to decide which record to keep.

Ways to delete blank rows in Excel without losing data: Go To Special, filters, sorting, formulas and online tools for large files.

A practical checklist to clean any spreadsheet before analyzing, importing or sharing it: from duplicates to dates.

A guide to joining several Excel workbooks or sheets into a single file: copy and paste, Power Query, online tools and how to prepare the data before merging.

Techniques and formulas to normalize data in Excel: trim spaces, standardize case, convert text dates, standardize phone numbers and codes.