Software

Put Excel 2007's new Table feature to work for you!

The purpose behind tables is to make managing and analyzing related data easier. Learn how to put this new feature to work for you!

One of my favorite additions to Excel 2007 is the table feature, which you might be tempted to ignore. I mean, isn't a spreadsheet really just a table? What's to improve, right? A lot--by formatting a spreadsheet as a table, Excel 2007 extends functionality, almost magically! Things that use to take a few clicks, now require no effort at all.

Perhaps the easiest task is creating the table in the first place! To create a table, select any cell in the spreadsheet (with or without data) and press [Ctrl]+T. When Excel displays the Create Table dialog box, click OK. If your spreadsheet contains header cells, you might need to check the My Table Has Headers option.

Excel adds a green bar format to the table and enables an AutoFilter for each column. If you don't like the default format, choose a new one (there are 60). To do so, simply choose a new one from the Table Styles gallery on the Design tab in the Table Tools ribbon. As you hover over each option, Excel updates the actual table, accordingly, so there's less experimentation and reformatting.

Adding data couldn't be easier.  Select any cell in the row below the last row of data and enter a value—Excel will extend the table format to include the new row. In addition, any formulas that refer to the table will include the new row.

Want totals? Click the Total Row option in the Table Style Options group on the Design tab (on the Table Tables ribbon). This is a toggle option that displays and hides a row of formulas at the bottom of the table. Notice that as you scroll down, the heading labels are still visible, without a single keystroke from you!

The total row sums values in the last column only, but you can quickly add more formulas. Each column in the total row has a dropdown. Simply choose a function—and Excel does the rest!

If you add a formula to the first empty column to the right, the table extends to include this formula and copies the new formula to the remaining rows! (Be sure to update the temporary heading label.) Below, you can see what happens when Excel encounters a simple expression in cell H2. Excel knows you're working in a table and assumes that you want a consistent formula.

If the table assumes too much, simply undo its work by choosing an option from the AutoCorrect dropdown.

I've barely scratched the surface, but these are the features that I think most users will put to use the most. Many common spreadsheet tasks are now easier to implement and more intuitive. I'm in favor of anything that means less work for me!

Are you using Excel 2007 tables? If so, which feature are you using the most and why?

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.

5 comments
RU7
RU7

The table feature does not work with multiple row headings. I frequently use multiple columns for portions of a piece of data with a group of merged cells above them to group them. For instance, 4 merged cells each for latitude and longitude. The individual column headings for the 4 columns are in the next row: degrees, minutes, seconds, and N/S or E/W. Tables do not work well in this case. It un-merges the top row, adds "ColumnX" headings to the newly blank heading cells, and treats the second heading row as the first row of data.

dugoneill
dugoneill

Will definitely make some of what I do a lot more efficient - one issue though, mentioned in the article is "Notice that as you scroll down, the heading labels are still visible, without a single keystroke from you" - this did not happen for me, I still had to go to View - Freeze Panes. Is there a setting which needs to be set to make it default to freezing the headers in the table?

dafj06
dafj06

How do you create colored maps out of datable to show for example repartition of your revenue worldwide. Does Excel 7 allow you to do that? or any other software

danny
danny

I swear that there is a "trickster" at work on this feature. I seem to get inconsistent results when it comes to keeping the top row titles on view while scrolling an Excel 2007 Table. Yes, when you first activate "Totals Row," the top row titles remain on view. Deactivate the Totals Row and my results vary. As a result, I simply go ahead and freeze the top rows so that I am sure to get consistent results. Has anyone else experienced this with Excel 2007 tables? Danny Rocks The Company Rocks www.thecompanyrocks.com

Editor's Picks