Autofill is an essential feature that any Excel user must be constantly using as it saves tremendous amount of time when creating spreadsheets. The capabilities of this feature range from simple copy-paste tasks to automatically creating entire tables, or transferring formatting settings from one cell to the rest of your workbook. With autofill Excel will save you a ton of headache and time. Let’s see how!
You just need to enter the pattern of your list into a few cells and then all it takes is dragging or double clicking the cells. This amazing feature can save you hours, if not days of copying or manually entering data in most applications. It’s a smart feature too – in some cases Excel can ‘sense’ the pattern in your data and suggest possible actions.
How to use
There are mainly 3 ways to use the Autofill feature, and all start at the same place: The small square on the bottom right corner of a selected cell or a range.
You can,
- drag the square with left-click to fill any number of cells with the same basic options.
- double-click the square to apply the basic options to cells that are adjacent to the range or cell.
- drag the square with right-click to fill the desired number of cells with the basic options.
Now, let’s take a closer look at each action.
Dragging with left-click
To create a new list independent form other ranges or find the length of a list, drag the bottom-right square with a left-click in a single direction (i.e. left or up).
For example if you type in 1 in a cell, dragging the fill square down the column will look like below.
The tooltip text that appears next to your mouse pointer shows the value of the last cell after autofill. If there are no predefined lists or numbers following a text, using the Autofill from the first cell will copy the first cell value. We will cover other alternatives later.
When you release the left mouse button, the range will be populated with Autofill values with an options button appearing on the bottom right corner.
Click the Autofill options button to reveal additional Autofill options.
- Copy Cells copies the same static value into all cells.
- Fill Series populates values of predefined lists or sequential numbers. For example, choosing Fill Series will fill the list from the example above with numbers from 1 to 8.
- Fill Formatting Only copies the format of the first cell into other cells.
- Fill Without Formatting copies cells like in the Copy Cells option, without copying the first cell format.
- Flash Fill is essentially an advanced version of Autofill that can ‘sense’ a pattern in adjacent columns and predict the value of the rest of the cells.
If source cell includes date values, you will see dates listed in an incremental fashion (default is by day). You can choose other options to change the date increment.
Double-clicking
Double-clicking the bottom right square does the same thing as dragging with a left-click - it fills the cells until the last cell of an adjacent column. If there is data both to the left and right of your selection, Excel will pick the longest column to fill out with data. Double-click method will show the same Autofill Options button after data is populated.
Dragging with right-click
Dragging with right-click allows fine tuning the Autofill process. Instead of only filling in the first cell, dragging with right-click will ask you what type of fill action you’re looking for.
The first section of options are the basic settings that are also listed in the left-click and double-click pop-up button. The rest of the options are:
- Fill Days / Weekdays / Months / Years: These options become available only if the source cell has a date value. The option selected will determine the date interval for populating the rest of the cells.
- Linear / Growth Trend: If the source cells contain numeric data, you can use these options to generate a series.
- Series: This item pops up the Series dialog box that contains other advanced options.
Tips & Tricks
Populating Month and Day Names
You may have noticed Excel auto-complete the rest of a column when you enter, say "January". Entering data like this and double-clicking the Autofill Excel button (the square) will fill out the rest of the column with the consecutive month names.
This works with both long and short (i.e. "January" or "Jan" will work) names, as well as days of the week. These are called predefined lists in Excel.
Creating and populating custom lists
You can create your own lists and use the same Autofill features, just like in date names. We begin by defining a custom list. Follow one of the paths below depending on your Excel version:
- In Excel 2003, Tools > Options > Custom Lists
- In Excel 2007, Office button > Excel options > Advanced > Edit Custom Lists… button in the General
- In Excel 2010 and newer, FILE > Options > Advanced > Edit Custom Lists… button in the General
The Custom Lists dialog box that allow adding, removing, and managing custom lists. Month and day names (built-in lists) will be shown here as well.
You can add your custom list using either method:
- Manually typing in the list items into the List entries You can separate items by pressing the Enter key.
- Selecting a range of cells from the Import list from cells
Custom lists can also be very useful in sorting data. You can sort your data in the same order you entered your list. For additional information about sorting, please see following article: The crux of organizing your data: How to sort in Excel
Populating sequential numbers
To quickly generate a set of ascending or descending numbers in a linear trend, all you need to do is enter the first 2 values of the series and use the Autofill feature.
Note that you can Autofill more than one column or row at once.
Text with numbers
Although a single number value is not enough to start a sequence of numbers, Excel can automatically increase the number at the end of a string value. This can be helpful when you need numbers with prefixes.
Special Cases
Excel can populate text-number combinations in special ways. For example, if you use a number with the string "Quarter", Excel will auto-populate numbers from 1 to 4.
Another special case is the ordinal numbers. Excel can detect ordinal numbers and modify their suffixes in accordance with accompanying numbers.
Repeating Series
Via Autofill Excel can copy a set of repeating values multiple times. If you need to repeat different values in the same order that they are entered, select them all, and use any Autofill action. In the example below, "Base Salary", "Bonus" and "Overtime" values repeat themselves, and are copied down in the same order they are entered.
