How to Use Excel’s Flash Fill and Auto Fill to Automate Data Entry
Excel is great when you’re working with vast amounts of data and you need to keep things organized . 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 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.
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.
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.
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 .
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.
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.
If we drag downward, Auto Fill populates the selected cells with sequential times as hourly intervals.
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.
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.
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.
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 .
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 .
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.
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.
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.)
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.
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.
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.
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 .
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: Rawpixel.com via Shutterstock.com
Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.