Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Streamline Workbook Creation with Autofill

Quote of The Day

Ask not what the world needs. Ask what makes you come alive… then go do it. Because what the world needs is people who have come alive.

Howard Thurman

One day I was teaching an Introduction to Excel class when one of the participants questioned why it was important to ‘do everything quickly.’ I had discussing ways to efficiently create a workbook. I took the comment as coming from the perspective that many North Americans and Canadians, specifically, live life at too fast a pace. I happen to agree with that point of view but at the same time I prefer to get the drudgery of a task quickly out of the way so that I can dedicate more time to parts of the task that I find more interesting.

Autofill is an Excel feature that many self-taught users are not aware of. It’s easy to miss the tiny clue that the feature even exists. Autofill has two main purposes in Excel, extending a series, and copying formulas.

The Autofill handle

Unless you look very carefully, it’s easy to miss the autofill handle at the bottom right corner of the selection outline.

Once you have found the handle, you also need to watch the shape of your mouse pointer carefully:

 

When the mouse pointer resembles a black plus-sign, you are pointing at the autofill handle. You are ready to autofill. If the mouse pointer is any other shape, you are not  ready to autofill:

imageTo perform the autofill, point your mouse at the autofill handle, then press and hold  the left mouse button. Then move your hand in the direction that you want to fill, either vertically or horizontally.

Extending Series

Excel recognizes certain data as belonging to one or other series. Weekday names, month names, expressions like ‘Qtr 1’ or ‘1st Quarter’, these are all series that autofill can automatically extend for you. Here is a brief video demonstration in which I create a series of month names for an income and expense worksheet. (I recorded

 

this demo using Excel 2007 so there are some screen features that you may not see if you are using a version of Excel earlier than 2007. The basic autofill techniques and results are the same however. In the demo, I started with the abbreviation for the month of January. If I had entered the full name, then autofill would have inserted the un-abbreviated name for each month into the cell. Note also that the case of the starting cell determines the case of all of the autofilled cells.

Copying Formulas

If the only thing autofill could do was to extend series, saving us from some typing drudgery, it would still be a useful tool that every Excel user should be aware of.

However, autofill can do more. Try autofilling a formula, instead of the first cell of a series. You will find that  autofilling may faster than copying and pasting formulas when you want to apply a formula across several rows or columns

Advanced Autofill TechniquesRemember that Autofill can extend any series as far as you need.  Now, here are some specialized techniques.

  • create a series of sequential numbers
    • in Excel 2000, enter the beginning number in a cell and then hold the <Ctrl> key down while you drag the autofill handle.
    • in Excel XP (2002) and later, enter the beginning number in a cell, drag the autofill handle, then click the fill options button and select series.
    • if you want a sequential series to the right or left of a column of data, enter the starting value in the cell to the left or right of the column containing the data, point to the fill handle and double click.
  • create an intermittent series
    • to create a series of even numbers, enter the first number of the series into one cell, the next number in the series in the next cell down or to the right (depending on the direction in which you want to fill), select both cells and then fill.
    • to create a series of five weekdays with no weekend days, create a series from the first day to the last day you want to include in the week, copy that last of days and paste it in the immediate next cell, then select all of the cells that have a day name in them and fill for as many weeks as you need.

This video demonstrates these techniques.

 

 

 

Comments are closed.