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

The Making of a Great Webcast Speaker

It goes without saying that if you are going to teach continuing education, you have to know your stuff. But the requirements of a good presentation go well beyond having knowledge of your subject—especially in an online presentation.

At CPE Link, post-event evaluations continue to support that a speaker’s presentation skills must be top notch. You can have outstanding knowledge (and score a perfect 5.0) but if your presentation skills are off, everything falls flat to the attendees. So, how do you ensure a great online presentation? Here are some suggestions:

Rule #1: Mix it up! Simply reading your PowerPoint slides is the quickest way to earn a failing grade. Show a video clip, share a PDF form, show a related website, or demonstrate a software application. Give the attendees something with visual interest. Top-rated speaker, David Ringstrom demonstrates Excel tips directly in Excel by screen sharing most of his presentation. He even toggles back and forth between Excel 2007 and Excel 2003 to highlight the differences. David scores high! Larry Perry, who teaches a variety of accounting, auditing and nonprofit topics, often shares PDFs of key forms during his webcasts as he discusses the how and why to filling them out.

Rule #2: Engage the audience. Good presenters invite participants to ask questions and of course, answer them promptly. In his ethics webcasts, Art Berkowitz uses case studies to set up various ethical scenarios and then asks participants to share what they would do. This format scores very high with attendees. They love practical examples they can understand and easily relate to their own practices. A participant raved, “best CPE seminar on Ethics I have attended.”

Rule #3: Give us some energy! Without a face-to-face connection with the audience, some speakers sound a little dull, but good speakers keep their energy level up. They stand up, walk around, whatever it takes to help animate their voice. Federal Tax guru, Vern Hoven adds lots of humor to his webcast presentations to keep people’s attention and keep things interesting. One participant said, “Great! Enjoyed the humor!! Will be back for more webinars with Mr. Hoven.” Another said, “Thought Vern was a fabulous speaker who made CPE a little bit entertaining.”

Rule #4: Keep on pace. Rushing through the material ranks as a major offense. Having “bonus” material ready (just in case) is a better strategy than trying to cram too much into the core of the presentation. Pace may be one of the most challenging elements to master. Going too fast or too slow can receive poor ratings. And all participants may not agree. We have seen both “much too fast a pace” and “should have moved along much faster” on the same course evaluation!

Well, that’s the CPE biz. Happy teaching!

Tweet about this on TwitterShare on FacebookShare on LinkedIn

Firm CPE Center Launches!

CPE Link’s live webcasts serve a national audience. We see all types of attendees—from sole practitioners to firms with groups of 3 to 10 participating in our programs. In serving these larger groups we have discovered that firms have special needs when it comes to CPE. We have taken their suggestions and requests for functionalities on our website and built a special place just for firms. It’s called the Firm CPE Center.

Using the Firm CPE Center, you can manage online CPE for your professional staff. Your administrator can register individuals or groups to live webcasts or self study courses or allow everyone to register themselves. The Firm CPE Center offers a dashboard for CPE activity tracking, e-mailed confirmations and reminders plus online evaluations and certificates of completion.

You can also control your CPE budget with bulk purchases of CPE with our new Firm Advantage Pass. Choose live webcast hours, self-study, or both. (100 hours minimum). Your Advantage Pass hours may be used by anyone in the firm. Staff can register at the last minute and cancel easily if needed. And your hours never expire!

I invite you to create your firm profile and check out the Firm CPE Center. Let us know what you think!

Tweet about this on TwitterShare on FacebookShare on LinkedIn