Search

How to fix your data with Flash Fill

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:

  1. Click the File tab on the ribbon and select Options

  2. 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

  • Rearranging data

  • 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:

  1. Type your first entry sample

  2. Point to the AutoFill handle

  3. Drag down with the right mouse button

  4. 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.


#flashfill #hintsandtips #microsoft365 #Excel #fixingdata

4 views0 comments

Recent Posts

See All

AI Digital Training Ltd

+44 (0)333 772 1812

Newland House, The Point, Weaver Road, Lincoln. LN6 3QN

  • Facebook
  • YouTube
  • AI Digital Training

AI Digital Training Ltd is a socially responsible employer, and is a signee to the Lincoln Social Responsibility Charter

© AI Digital Training Ltd 2020