Home > Data > Data Entry > AutoFill

Excel AutoFill Examples

Excel AutoFill examples show how to quickly make a list of dates, months, numbers, or custom items. AutoFill can quickly insert or delete cells too

Introduction

These Excel AutoFill examples show how to quickly make a list of dates, months, numbers, or custom items, with very little typing.

Watch this short video, to see how AutoFill creates a date series. There are written instructions, and more examples, below the video.

Fill Handle

When you're working in Excel, the active cell has a Fill Handle -- a small black square at its bottom right corner. When you point to the Fill Handle, the pointer changes to a small black plus sign.

You can drag the Fill Handle, or double-click it, to activate the AutoFill feature.

AutoFill a List of Dates

If you want to enter a series of consecutive dates in a column, use one of these AutoFill examples.

-- Data in Next Column

-- No Data in Next Column, or Blank Cells in Next Column

Data in Next Column

If there is data in an adjacent column, follow these steps:

• In the starting cell, type the first date
  • Tip: Press Ctrl + ; (semi-colon) to enter the current date
• Double-click on the Fill Handle at the bottom right corner of the cell

Same Date

After the series is created, follow these steps, if you want all the cells to have the same date:

• Select all the cells
• Press Ctrl + D, to copy down

No Data in Next Column, or Blank Cells

If there is no data in the adjacent column, or the adjacent column has blank cells, follow these steps:

• Click in the Name Box at the left of the Formula Bar
• Type the address that you want to fill, e.g. A2:A200
• Press Enter, to select the range that you typed
  • 
• In the active cell, type the starting date
• Press Ctrl + Enter, to put that date in all the cells

Create a Date Series

After the dates are filled in, follow these steps, if you want a date series, instead of all dates the same:

• Select the starting cell
• Double-click its Fill Handle, to create a series of consecutive dates

AutoFill Options

Instead of double-clicking the Fill Handle, you can drag it.

• TIP: In most cases, you'll drag the Fill Handle down, to AutoFill a list down a column. However, you can drag the Fill Handle, left, right, up or down, to AutoFill in any direction.

While you drag, press either the Left mouse button, or the Right mouse button.

• Press the LEFT mouse button while you drag, to get the default fill setting
• Press the RIGHT mouse button, and you'll see an options menu when you release it.
  • Then click on one of the options, such as Weekdays, to AutoFill with that setting

AutoFill with a Custom List

You can use a custom list with the AutoFill feature. Excel has a few built-in customs lists, such as weekday names, and you can create your own custom lists.

--AutoFill Weekday Names

--AutoFill Month Names

--AutoFill Your Own List

AutoFill Weekday Names

To quickly create a list of weekday names:

1. In a blank cell, type the short or long version of a weekday name. For example, type Tue or Tuesday.
2. Select the cell, and point to the Fill Handle, at the bottom right corner of the selected cell.
3. Press the left mouse button, and drag (up, down, left or right) -- the weekday names will appear in a pop-up tip, near the pointer.
4. When you reach the last cell that you want filled in, release the mouse button.

   

AutoFill Month Names

To quickly create a list of month names:

1. In a blank cell, type the short or long version of a month name. For example, type Sep or September.
2. Select the cell, and point to the Fill Handle, at the bottom right corner of the selected cell.
3. Press the left mouse button, and drag (up, down, left or right) -- the month names will appear in a pop-up tip, near the pointer.
4. When you reach the last cell that you want filled in, release the mouse button.

AutoFill with Your Own List

To quickly create a list, based on a custom list that you created. For example, create a custom list of cities, where your offices are located.

1. Type any item from your custom list in a cell
2. Select the cell, and point to the Fill handle

   

3. Drag down, up, left or right, to AutoFill the list.

AutoFill a Number Series

Use the AutoFill feature to quickly create a number series, such as a list of odd numbers or even numbers.

For example, to create a list of even numbers, follow these steps:

• In cell A2, type the number 2
• In cell A3, type the number 4
• Select cells A2:A3
• Point to the Fill Handle at the bottom of cell A3, press the Left mouse button, and drag down
• As you drag down, a popup tip shows the current number in the sequence
  • In the screen shot below, the popup shows "12"
• When you reach the end of the series that you want to add, let go of the mouse button

Alternative Method to Fill Series

Another way to create a series of numbers is by pressing the RIGHT mouse button, as you drag down.

To try this alternative method, follow these steps:

• In the first cell for the series, cell A2, type the number 2
• In cell A3, type the number 4
• Select the two cells where you started the number series - A2:A3
• Point to the Fill Handle at the bottom-right corner of cell A3
• Press the RIGHT mouse button, and drag down
• As you drag down, a popup tip shows the current number in the sequence
  • In the screen shot below, the popup shows "12"
• When you reach the end of the series that you want to add, let go of the mouse button
• A pop-up menu appears, showing the AutoFill options
• Click the Fill Series command, to complete the series of even numbers

AutoFill with Series Settings

This short video shows how to use the Series dialog box, and create a list of dates. There are written steps, and more AutoFill examples, below the video.

-- AutoFill Dates by Week

-- AutoFill Dates by Weekday

Fill Dates By Week

To create a list of dates by week, follow these steps

• Type the first date in cell A2
• Point to the Fill Handle, press the Right mouse button, and drag down, for as many weeks as you want in the list
• In the popup menu, click on Series (at the end of the list)
• In the Series dialog box, click in the Step Value box
• Where the cursor is flashing, type the number, 7
• Leave the other settings unchanged, for Columns, Date, and Day.
  • 
• Click OK, and the list with a series of dates is created

Fill Dates By Weekday

To create a list of every second date, with weekdays only (no weekends), follow these steps

• Type the first date in cell A2
• Point to the Fill Handle, press the Right mouse button, and drag down, for as many dates as you want in the list
• In the popup menu, click on Series (at the end of the list)
• In the Series dialog box, in the Step Value box, type 2
• (optional) To end at a specific date, enter that date in the Stop Value box
• For Date Unit, choose Weekday
• Leave the other settings on Columns and Date.
  • 
• Click OK, and the list is created, showing every second workday.

Insert or Delete Cells with AutoFill

In addition to entering data, you can also insert or delete cells with the AutoFill feature.

• NOTE: Make a backup copy of your file, and work carefully, when using this technique

To insert or delete cells with AutoFill, press the Shift key, then

• Drag away from the selected cells, to insert cells (shown below)
• Drag over the selected cells, to delete them

Get the Sample File

To try the AutoFill examples from this page, get the AutoFill Examples workbook. The zipped Excel file is in xlsx format, and does not contain any macros.