Excel contains a new tool that is brilliant to help clean data in Excel 2013. It is so brilliant that the only way is to show you what it can do.
It is called Flash Fill and what Excel does is it look at your existing data, looks at a sample of what you want, and then completes the rest of the column WITH NO FORMULA REQUIRED.
These and other useful tips and tricks are part of our live and online training courses.
Table of contents
- Split a name and surname based on the upper and lower case letters
- Add Capital Letters to names
- Create a complex name from multiple columns
- Split the name and surname
- Create email addresses from a list
- Format phone numbers
- Change date formats from American to European or back
- Extract a date from report headings
- Not Working? Give Flash Fill more information
Split a name and surname based on the upper and lower case letters
We showed how to do this recently using the CODE function. But watch below as the Flash Fill tool identifies that the name and surname are split with the capital letter on the surname and then does the rest for you.
For more on how to use Flash Fill see the YouTube video below
Add Capital Letters to names
Have you received a list of names but all in lower case? You can use a function like PROPER but watch as Flash Fill identifies the trend and completes the list.
Create a complex name from multiple columns
Do you need to take a persons first, second and surname and combine them into a first name, initial and surname? Flash Fill can do this. Note that where there is no second name Excel has left it blank. You can now enter what should happen in that case and Excel will apply that same rule to the rest
Split the name and surname
Split the name and surname in a cell into 2 different columns? We teach you how to use FIND, LEFT, RIGHT and MID to do this but watch Flash Fill do its thing
Create email addresses from a list
Create email addresses from a list of names based on the naming convention
Format phone numbers
Don’t like the format of your phone numbers but it is too much effort to retype them all. Flash Fill can do this
Change date formats from American to European or back
Have you been given dates in the incorrect way and Excel is not recognising them. Flash fill will fix this once is knows what you want.
Extract a date from report headings
Want to pull out a date from some text? Note that the dates are in different positions within the text but once Excel knows what you want it pulls out the key information.
Not Working? Give Flash Fill more information
If the fill does not make sense it means the Excel has not fully understood what you want. By providing another example Excel will be able to identify the trend and complete the list. Note below that after the first entry it thinks you only want the 1-9-2011. When you provide the second entry Excel understands what you want and completes the process. Give Excel more examples to make Flash Fill more accurate.
As with everything, you must check what the results look like, but Flash Fill should significantly reduce the amount of work you need to do.