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.

Editor's Picks