Using Auto Fill in Excel

The Auto Fill feature in Excel is a powerful tool for streamlining workflow and is an excellent way to quickly fill series of data and copy formulas.  For instance, Auto Fill has been programmed to know that Tuesday comes after Monday and that February comes after January.  So instead of typing out a series of days or months, you can just type the first day or the first month and then fill the series by dragging the Fill Handle across the empty cells you wish to populate.

The Fill Handle is located in the bottom, right hand corner of the active cell or range (the selected cell or range) and appears as a small, solid black box.  To activate the Auto Fill feature:

  • hold your cursor over the Fill Handle until a solid, black cross appears (make sure you have the black, solid cross and not the cross with arrows on each end)
  • hold your cursor over the solid black cross and click with your left mouse
  • keep the left mouse held down and drag across the range of cells you wish to populate
  • let go of the left mouse when done

Creating Numerical Patterns using Auto Fill

When filling a numerical series you must create a numerical pattern first for Auto Fill to work properly.  As an example, using Auto Fill will not automatically give you the number 2 after the number 1.  If you type 1 in a cell and try to Auto Fill that cell down, you will get a 1 all the way down the list.  You must type a 1 in the first cell and type a 2 in the second cell.  Then highlight both cells together and Auto Fill from the second cell (the 2).  Auto Fill will then continue with the pattern you have created (i.e., 3,4,5,6).  Similarly you may create other Auto Fill patterns such as 2,4,6,8 or 4,8,12,16.  Auto Fill will recognize the pattern you create and fill the series accordingly.

Copying Formulas with Auto Fill

As well as filling series of data, Auto Fill is a very efficient way to copy formulas in Excel.  By default each cell reference in Excel is a “relative” reference.  That means, as you Auto Fill formulas, the cell reference changes relative to its new position in the worksheet (i.e., =SUM(A1:A10) becomes =SUM(B1:B10) and then =SUM(C1:C20)) as you fill the formula across columns.  If you Auto Fill a formula down by row, then =SUM(A1:D1) becomes =SUM(A2:D2) and so on.

Using Auto Fill to duplicate formulas is faster and more efficient than using the copy feature and I highly recommend this as a Best Practice. 

This entry was posted in: MS Excel Tips.
"Hawthorn Training delivers expert and up to date software training solutions for your business."