Send in the clones: AutoFill in Excel

AutoFill is absolutely my favorite Excel feature. It’s because I’m lazy. I can fill a couple cells and then use AutoFill to turn it into a big, useful spreadsheet.

You can use it for header rows or columns. Say you want to number your rows 1, 2, 3,… You can look at this and figure out what comes next, and so can Excel. Put 1 in the first cell and 2 in the one below it. Then select both cells and move your cursor to the lower right corner until you see a fill handle. It might look like a plus sign or a square. Drag it down and Excel fills in 3, 4, 5, and so on, as far as you want to go. You can use AutoFill for less obvious sequences, such as times, days of the week, dates and text-number mixes like Topic1, Topic2,…  or 1st Phase, 2nd Phase,…

I recommend always giving it at least two cells to work with. Excel makes its best guess as to what you want, but it doesn’t always do what you expect. Even if you want all the cells to be the same you may or may not get it if you give it just one cell to start from.

This is convenient, but the real power comes from using AutoFill with formulas. Excel copies the formula and adjusts it for each new location.

Check out autofill.xls from http://www.decisionspreadsheets.com/book-spreadsheets/, pictured here. It’s a list of orders. One formula computes the sales tax and another takes the total for each order. Let’s look at that total. You can write that formula by clicking on the cells you want to add, with plus signs in between. Just think and click, “=436.19 + $6.73 + $32.71” and you’ll get the formula shown. Now grab the fill handle for that cell and drag it down. It automatically calculates the sum for all the other orders.

I’m astonished how little attention standard Excel books pay to this powerful technique. I have one popular Excel book, more than 800 pages long, that devotes only one paragraph to the topic, and then just mentions it as a way to get a series of incremental values, something that you can do easily without formulas. Another popular book calls it “quirky” and doesn’t even mention that you can use it for formulas. It makes me wonder what other essential decision making techniques are out there that we don’t hear about.

Do you know of any other hidden powerful decision making features of Excel?

One response to “Send in the clones: AutoFill in Excel

  1. Pingback: Useful spreadsheets from a single formula: “Bucket brigade” formulas in Excel | Take Charge

Leave a reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s