AutoFill and Flash Fill – 2 Excel Features You Need to Know
There are two timesaving features available in Excel that you need to learn about if you’re not already using them: AutoFill and Flash Fill.
I’ve been using AutoFill for years, but to be honest, I only started using Flash Fill recently. I didn’t understand how helpful Flash Fill was until I understood how it worked.
In this article, I’ll show you how to use both of these features to make working in Excel easier.
AutoFill
The AutoFill feature can be used in Excel to extend filled cells to empty cells in your worksheet. You can AutoFill up, down, left, or right in your worksheet.
The AutoFill feature can be used to:
- Copy formulas.
- Extend a pattern of values.
- Extend a predefined custom list.
AutoFill Examples
Here are some examples to show you how the AutoFill feature works.
Copying Formulas
You can use AutoFill to copy formulas. Let’s say you had a worksheet that contained the number of Runs a player had in column A, the number of Walks in column B, and the number of Runs & Walks in column C. The Runs & Walks value is a calculation that adds the number of Runs to the number of Walks.
Once you have the formula for Runs & Walks in the first cell in column C, you can use AutoFill to quickly copy the formula down the remaining cells in the column.
Extending a Pattern
If you had a worksheet with a list of States and Cities and wanted to add a sequential number to the worksheet in the Entry # column, you could use AutoFill for this. You would type a “1” in the first cell of the Entry # column, type a “2” in the second cell, then use the AutoFill handle to extend the sequential numbers down the remaining cells of the column.
Extending a Predefined Custom List
As a final example, if you had a worksheet that showed the Sales and Commissions for the first six months of the year, you could type “January” in the first month cell, then use the AutoFill handle to populate the rest of the month names in the column.
How to AutoFill
There are several ways of using the AutoFill feature.
Dragging to AutoFill
- Highlight the cell or cells that contain the starting value(s).
- Left-click the AutoFill handle in the bottom right-hand corner of the selection.
- Drag until all cells to be filled have been selected.
- Release the mouse button.
When you use the AutoFill handle, you can drag it up, down, left, or right.
Double-Clicking to AutoFill
- Highlight the cell or cells that contain the starting value(s).
- Double-click the AutoFill handle in the bottom right-hand corner of the selection.
When you double-click the AutoFill handle, Excel will fill in all the empty cells below the starter cells until the end of the data range (the contiguous data in the cells surrounding the autofill cells).
Copy Instead of AutoFill
You can use the AutoFill handle to simply copy a value to other cells in a row or column. If you select a single value to AutoFill, by default Excel will simply copy that value to the other cells.
If you select more than one value to AutoFill, Excel will try to extend the pattern if one is detected. If you wanted the values copied instead:
- Use the Drag or Double-Click option to AutoFill the cells.
- Click the Paste Options icon.
- Select “Copy”.
Custom Lists
Excel comes with several custom lists that you can use the AutoFill feature to extend within your data. By default, Excel has the following custom lists:
- The first three letters of weekday names (Sun, Mon, etc.)
- Full weekday names (Sunday, Monday, etc.)
- The first three letters of month names (Jan, Feb, etc.)
- Full month names (January, February, etc.)
To see the available custom lists:
- Click “File”.
- Click “Options”.
- Select “Advanced”.
- Scroll down to the bottom of the Excel Options dialog box.
- Click “Edit Custom Lists”.
You will see the current custom lists in the “Custom lists” section of the dialog box.
You can also create your own custom lists. So, if there is a list of values that you enter frequently, create a new custom list so you can use the AutoFill feature to extend it in your cells. For instance, you might want a list with the four cardinal directional names:
- North
- South
- East
- West
To create a custom list with these values:
- Follow the steps above to get to the available custom lists.
- Select “NEW LIST” from “Custom lists”.
- Place your cursor in the “List Entries” textbox.
- Type each list item and press enter.
- Click “Add”.
- Click “OK” twice.
Once your custom list has been added, you can type any item in the custom list and then extend the list using the AutoFill handle.
Flash Fill
Excel’s Flash Fill feature can be used to fill in a column of cells based on a pattern from another column of cells. This feature can be helpful if you have a column of data that you want to reformat or split into one or more columns.
For instance, let’s say you have a worksheet with the following three columns:
- Cast Name
- First Name
- Last Name
If the Cast Name column contains the full name of cast members and you want to populate the first name of the cast member in the First Name column and the last name of the cast member in the Last Name column, you could use Flash Fill to accomplish this quickly.
To update the First Name column:
- Select the first cell in the First Name column.
- Type the first name contained in the first Cast Name column in the selected First Name cell.
- Select the second cell in the First Name column.
- Start typing the first name contained in the second Cast Name cell.
- When you see the remaining first names in the gray text below the second entry, press the Enter key to populate them in the cells.
If Excel can detect the pattern from what you are typing, it will display the remaining items it will populate down the column as light gray text. As long as you press Enter while the list is displayed, the remaining cells will be populated with the values.
If Excel cannot detect the pattern, you’ll need to enter the values in the first two cells in the column, then select all the cells that should be populated with the values (including the two cells that you typed values into) and then press CTRL+E to populate all the cells.
In the example below, Excel could not determine that pattern automatically; I needed to force Excel to do the Flash Fill. I just typed the first two dates (in cells B2 and B3), then I selected cells B2 through B6 and pressed CTRL+E to complete the Flash Fill.
If Excel still cannot detect the pattern after you press CTRL+E, you will receive an error message and will not be able to use the Flash Fill feature for that instance.
You can use Flash Fill to update cells to the left or the right of the original data, but be sure there are no empty columns between the data range with the original data and the column you want to Flash Fill in.
Conclusion
AutoFill and Flash Fill are great Excel features that can save you a lot of time, especially if you have a long list of data to update.
Are you ready to try out these features? How will you use them?
For more Excel tips and tricks, check out the following articles:
25 Excel Navigation and Selection Shortcuts You May Not Know
5 Excel Shortcuts to Drastically Speed Up Data Entry
3 Ways Excel Can Help You Track Your Freelance Income and Expenses
Top Excel Features Every Freelancer Needs To Know
How to Automate Repetitive Tasks Using Excel
Brenda Keller is a software coach who helps people feel comfortable with and learn how to use software. She has been an Excel user since it was first introduced in 1985 and has taught the program in various settings. Her new Microsoft Excel: Quick Start course was designed to help those new to Excel or those wanting a refresher to get a handle on Excel’s key features quickly. Check out her course on Udemy here or view her YouTube channel here to get more help with Excel. To learn more about Brenda Keller, check out her official website here.
