Updated: Jul 23, 2020
Welcome to the first in our Spotlight series of blogs where we will be highlighting some useful but perhaps lesser known features of your Microsoft desktop applications. This month our focus is on Excel.
Do you have an Excel spreadsheet where the data is not quite how you want it to look? Maybe you've imported data from another program and you want to combine or split up the data, or you want to remove punctuation? Well the good news is that you don't have to be an Excel ninja and spend hours creating complicated formulas - all you need is Flash Fill.
Flash Fill is a brilliant time-saving tool that lets you combine, extract or transform data based on a couple of examples. Once it recognises the pattern you want, it can fill in the rest of the data for you.
Turning on Flash Fill
Before you can use Flash Fill, you need to check that the feature is turned on:
Click the File tab on the ribbon and select Options
Select the Advanced tab, then make sure Automatically Flash Fill is ticked
How does it work?
We've put together a short video so you can see Flash Fill in action:
What else can you use Flash Fill for?
Here are some examples you might want to try:
Extracting a name from an email address
Data cleansing such as removing spaces and punctuation
Change the case to uppercase or lower case
Extract numbers from a text string
Flash Fill Rules and Limitations
Although Flash Fill is a powerful feature, there are a few things you need to be aware of:
The examples you are typing need to be in the column immediately to the right of the source data - you can't leave any blank columns
Each example needs to be on the same row as the data it's based on
Flash Fill can only fill downwards from where you start to type your examples, it can't fill upwards
Flash Fill can only fill down a column, not across a row
Flash Fill is not dynamic - this means that if you change the original data values, the filled data will not automatically update, (but you can always run Flash Fill again if you need to).
If your data is too complex, Excel may not be able to find a pattern
If you make a typo, Flash Fill may not work
Did you know?
You can also use the AutoFill handle to Flash Fill:
Type your first entry sample
Point to the AutoFill handle
Drag down with the right mouse button
When you release the mouse, select Flash Fill from the shortcut menu
So what are you waiting for? Give Flash Fill a try today and see how easily you can fix YOUR data.