Software

Use Excel tables for quick formula auto-fill

Excel 2007's new table feature eliminates the need to copy formulas; once you define a data range as a table, Excel will do it for you!

Tables are new to Excel 2007 and at first, you might think…redundant…who needs them? They do appear to be just an easy way to apply some fancy formatting to a data range, but tables are far more than that. Tables have special properties that will make you more productive and efficient.

Tables offer a lot, but my favorite feature is the formula auto-fill. Normally, you enter a formula and use the fill handle to copy the formula as needed. For example, using the data range shown below, you might enter a SUM() function that sums B3:E3 in cell F3. Then, you'd use the fill handle to copy that formula to F4:F6.

If you define your data range as a table, Excel will automatically copy the formula in F3 to F4:F6 for you. To do so, you must first define the data range as a table, as follows:

  1. Select the data range. In this case, that's A2:E6.
  2. Click the Insert tab.
  3. Click Table in the Tables group.
  4. Click OK to close the Create Table dialog box. Excel formats the data range as a table.

Now, enter the following function into cell F3: =SUM(B3:E3) and press Enter. When you do, Excel automatically copies the formula to the remaining rows in the table. It isn't perfect: you'll still have to adjust the column width and enter a column heading. But, copying the formula is one less chore you have to do.

It's not a huge deal, but if you can get Excel to do more work for you, why not? This particular behavior will come in handy when working with large data ranges.

The row behavior is different, but still very handy--let's take a quick look. Enter the following function in cell B7: =SUM(B3:B6). When you do, Excel displays a dropdown arrow next to the cell. Click it and you'll see a list of functions. Simply select one of the listed functions to quickly update the current function.

How are you using Word tables to be more productive and efficient?

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

7 comments
DBlayney
DBlayney

This is a neat feature, one of many that I have not yet seen before. It's a pity that it does not autofill the row functions. There are also some "features" that need to be understood: The selectable functions in the bottom row all create SUBTOTAL functions that will ignore hidden rows in the table which is good if that's what you want. If you select the relevabt function by using the "More functions" option at the bottom of the drop down, this does not happen. Care is needed in copying a (SUBTOTAL) function across under all the columns; dragging with the autofill handle works nicely; using Fill:Right does not do what you might expect as it copies the formula exactly and gives the same result in all columns (i.e. the Sum/Average or whatever of the first column)

ray
ray

The author only scratched the surface of what tables offer. Yes, when creating a table, automatically replicating formulas down a column is nice but the REAL winner is that when you insert a new row between existing rows in a table, the formula is automatically added to the new row AND the formula is automatically adjusted in all following rows! Try THAT with a plain old worksheet. Just THAT feature makes using tables highly advantageous.

lyambor
lyambor

Since it really only works for column functions not fill in for row - seems of limited use Might as well just select the blank Column/row that will calculate your function and then select your function from the Sum tool options - it automatically calculates displaying answers in both Column and ROw and same time - Alot less trouble than these steps

daniel
daniel

The table feature existed in Excel 2003 also. They were called Lists (in the Data menu) and they worked the same way too. One think new in 2007 is that they can easily be formatted to show banded rows. Also double-clicking on a value in a Pivot Table will display the break down for that value in a Table.

ssharkins
ssharkins

Most likely, an AutoCorrect option has been disabled: Click the File tab (2010) and choose Options from the left pane (under Help). In 2007, click the Office button and then click the Word Options button (in the left right corner). Choose Proofing in the left pane. (Can't remember what it is in 2007, if someone needs it, I'll look it up.) Click the AutoCorrect Options button in the AutoCorrect Options section. Click the AutoFormat As You Type tab. Make sure the Fill Formulas In Tables To Create Calculated Columns option is checked. Click OK twice. I meant to include that information and just ... forgot! Thanks for asking, but I'm sorry you had to! It's kind of an odd option to find disabled and I don't think it'll happen to many people.

ken
ken

I followed the instructions but the formula did not autofill. Any suggestions?

Editor's Picks