Excel speed tips - online tutorial

AccountingWEB has formed an alliance with US online training provider CPE Link. Find out how regular contributor David H Ringstrom CPA can help you boost your spreadsheet skills.

We all know that Excel is a fundamental tool for accounting, but one of the things that brought ExcelZone into existence was the realisation that accountants may be learning most of their expertise through trial and error. This means it can take them longer than necessary to get things done and increases the risk of spreadsheet errors.

The point of our community is to share ideas and tips for everyone’s benefit. To that end, we have formed an alliance with US online training specialist CPE Link, which features tutorials from regular AccountingWEB contributor David H Ringstrom CPA.

He has compiled a range of resources, downloadable webcasts, self-study courses and training bundles ranging from $39 to $109. As part of AccountingWEB’s partnership with CPE Link, we will be posting extracts and links to the full range of training materials over the coming months.

“Accountants are now seeking Excel training in droves. CPE Link has seen participation grow substantially in both its live webcasts and self-study programs covering Excel.  Some of the most popular topics include speed tips, pivot tables, macros, advanced formulas, and creating charts,” commented CPE Link program director Sue Anderson.

To kick the series off, we’re borrowing an idea or two from David Ringstrom’s Excel Speed Tips webcast,
a whirlwind tour of some of the tricks he uses speed up his Excel use every day.

Formatting rows in Excel
Sometimes information starts blurring together when you’re using large Excel data sets. One of the most common ways to improve navigation within the rows is to alternate the row shading. Among the collection of time-saving features introduced in Excel 2007 is a gallery of Style options that make this fast and easy to achieve.

To change the colour of every other row in an Excel 2007 or Excel 2010 worksheet:

  1. Select a single cell within your list of data.
     
  2. Choose Format as Table from the Styles section of the Home ribbon, and then choose a colour scheme, as shown below.
     
  3. Excel automatically determines the cell coordinates of your table. If you choose the My Table Has Headers option, then Excel will add Filter arrows at the top. As an added bonus, if your table is longer than what you can see on a single screen, your header row contents will move into the worksheet frame when you scroll down.
     
  4. To eliminate the table, choose Convert to Range in the Tools section of the Design tab that appears when you click on the table. The formatting remains in place, so to eliminate it you can either choose new formatting, or use the Clear Formatting command in the Editing section of the Home ribbon.

Other time-saving topics covered in David H Ringstrom's Excel speed tips tutorial

  • Keyboard shortcuts and hidden menus to speed your way through work
  • Filter and AutoFilter – what’s the difference between them, and how they can help.
  • Overlooked features that can save you time
  • Why the SUBTOTAL function is far superior to SUM

Have a look at the full Excel curriculum to see if there’s a tutorial covering the Excel topics that most interest you.