Excel's Best Data Cleaning Tricks (Updated 2023)

In this video, we will use Go To (Special) feature to learn -

1. How to detect / delete / color cells with errors

2. How to detect / delete / color cells that contain hard-coded number or text

3. How to use 'Column difference' to detect the inconsistent formula inputs

4. How to use 'Show Formula's feature to see all the formulas of a worksheet

In this video, we will learn how to use Go To (Special) feature with the shortcut key Ctrl + Enter to fill the blank cells intelligently from the cells above.

In this video, we will learn about Go To (Special) Feature and will learn the following:

1. How to select & color only the visible cells of a filtered dataset

2. How to delete objects (shapes, images, and charts)

3. How to select those cells having comments or notes

In this video, we will learn about FlashFill feature (Basic to Intermediate) -

1. How to specify sample output / pattern to get the desired result

2. Best practices - Extract numbers from text using ' (apostrophe sign)

3. Best practices - Show >1 samples

4. Best practices - Keep no blank column(s) between input data vs. sample output

In this video, we will work on a complex bank detail dataset. We will use multiple techniques to clean the data -

1. FlashFill with ' (apostrophe sign)

2. SUBSTITUTE() formula

3. Paste Special Value & Text to Columns

In this video, we will work on an electricity consumption logbook dataset. We will use multiple techniques to clean the data -

1. FlashFill

2. Find & Replace with wildcard character asterisk *

3. TEXT() formula

Then, we will analyze the clean data to make a visual Pivot Table dashboard with heatmap, slicer and sparklines.

In this video, we will learn about the basics of Find and Replace -

1. Shortcut key

2. Options - Match Case, Match entire cell content

In this video, we will learn about some advanced Find and Replace using wildcard characters such as

1. asterisk or * - implies many characters

2. question mark or ? - implies a single character

In this video, we will be learning two bonus tricks of Advanced Find and Replace -

1. How to neutralize the effect wildcard characters using ~ (tilde)

2. Remove Any Digit or Letter using MS Word's Find & Replace

Pro Tip -

In this video, we will learn about How to convert dates with invalid format to a valid one using Text to Columns.

Sometimes, when you write a formula and press enter, the formula just "hangs" in there without giving any result. In this video, we will learn about how to troubleshoot that.