How to clean data in Excel using Flash Fill.
Watch the video below:
How to use Flash Fill feature in Microsoft Excel.
Flash Fill fills your data automatically when it recognizes a pattern. Flash Fill is available in Excel 2013 and later. Let’s enter the first name and last name of employees in column A. Many of the names are not capitalized. Some are partly capitalized and the data looks haphazard. We enter the one of the names in the column B next to the name in the column A in the correct fashion. For example in Range A2 we have the name ‘john doe’. In the Range B2 we write ‘John Doe’.
Next we select as many cells in Range B as in Range A, go to the Fill item in the Editing group and click on Flash Fill. Voila! Our data of names is filled in the same manner as John Doe, i. e. the first and last name are properly capitalized in range B2.
Under normaol citcumstances Flash Fill generates a preview. If it doesn’t generate the preview, it might not be turned on. We can go to Editing > Fill > Flash Fill in Excel 365 to run it manually as we did in the video , or we can press Ctrl+E. To turn Flash Fill on, we can go to File > Options > Advanced > Editing Options > check the Automatically Flash Fill box. This is the 9th item from the top.