Auto Fill is a tool in Microsoft Excel that helps you create spreadsheets more efficiently. It lets you quickly copy text, numbers, and formatting. You can also use it to create a fill series using numbers, headings, or dates.
Auto Fill is very flexible. You can use it in a variety of scenarios. It’s an Excel feature that I use on a daily basis – and I think you’ll find it’s worth taking the time to learn about.
The Fill Handle
Before we talk about Auto Fill, you really need to know what the fill handle is.
The fill handle is located in the lower-right corner of the active cell. Any time you click to select a cell and position your mouse pointer over the fill handle, it will turn into a small black plus sign. This is always the first step in using Auto Fill.
In the screenshot below, I have positioned my mouse pointer above the fill handle. The pointer has turned into a small black plus sign.
Enough preamble… let’s get to the shortcuts!
Copy Text or Numbers
The first way Auto Fill will save you time involves copying text or numbers. If I type some text into a cell and I want to copy that text to a number of adjacent cells, I can simply drag the fill handle to copy it to additional cells in that row or column.
Let’s look at an example.
Step #1 – Select a cell with data in it then position your pointer over the fill handle.
Step #2 – Press and hold the left mouse button as you drag your mouse.
Step #3 – Release the mouse button.
The cell’s contents are copied to the adjacent cells.
In this scenario, I copied my name down two rows. I could have copied my name up two rows or I could have copied it to adjacent columns to the left or right. Using Auto Fill this way will work with text or numbers.
Copy Formatting
The second way Auto Fill will save you time involves formatting. Auto Fill can take formatting applied to one cell and copy it to adjacent cells.
To be clear, Auto Fill will copy a cell’s contents and formatting. Afterward, you can specify that only the formatting should be applied. This fact wasn’t obvious in the previous example because the cell that had my name in it didn’t contain any formatting.
Let’s look at an example where one cell is formatted the way we want it to be and we copy that formatting to adjacent cells.
In this example, the Housing category label has a yellow background and the font is italic.
Here is the process to copy the formatting to adjacent cells.
Step #1 – Select cell A4 and point to the fill handle.
Step #2 – Press and hold the left mouse button and drag down as far as cell A6.
Step 3 – Release the mouse button.
The content and formatting from cell A4 are copied to A5:A6.
Step 4 – Click the Auto Fill Options button and select Fill Formatting Only.
The formatting is applied to A4:A6 and the category labels are restored.
Create a Fill Series
Now we’re going to have some fun! The third way to use Auto Fill helps you to create a fill series. A fill series is a sequence of words, numbers, or dates. You’ll see what I mean shortly.
If I type the name of a month then drag the fill handle to the right, Auto Fill adds the next month and continues the sequence. This will work for the names of months spelled out in full like January and February. It’ll also work for short forms like Jan and Feb.
If I type one of the days of the week and drag the fill handle to the right, Auto Fill continues that sequence too. Once again, this will work with the weekdays spelled out in full or with short forms.
You can use also use AutoFill to copy dates and headings.
Using Auto Fill to Create a Numeric Sequence
There is one aspect of using Auto Fill that isn’t very intuitive. With a numeric sequence, Auto Fill needs to see two values. If I want to create a series of numbers, I have to include the first two numbers in the sequence.
Here is how it works.
Step #1 – Type the number 1 into cell A1, the number 2 into cell B1.
Step #2 – Select the range A1:B1.
Step #3 – Drag the fill handle to continue the numeric sequence, as shown below.
Copy Formulas
The fourth way that Auto Fill will save you all sorts of time is by copying formulas.
Step #1 – Enter the formula =SUM(B4:B6) into cell B7.
Step #2 – Press and hold the left mouse button and drag the fill handle to the right to copy the formula to additional cells.
A sum is calculated for each column.
More: Excel’s SUM Function vs SUMIF, SUMIFS
Creating a Custom List
The fifth way that you can use Auto Fill to save time is by creating and using your own custom lists. Once you’ve built a custom list, you can create a fill series based on that sequence.
An example will go a long way toward illustrating this concept.
Imagine that you’re a huge Beatles fan. You’ve collected photos and artifacts belonging to each band member and you want to catalog the items you’ve accumulated.
Here’s how you create a custom list for this purpose.
Step #1 – Click the File tab in Excel and select Options.
Step #2 – In the Excel Options dialog box, select the Advanced tab, scroll all the way to the bottom and click the Edit Custom Lists button.
Step #3 – In the Custom Lists dialog box, make sure that NEW LIST is selected in the Custom lists: box. Click in the List entries: area and type John, Paul, George, and Ringo. Make sure you press Enter after each name.
Step #4 – Click the Add button then click OK to close the Custom Lists dialog box.
The Excel Options dialog box reappears.
Step #5 – Click OK to close the Excel Options dialog box.
At this point, you can click in any cell and type John. When you drag the fill handle to the right (or any direction, really), the remaining Beatles’ names will be filled in.
