Categories
Excel

5 Ways to Save Time with Auto Fill in Excel

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.

Excel Courses: We offer Microsoft Excel training delivered online by a live instructor. Participants can connect from anywhere in Canada.

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.

Fill handle in Excel
Fill handle in Excel

In the screenshot below, I have positioned my mouse pointer above the fill handle. The pointer has turned into a small black plus sign.

Mouse pointer positioned above the fill handle
Fill handle close up

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.

Mouse pointer positioned above the fill handle in a cell containing text

Step #2 – Press and hold the left mouse button as you drag your mouse.

Using Auto Fill to copy text
Using Auto Fill to copy text

Step #3 – Release the mouse button.
The cell’s contents are copied to the adjacent cells.

Text copied using Auto Fill

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.

Mouse pointer positioned above the fill handle in a cell containing text and formatting

Step #2 – Press and hold the left mouse button and drag down as far as cell A6.

Using Auto Fill to copy formatting

Step 3 – Release the mouse button.
The content and formatting from cell A4 are copied to A5:A6.

The Auto Fill Options button appears

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.

Fill Formatting Only selected
Using Auto Fill to copy formatting

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.

A fill series displaying months
Using Auto Fill to create a fill series

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.

A fill series displaying days of the week

You can use also use AutoFill to copy dates and headings.

A fill series displaying fiscal quarters and dates

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.

Cells containing numbers (1 and 2)

Step #2 – Select the range A1:B1.

A1:B1 selected

Step #3 – Drag the fill handle to continue the numeric sequence, as shown below.

A numeric sequence created using Auto Fill
Using Auto Fill to create a numeric sequence

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.

Formula in cell B7 adds numbers in the column

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.

Formulas copied with Auto Fill
Copying formulas with Auto Fill

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.

Options selected in the File menu

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.

The Edit Custom Lists... button in the Excel Options dialog box

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.

NEW LIST selected in the Custom Lists dialog box

Step #4 – Click the Add button then click OK to close the Custom Lists dialog box.
The Excel Options dialog box reappears.

New custom list created

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.

Fill series with custom list entries

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.