How to Use Excel’s Flash Fill and Auto Fill to Automate Data Entry

Brad Jones 24-04-2017

Excel is great when you’re working with vast amounts of data and you need to keep things organized 3 Beginner Tips for Managing Worksheets in Excel Worksheets are the core essence of Excel. Here are a few tips that'll help you stay organized and productive. Read More . However, entering that data into your document and formatting it correctly can often be very time-consuming.


Fortunately, Microsoft designs Excel with this kind of task in mind, and there are various features you can use to accelerate the process. By taking advantage of Auto Fill and Flash Fill, you can ensure that you’re not wasting time 14 Tips to Save Time in Microsoft Excel If Microsoft Excel has consumed a lot of your time in the past, let us show you how to get some of it back. These simple tips are easy to remember. Read More on work that could be automated.

Here’s how to get the most out of Auto Fill and Flash Fill.

First Steps With Auto Fill

Before we jump into Flash Fill, it’s worth making sure we know how to get the most out of Auto Fill. I’m making an early start on my Christmas cards, so I’ve set up an Excel document to help me organize my list of recipients.

christmas cards

Fill Series for Lists

I want to number my list, but I don’t want to write out each number individually. Instead, I’m going to start my list with the number one in cell A2, then drag the bottom right corner of the cell to Auto Fill the rest of the column.


first fill excel

Unfortunately, doing this just fills the list with ones, which isn’t very helpful. We need to use the highlighted Auto Fill Options button to specify what we’re looking for. Click the button and select Fill Series. A quicker way to access these options is by clicking and dragging with the right mouse button, which opens the contextual menu automatically when you release it.

amended fill excel

As you can see, our our numbers progress sequentially. This is about the simplest possible use for Auto Fill. It really becomes powerful when it can make assumptions about your data How to Convert Delimited Text Files to Excel Spreadsheets It's easy to convert a delimited text file to an Excel spreadsheet. Here's how to do it using three different methods. Read More .


Fill Series for Dates

For instance, below you can see how Auto Fill works with dates. Dragging upward automatically populated dates from the past while dragging downward automatically populates dates from the future. This works exactly the same way when you drag left and right, respectively.

filling years excel

Fill Series for Times

The more information you give Auto Fill, the better it will work. Below, you can see that I’ve entered 10:00 AM into a cell, before specifying that it should be treated as a time in the Number section of the Home tab of the Ribbon.

time formatted excel


If we drag downward, Auto Fill populates the selected cells with sequential times as hourly intervals.

hourly intervals excel

That’s useful enough, but Auto Fill can do more than just hourly increments. If we manually enter 10:30 into the cell below 10:00, we can select both cells then drag, resulting in half-hour increments.

half-hour intervals excel


Fill Series for Words

So how does this help our Christmas card list? I’m going to use Auto Fill to determine which card design each person gets.

card designs excel

The three types of cards I have available are already present in the Design column. By selecting the three cells, then using the Fill Handle to extend our selection, we can quickly populate this entire column.

auto fill designs excel

You can also use Auto Fill to add days of the week or months of the year. All Excel needs is the first day or month and it will automatically fill in the rest.

Using auto fill is really helpful when creating your own custom lists in Excel How to Create a Custom List in Microsoft Excel Do you have to fill in the same sets of data into your Excel spreadsheets? Just create a custom list in Excel and use autofill. Read More .

First Steps With Flash Fill

Flash Fill is a close relative of Auto Fill, but it performs a slightly different function. Rather than continuing a series of data, it can source information from other cells and present it with your desired formatting 9 Tips for Formatting an Excel Chart in Microsoft Office First impressions matter. Don't let an ugly Excel chart scare off your audience. Here's everything you need to know about making your charts attractive and engaging in Excel 2016. Read More .

In order for Flash Fill to work correctly, we need to use cells that are adjacent to the data we’re attempting to format. It also helps to alter the formatting of your headers, so Excel doesn’t assume that they’re a part of your data set.

addresses excel

Above, I’ve added two more columns to my spreadsheet, the first containing each individual’s address, and the second ready to receive their zip code. I’ve also added the first zip code manually, to help Excel understand what I’m looking for.

addresses selection excel

Next, select all the cells that you want to fill. From here, open the Data tab in the Ribbon and select Flash Fill from the Data Tools tab. (We could also complete this process using the Fill Handle, much like we used Auto Fill earlier.)

flash fill button excel

Doing this will prompt Excel to find all the information that corresponds with what we manually transferred from cell D2 to cell E2. The software will find each zip code and copy just that information over to the selected cell.

flash filled excel

This can be a powerful time-saving tool. For instance, imagine you’re preparing these addresses for a mail merge, and you want to reconfigure the way the information is arranged and formatted. Flash Fill allows you to experiment with one cell, then automatically populate the same results for the rest of the data set.

addresses formatting excel

Above, you can see that I’ve reordered the information, and changed the case of the city name using Flash Fill. This functionality can’t read your mind, but it can follow your lead if you offer up the right data. Remember: a second example cell can often yield more accurate results, especially if your formatting varies between different rows.

Automate Excel

Now you know how to automatically create lists of any kind with Auto Fill or let Excel extract information from one cell and add it to another with Flash Fill. If you ever need a paper copy of your data, make sure your spreadsheet is well-formatted for printing 7 Excel Printing Tips: How to Print Your Spreadsheet Step-by-Step Are you having problems printing from Microsoft Excel? This guide will show you how to print an Excel spreadsheet perfectly. Read More .

Do you need more help getting to grips with Auto Fill or Flash Fill? Or do you have a tip that you want to share with other readers? Either way, why not join share your thoughts in the comments section below?

Image Credit: via

Related topics: Microsoft Excel, Microsoft Office Tips.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Whatsapp Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *