The Excel Automation Feature You’re Likely Not Using

By David Ringstrom, CPA

Although Excel 2007 brought us the new ribbon interface, which replaced the traditional drop-down menus, it also gave us a great automation tool known as the Table feature. I find that most Excel users either aren’t aware of this feature, or aren’t fully aware of its capability. Read on to get a high level overview of what’s possible with the Table feature.
The Table feature is actually an update to the List feature that was buried on the Data menu in Excel 2003 and earlier. This feature appears both on the Home tab and Insert tabs of Excel 2007 and later, and is designed to simplify working with lists of data in Excel. Once you make a list into a table in Excel, the dataset takes on special characteristics:

  • Every other row will be shaded.
  • Filtering arrows appear at the top of each column.
  • If your list is too long to appear on a single screen, the headings in the first row replace the column letters in the worksheet frame when you scroll down.
  • A Design tab appears when you click any cell within a table, and from there you can toggle a total row on or off. Click any cell within the Total row to reveal a drop-down list from which you can choose to sum, count, or display other statistics that update automatically as you filter the table.
  • Tables expand automatically when you add columns to the right, or rows below (assuming that the total row is turned off). Further, when you type a formula in cell within a table, Excel automatically copies the formula down the entire column, saving you from having to drag or copy and paste the formula.

To add all of these characteristics to a data set in Excel, carry out any of these steps in Excel 2007 and later:

  1. Select any cell within a list of data.
  2. Carry out one of these steps:
    • Press Ctrl-T.
    • Choose Insert, and then Table.
    • Choose Home, Format as Table, and then choose a style.
  3. At this point you’ll be presented with a dialog box from which you can confirm the cell coordinates for data. Make sure that My Table has Headers is clicked, and then click OK.
  4. All of the above features will now be added to your data.

If you don’t like the automatic formatting that gets applied to a table, click any cell within the table to make the Design tab appear. Click the arrow in the Table Styles section, and then choose another style or click Clear. This will preserve the other functionality but remove the formatting. At any point you can return a table back to a “normal” range of cells by choosing Convert to Range on the Design tab.

Making data into a table improves data integrity in your spreadsheets:

  • Charts based on a table expand automatically as you add new months of data.
  • Pivot tables based on a table automatically “see” new rows and columns of data when you refresh the pivot table.
  • Other features, such as Sparklines in Excel 2010 and later, will automatically display additional data added to a table.

This is only a sampling of the automation features available with Excel’s Table feature.

This post was written by CPE Link Instructor David Ringstrom for his upcoming Live Webcast on June 11, Tackling Excel’s Table Feature.

About the author:
David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB, Going Concern, et.al.

Tweet about this on TwitterShare on FacebookShare on LinkedIn