Excel Autofill

The Excel Autofill feature can be used to populate a range of cells with either a repeat value, or with a series of numerical values (eg, 1, 2, 3, ...). To use the Excel Autofill :


Excel Cell Fill Handle and Autofill Options Box
  • 'Copy Cells' - copy the initial cell across the selected range
  • 'Fill Series' - fill the selected range with a series, starting with the initial cell value
  • 'Fill Formatting Only' - fill the selected range with the formatting, but not the values of the initial cell
  • 'Fill Without Formatting' - fill the selected range with values, but do not copy the formatting from the initial cell

Note that the Fill series option also works with dates and times, as these are stored as numbers in Excel.


Autofill Using More Than One Starting Cell Value

Excel Auto Fill Sequence

The easiest way to start off a series which you want to extend using the Auto Fill feature, is to type the first two values of your series into the first and second cells of a range. Select both of these cells and again, drag the fill handle across the range to be filled. Excel will automatically recognise the pattern from the two initial cells and continue this across the selected range. Using this method, you can get Excel to fill cells by increments of any number (eg. 2, 4, 6, 8, ...).

Alternatively, if you want Excel to fill cells with repeated alternating values (eg. 1, 2, 1, 2, 1, 2, ...) you can start off the pattern in the first two (or more) cells, then, with the initial cells highlighted, drag the fill handle and then click on the 'Auto Fill Options' box. Within this box, select the option 'Copy Cells' to repeat the initial cell values across the selected range.


Excel Autofill Dates

Autofill Dates & Times

As dates and times are stored in Excel as numbers, these also work in the Excel Autofill. By default (if you just type in a single date or time and drag the fill handle), dates and times will complete in a series, by adding one day (for dates), or one hour (for times). However, as with simple numbers, you have the option of clicking on the 'Auto Fill Options' box, to select a different type of Auto fill.

Times have the same four Auto fill options as are shown above, for simple numbers. However, for dates, there are more Auto Fill options. In addition to the four options for simple numbers, there are also the following:


Autofill Functions and Formulas

The Excel Autofill feature also works with functions and formulas in Excel. However, with this type of Autofill, there is no 'series fill' option. Instead, Excel observes the rules of Absolute and Relative Cell References (ie. if a row or column reference is preceded by a $ sign, excel will keep the reference constant as the formula is copied to other cells; Otherwise, the row or column reference will be adjusted as the formula is copied to other cells.


Horizontal and Vertical Autofill

Excel Autofill Across a Row

As well as working down a column, the Autofill feature also works horizontally, across rows. If you start off with just one cell selected, the Autofill will also work horizontally and vertically at the same time (although this is not permitted if you have more than one cell selected when you drag the fill handle).


Autofill Multiple Rows or Columns Simultaneously

The Excel Autofill can happily handle data in more than one row or column. This is shown in the example below, in which cells A1 and A2 initially have numerical values 1 and 2, and cells B1 and B2 initially both have the numerical value 3.

Highlighting cells A1 to B2, and then dragging the fill handle down columns A and B causes the Autofill to complete both columns with their own series (ie. column A completes with 1, 2, 3, 4, ... and column B completes with 3, 3, 3, 3, ...)

Excel Autofill More Than One Column


Double Click on the Fill Handle

For speed, you can Autofill a full column by double-clicking on the fill handle of a highlighted cell (or range of cells). If the cells below the highlighted cell (or range) has values in it, double clicking the fill handle causes the Autofill to fill down the current column until it encounters a blank cell.

If there is no data in the cell below the highlighted cell (or range), the Autofill will only work if there is data in an adjacent column. Then the Autofill will fill down the current column until it either encounters a non-blank cell in the current column or a blank in the adjacent column.

Return to the Basic Excel Page

Return to the ExcelFunctions.net Home Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013 ExcelFunctions.net