Excel Training

Hiding and unhiding rows and columns are mundane tasks that many users take for granted. However, sometimes simple tasks can trip up Excel users, like unhiding just one row or column within a hidden set. Other users don’t know simple keystroke commands that can streamline hiding and unhiding columns or rows. In this article, I’ll explore these techniques as well as discuss two powerful alternatives to manually hide and unhide rows and columns. I’ll also discuss how to re-enable an Excel keyboard shortcut that’s disabled in any operating system subsequent to Windows XP.
Continue reading at AccountingWEB.

This article is written by one of our esteemed Instructors, David Ringstrom. David is a CPA and owner of Accounting Advisors, Inc., an Atlanta-based spreadsheet consulting firm that he started in 1991. Throughout his career David has spoken at conferences on Excel, and written dozens of freelance articles about spreadsheets. He presently writes for AccountingWEB.com, and offers Excel training and consulting services nationwide.


By guest blogger, David Ringstrom . . .

As an instructor leading dozens of Excel classes for CPE Link each year, I find that the preponderance of attendees use Excel 2007. A surprising cadre is still holding on to older versions such as Excel 2003, or even Lotus 1-2-3. At some point I’ll need to add Office 365 to my presentations, but I’ll wait until I see folks using it. Microsoft just announced price cuts of up to 20% for the service, likely in hopes of sparking demand.

As daunting as it is to lead classes that cover three different desktop versions of Excel, soon I’ll be adding a fourth version. Excel 15, likely to be dubbed Excel 2012, is currently in a technical preview, or pre-beta, phase. This allows select customers to provide feedback to Microsoft prior to the next phase, which will be a public beta this summer. A final release of the next version of Office is expected by the end of this year.

Microsoft has been particularly tight-lipped about Excel 15 and its other Office suite companions, but information is starting to leak out. Windows 8, currently in beta testing, sports a new touch-optimized Metro look that replaces icons with onscreen tiles. My observation over the years is that Microsoft tends to make major changes every other Office version, so we could be in for changes that are as initially jarring as the Ribbon interface introduced in Office 2007.

Microsoft maintains three levels of support for their products: mainstream, extended, and online self-help. During the mainstream phase, Microsoft issues service packs and provides full levels of support. In the extended phase, primarily security patches are issued, but paid support is still available. The online self-help phase allows users to use the Microsoft Knowledgebase to try to fix problems on their own. Office 2007 enters a five-year extended support phase on October 9, 2012, while extended support for Office 2003 ends April 4, 2014.

Unless you’re chomping at the bit for yet another new Excel interface, my recommendation is upgrade to Office 2010 this year so that you can stay on a supported version and let the dust settle on Microsoft’s Metro and cloud computing changes.

This list comes courtesy of David Ringstrom, CPA, CPE Link instructor and Excel expert.

1. Macros are programming code that you can add to your Excel spreadsheets to automate repetitive tasks. Macros can be as simple as a single line of code to carry out a task, such as typing your company’s name. Other macros interact with accounting programs, download data from the Internet, or collect information from users via custom forms—you’re often limited only by your imagination.

2. You don’t need to know anything about programming. Excel’s Macro Recorder feature makes it easy to create your own macros. Think of it as Excel’s version of a camcorder, where you click Record and have the actions you carry out transformed into programming code that you can play back over and over. This allows you to automate simple tasks such as cleaning up a text file that you download from a web site.

3. Many of the tools that you use for macros in Excel 2007 and 2010 reside on a hidden Developer tab. In Excel 2007, click the Show the Developer tab checkbox on the first Excel Options window. In Excel 2010, right-click on the ribbon, choose Customize the Ribbon, and then click the checkbox for the Developer tab. You’ll find the Macro Recorder button on the Developer tab, as well as a Visual Basic button that enables you to get behind-the-scenes to your macros in the Visual Basic Editor.

4. You don’t have to create your own macros. Programming isn’t for everyone, but knowing that it’s possible to have a tool that carries out a repetitive task dozens, hundreds, or even thousands of times can be a huge time saver for your company. You can hire an expert to create a macro-enabled spreadsheet that allows you to accomplish your task with the click of a button.

5. The best way to learn about macros in Excel is to have a project in mind that you wish to automate. Many of Excel’s features seem to fall into the category of “why would I want to know how to do that”, but given the proper context you have that “Oh! I get it!” moment.

If you find yourself carrying out the same steps over and over again in Excel, try experimenting with the Macro Recorder, or do a Google search on automating your task. You’ll be surprised at the wealth of information that’s just a click away!

Imagine this situation. Building contractor Jones has asked CPA Smith to review payables and receivables. “I want to be sure that our vendors have billed us as agreed,” says Jones. Smith wants to send a staff person to Jones office to compile the information into an Excel worksheet for further analysis and reporting.

How can Smith design a spreadsheet for data entry and analysis that
• helps speed data entry, is easily modified, and is responsive to changing assumptions?
• provides instructions to guide the data entry process?
• is also suitable for presentation to the client?

What’s more, since Smith won’t be doing the data entry himself, how can he include automation to give feedback during the data entry process and provide validity checks to prevent “garbage-in garbage out” problems?

If you don’t know the answers these questions and would like to, you may want to participate in the May 12 webcast Building Interactive Excel Spreadsheets for Data Capture.

Or consider this problem. A frantic client calls Ray Knight, CPA and owner of Knight Consulting. “My accounting software won’t work, the software vendor is out of business, and the data isn’t compatible with any other available software. Help!”

Ray helps the client set up another accounting software package for current year operations, but the client still needs access to information from prior years. Ray received a data dump from a recovery specialist and is considering the next steps. What issues should Ray consider when integrating data for use in Excel? How can he modify the dataset? How can he design a spreadsheet that will provide interactivity with the client for ad hoc analysis and reporting? You can find out the answers to these and more questions in the May 12 webcast Using Excel with External Data.

Can’t make the May date? No worries. Both webcasts will be presented again in July by L. Keith Jordan, CPA. In addition to 30 years experience in accounting, supervision, and management, Jordan is an expert in IT and business applications.

Why not make a day of advancing your Excel skills?