Excel Tables

A new feature in Excel 07 is the table. Why the table you ask? Well it has some unique properties:

• Banded Rows
• Header Filters
• Calculated Column
• Total Row

To create a table, select a single cell in a database, and click on the Table button in the Insert tab.

The banded rows and header filters are immediately noticeable.

But, what is the real advantage of the table besides the aesthetics of the banded rows and the filters which can be used in a database without the table feature?

For me the advantage of a table is when it is used as the source data for a pivot table. The pivot will still need to be refreshed whenever data is added, but the pivot will automatically track the range of the table. In fact, the data source dialog box now asks for a table or range. The table default name for my labor database is “Table1” as shown below. Also, the range of the table is updated when data is added to the bottom row or rightmost column.

Another feature is the calculated column. I have to download labor out of the manufacturing software database and reconcile it to the labor that was reported and paid through the payroll service. Sometimes downloaded data doesn’t have all the information necessary to perform the task. I must add a formula to pick up the week number on each row since we are paid weekly. I use the formula “weeknum” at the column just to the right of the download data. In the following example the download from the manufacturing software stops at column “M” and the “weeknum” formula has been placed into column “N”. Next month, when I download and add to the Excel database the formula in column “N” will automatically copy to the added data.

Now refresh the pivot and filter for the new week to be reconciled.

Using the table saves time, effort and it just looks good too. If you’re not convinced about the look, just click on the design tab and select from a multitude of colorful formats.