I was recently asked to give a 5-minute presentation on any Excel topic for a “Lunch and Learn” session. Five minutes?!?!? That is not much time. And besides, the Internet is chock-a-block full of Excel productivity tips, so how could I narrow it down to just one tip? Also, I knew very little about the audience except to assume they were professionals who were not daily users of Excel. They’re not beginners, but they only use Excel occasionally.
I decided the one feature in Excel that most people could be using but aren’t, is Excel tables. Here’s why you’ll want to use this f
eature:
- It is so simple to implement. Just hit Ctrl-T from the keyboard. (Or use the “Format as Table” button from the Excel ribbon.)
- Automatically sizes itself to accommodate existing rows and columns (right up to the first fully blank row and blank column).
- Automatically expands to accommodate a new row or column when you type in cells adjacent to the existing table.
- Automatically formats your list of data using alternately shaded rows, and a contrasting header row, for easy reading and a pleasant look. (If you don’t like the shading, you can use the Ribbon to change to a different Table Style.)
- Automatically includes Auto-Filter features (shown as little triangles in each column header).
- Automatically uses meaningful cell reference names in formulas. Instead of the old cryptic A2 – B2 notation, your formulas will look like [EndTime]-[StartTime].
- Automatically assigns a range name to the table (you can either accept the default name or change it). The more you use formulas, the more you’ll appreciate range names!
- … and many more benefits too numerous to list here.
Pretty much the only time I don’t use Excel tables is for information that is not in a list format. In other words, if it is not in a row-and-column group of related information, for example, some financial reports that use multiple subtotal rows.
Here are a couple of really good (and short!) introductions to how to use Excel tables:
Want to become a Data God? Learn Excel Data Tables
http://chandoo.org/wp/2009/09/10/data-tables/
Or if you prefer video, here’s 8.5 minutes by Chandoo:
Introduction to Excel Tables
http://chandoo.org/wp/excel-tutorial/using-tables/
And a very thorough, concise intro over at Contextures (also includes some video tutorials):
Creating an Excel Table
http://www.contextures.com/xlExcelTable01.html