How to Use Flash Fill and Autofill in Excel Step-By-Step
There are so many instances when we need to fill in extensive data in Excel. Whether it’s product count, employee data, or invoice records – typing hundreds of entries can be frustrating.
So instead, we want to fill our cells automatically and save time and effort. That’s
where Excel’s Flash fill and Autofill make an entry.
These two functions are very powerful and can help deal with tons of data in seconds. We will study these functions in great detail below to understand how they work.
So, without further ado, let’s dive right into the topic. 😃
Also, you can download our sample workbook here if you want to tag
along with the guide.
How to Use Flash Fill to Split Cell Content
Flash fill is so useful when dealing with large data. It lets you separate all the entries in a mere second. Let’s see how it does that below.
Say we have a data set that contains the names of a company’s employees.
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 the data.
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, and it can then automate the tasks 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, and Excel picked it up, as shown in this image.
Simply hit Enter to enable the fill handle, and Excel will auto-fill the options present in the first column. Like this:
You can do the same using the Flash Fill option from the Data Tools group or the CTRL + E keyboard shortcut.
Type in the first word, go to Data Tab, and press Flash Fill.
Do the same with column c, Last name. Flash fill
will auto-fill the series till the last cell.
Use Flash Fill to Join Cell Content
Just as Flash fill can split
cell values, it can 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:
What we’re gonna do is write the first name in the Names column like this:
Then we will activate the Flash fill feature, and the result looks like this:
It’s as simple as that. 😉
You can also use the keyboard shortcut to enable Flash fill here. It is
an efficient method and saves time.
How to Use Autofill to Populate Cells
Excel’s auto fill feature – we’ve all used it at some point, whether knowingly or unknowingly.
It can be activated using the fill handle and cell drag and drop. The fill handle is in the lower right corner, represented by a plus icon. You can drag it or simply double-click to activate it.
The auto-fill options button lets you auto-populate adjacent cells. This
tool is most useful when creating an Excel table.
This is a small insight into how the Microsoft Excel auto-fill option is used. We have some data in this Excel sheet, and we will use Autofill to autofill numbers – confusing, right? 😅
You can guess what is in the first two cells. Now let’s see how Excel deals with this data. All we need to do is click and drag the Fill handle, and the result will be:
Did you notice how Excel picked up the pattern with only two entries? This shows how powerful the Auto fill
options icon is. But its functionality isn’t limited to filling series – it can do much more.
For instance, you can use the Autofill options icon to copy cells. You can also fill series, years, months, days, weekdays, weekends, and copy formatting.
Let’s see some examples to understand the Autofill feature in more detail.
If we write Monday in the second cell of the first column. And drag the fill handle to other cells in the adjacent column; here’s what will happen.
Now, if we click the small box on the bottom right corner, we will see a drop-down list like this:
You can select any option you want, and Autofill will sort your data accordingly. Say we select the last option, Fill Weekdays, from the drop-down list.
Autofill will return any and all weekends in the dataset.
You can do the same if you want to autofill dates in the selected cells. 😀
Limitations to Flash Fill
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 in real-time.
For this reason, we have listed some common limitations of Flash
fill for you. These are as follows:
- Flash fill can change your number to strings when enabled. If you want to keep the numbers in the same format, you can use the Excel format option. It keeps the same value and only changes its visual representation.
- 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. This is why it is better to go through the Flash fill results to ensure there are no errors.
- 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 flash fill in excel and automatically 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 5% of what Excel has to offer. 🥴
There is so much to learn
in this giant spreadsheet software. Starting from the basics like VLOOKUP, IF, and SUMIF functions in Excel.
Enroll in my 30-minute free email course today to learn these functions (and more!)