Contextures

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.

fill handle and pointer

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

type range address in name box

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
    • type range address in name box
  • 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 options for right mouse button

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.

    paste special command

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.

    paste special command

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

    custom list dialog box

  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 or even numbers.

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
  • When you reach the end of the series that you want to add, let go of the mouse button

type range address in name box

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, in the Step Value box, type 7
  • Leave the other settings on Columns, Date, and Day.
    • Series dialog box
  • Click OK, and the list is created

list of dates by week

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
  • For Date Unit, choose Weekday
  • Leave the other settings on Columns and Date.
    • Series dialog box
  • Click OK, and the list is created, showing every second workday.

list of dates by week

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

autofill insert cells

Get the Sample File

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

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

Get weekly Excel tips from Debra

More Tutorials

Data Entry Tips

Fill Blank Cells

Excel Shortcuts List

Add Number to Multiple Cells

Double-Click Shortcuts

 

Last updated: October 11, 2020 10:54 AM
Contextures RSS Feed