How to Use Flash Fill and Autofill in Excel Step-By-Step

Manually performing hundreds of similar entries in Excel can be frustrating. How about a feature that automatically makes these entries for you?

Yes, you heard that right – Excel’s Flash fill and Autofill features can do the job for you. These smart features sense the pattern of entries and fill the next cells in the same pattern.

These are very powerful tools and can help deal with tons of data in seconds. To study them in detail, continue reading the guide below👇

Also, to tag along with the guide, download our sample workbook here.

How to use Flash Fill to split cell content

The Flash Fill feature of Excel is super useful when it comes to splitting large datasets. It lets you separate tons of entries in a mere second✂

How? Let’s see that below.

Say we have a data set that contains the names of a company’s employees 🏥

Data set written adjacent to blank cells.

There are around 150 employees, and we want to separate their first and last names into two columns.

Instead of typing them manually, we will use Flash fill formatting to autofill all these names for us.

Pro Tip!

Even though the Flash fill feature can fill series automatically, we still need to type in the first name. This sets a pattern for Excel to understand and automate the remaining task for us.

In the image below, we typed in the First name of the employee in column B.

The second name wasn’t even complete yet, and Excel picked up the pattern✌

Entering names similar to the power query editor.

At this point, simply hit Enter to enable the fill handle. And Excel will auto-fill the options present in the first column. Like this:

Entering name in first cell brings up other names.

You can do the same using the Flash Fill option from the Data Tools group under the Data tab.

Alternatively, you may use the keyboard shortcut CTRL + E to access Flash Fill.

Kasper Langmann, Microsoft Office Specialist

Type in the first word in the “First Name” column, go to the Data Tab, and press Flash Fill.

Select icon of flash fill.

Do the same for the Last names. And flash fill will auto-fill the series for Last Names till the last cell.

Last names in column c.

Use Flash Fill to join cell content

Just as Flash fill can split cell values, it can also help join cell values in the same way🤝

Let’s see how that happens below.

We will use the same data set, but this time, we will be combining the names instead of splitting them. The data set looks like this:

Combining names from both columns.
  1. To set the pattern for Excel, write the first name of the Names column below.

First Name + Space Character + Last Name

Entering the first name.
  1. Select the cell where you have set the pattern (Cell C2).
  2. Go to the Data tab > Data Tools > Flash Fill.
Flash fill button

This will activate the Flash fill feature, and Excel will auto-fill the remaining list.

The result looks like this:

Flash fill enters the names.

As simple as that 🤩

How to use Autofill to populate cells

The very famous Auto-fill feature of Excel – we’ve all used it at some point, whether knowingly or unknowingly.

It can be activated using the fill handle and through cell drag and drop.

Pro Tip!

The fill handle is in the lower right corner, represented by a plus icon ➕ You can drag it or simply double-click it to activate it.

The auto-fill options button lets you auto-populate adjacent cells. This tool proves the most useful when creating an Excel table.

Let’s see an example of how the auto-fill feature works in Excel.

We have some data right here and we want to use the Autofill feature to autofill the remaining numbers of this list.

Data set for autofill options.

The first two cells are clearly multiples of 8. This sets a pattern for Excel8️⃣

Now let’s see how Excel auto-fills the remaining cells.

All we need to do is select the first two cells (A1 and A2) and drag the Fill handle.

The results are as follows:

Autofill fills 8th table.

Did you notice how Excel picked up the pattern with only two entries? This shows how powerful the Autofill feature of Excel is💪

But its functionality is not only limited to filling series – it can do much more.

For instance, you can use the Autofill feature to copy cells. You can also fill series, years, months, days, weekdays, weekends, and copy formatting.

For example, write Monday in a cell. And drag the fill handle to the adjacent column (or cells); here’s what will happen.

Autofill fills in the days.

Excel automatically fills the list for all the days of the week.

Now, if you click the small icon on the bottom right corner, a drop-down list will appear like this:

Drop down list shows auto fill options.

This gives you more auto-filling options to select from. Say we select the last option, Fill Weekdays, from the drop-down list.

Selecting Fill Weekdays options from the dropdown list

The Autofill feature will fill the list with weekdays in the dataset only.

Result of filling weekdays.

Yes, we get it. A list without weekends is just too boring 🙅‍♀️

What’s the difference between AutoFill and Flash Fill?

While both Autofill and Flash Fill in Excel seem to perform magic by automagically filling in data, they serve different purposes and operate under distinct logic.

Understanding these differences is crucial for harnessing their full potential in your Excel tasks.

AutoFill:

  • Purpose: Extends sequences and patterns across cells.
  • Strengths:
    • Ideal for numerical, date, and predefined patterns (e.g., days of the week).
    • Can rapidly populate large datasets with a repeating pattern.
    • Supports both series continuation and formatting duplication.
  • Limitations:
    • Limited to linear and simple patterns.
    • Less effective for text manipulation.
    • Predictive capabilities might not always align with user intent for complex patterns.

Flash Fill:

  • Purpose: Splits, combines, or reformats text based on example inputs.
  • Strengths:
    • Excels in handling complex text manipulations without formulas.
    • Intuitively recognizes patterns from a single example.
    • Automatically adjusts to intricate data entry tasks, saving time.
  • Limitations:
    • Requires an accurate example to initiate pattern recognition.
    • Static output: does not update with changes in source data.
    • Works exclusively with vertical data series and might struggle with inconsistent patterns.

Choosing Between Autofill and Flash Fill:

  • Use Autofill when:
    • You need to extend a sequence of numbers, dates, or a predefined list (like months or weekdays).
    • You’re looking to quickly apply the same format across multiple cells.
    • Your task involves creating or filling a large dataset with a predictable, repeating pattern.
  • Use Flash Fill when:
    • Your goal is to split first and last names into separate columns or combine them from two columns.
    • You’re formatting or reformatting text data (like adding dashes to phone numbers or combining date components).
    • You require a smart, pattern-recognition tool for text manipulation without resorting to complex formulas.

Limitations of the Flash Fill feature

Although Excel’s Flash fill feature is excellent and extremely resourceful, it still has its fair share of limitations 🚩

And it’s better to have an idea of its drawbacks before you begin using this feature.

So here are some common limitations of the Flash fill feature of Excel:

  • Flash fill can change your number to strings. If you want to keep the numbers in the same format, you can use the Excel format option.
  • Flash fill is static, and it cannot adapt automatically, unlike formulas. It does not reflect any changes in the result upon the alteration of the original data.
  • The Flash fill feature fills a series based on the pattern it has been given. This means that if there are some non-printable characters in the source data, Flash fill will not print them.
  • Although Flash fill instantly recognizes patterns, in some cases, it may fail to do so. For instance, if the contents of a particular cell are different from others, Flash fill might not be able to handle it😵
  • Flash fill can only automatically fill a series when it is vertical. It does not work on horizontal datasets.

That’s it – Now what?

In this article, we learned how to use the flash fill in excel and autofill hundreds of rows in seconds. We also saw the advanced use of the Autofill feature in sorting data in an Excel spreadsheet.

Flash fill and Autofill are two very powerful and resourceful tools of Excel. But even then, they merely make 1% of what Excel has to offer.

There is so much to learn in this giant spreadsheet software. So let’s start from the basics like VLOOKUP, IF, and SUMIF functions of Excel.

Enroll in my 30-minute free email course today to learn these functions (and more!)

Other resources

If you liked this article, we bet you will enjoy reading about other basic features yet super-important features of Excel too.

These include Conditional FormattingCopy Pasting, Referencing, and more.

Frequently asked questions

AutoFill and Flash fill features are very similar but not the same.

The AutoFill feature senses the pattern of data but it can only repeat a simple pattern.

On contrary, the Flash Fill feature can sense, repeat, and make adjustments to a pattern based on what the user is attempting to do.

To Flash Fill a data series in Excel:

  1. Select the cell based on which the series must be filled.
  2. Go to Data > Data Tools > Flash Fill handle. Or press the Control key + E.