Entering a high volume of data into Excel can be a tedious process. Excel 2013 addressed this challenge by introducing Flash Fill, a tool that rapidly executes data entry in seconds. We’ll discuss how to use Flash Fill in Excel to make your experience as seamless as possible.

What is Flash Fill?

Flash Fill is an Excel tool that analyzes data as you enter it and automatically fills out other cells once it identifies a pattern. Flash Fill can simplify dozens of tasks that would otherwise require complex formulas to perform, such as:

  • Splitting columns
  • Merging columns
  • Cleaning data
  • Formatting numbers, dates, and text
  • Correcting inconsistencies
  • Replacing parts of cell contents

For example, you could use Flash Fill to separate complete addresses into individual street addresses, zip codes, states, and countries.

How to use Flash Fill in Excel to split data

How to use Flash Fill in Excel to split data

You can find the Flash Fill tool in the “Data” tab within the “Data Tools” group. You can also access this tool using the Excel Flash Fill shortcut, Ctrl+E.

In most cases, Flash Fill initiates automatically — all you have to do is provide it with a discernible pattern by following these steps:

  1. Insert a new column: Insert a new column beside the one containing your data.
  2. Enter a value: Type the desired value into the first cell of your new column.
  3. Fill out the next cell: If Excel identifies a pattern, when you move to the cell and begin typing, it will preview your autofill options for all proceeding cells.
  4. Accept autofill: Press the enter key to accept the autofill suggestion.

Why is Flash Fill not working in Excel?

If you get an error message stating that the Excel Flash Fill does not recognize a pattern in your values, there are several things you can do to troubleshoot the problem:

  • Fill out more cells: If Excel doesn’t sense a pattern after one example, it might detect one if you give it a few more. Try filling in more cells manually.
  • Run Flash Fill manually: If Flash Fill options aren’t appearing as you type, try running it manually by going to the “Data” tab and clicking “Flash Fill” or using the Ctrl+E Flash Fill shortcut.
  • Enable Flash Fill: If you’re using a Windows device, you might need to enable Flash Fill before you can use it. Go to “File, then ”Options.” Then, click “Advanced” and check the box that says “Automatically Flash Fill” before hitting “OK.”
See also  How to Create Random Numbers in Microsoft Excel

Improve your IT operations with Agio

You can optimize your company’s information technology (IT) operations and protect your systems with managed IT and cybersecurity services from Agio. We’re dedicated to making your technology faster, safer, and easier.

Contact us to get in touch with a member of our team!