Recovering Unsaved Excel Workbooks

You’ve likely experienced that flash of panic that occurs when suddenly the Excel spreadsheet you’ve been laboring over unexpectedly vanishes off of your screen. I’ve certainly gotten lost in my work innumerable times over the years and forgotten to save periodically. This often sets the stage for any of a number of bad things to happen: your computer crashes; the power goes out; you close the wrong workbook without saving—the list goes on and on. Fortunately in Excel 2010 and later you have pretty good odds of being able to recover your work.

Microsoft Excel has long had a Document Recovery feature that in many cases will present a list of workbooks that were open at the time that Excel crashes unexpectedly. Ostensibly Excel creates a back-up copy of your document every 10 minutes, and it’s these back-up files that are presented to you after a crash. However, if you close the Document Recovery window without making a selection, those back-up files are lost forever. Fortunately in Excel 2010 and later you have a couple of additional recovery options.

Learn more about Excel documentation management.

Tweet about this on TwitterShare on FacebookShare on LinkedIn

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

Resolve to Automate Repetitive Excel Tasks in 2014

By CPE Link Instructor David Ringstrom, CPA

A number of years ago I coined the phrase “Either you work Excel, or it works you.” I can’t count the number of knowing nods I’ve gotten when spreadsheet users relate to the latter. Even proficient Excel users can sometimes get snarled into a quagmire of repetitive work in Excel. However, for those up to the challenge, Excel does offer a way to lighten your workload.

Depending upon your desktop version of Excel, there’s a Record Macro button tucked away on the View tab or the Tools menu. Think of this is a built-in video camera in Excel, except that instead of simply recording a video of what you’re doing in Excel, it actually transcribes your actions into programming code that you can play back on demand. Even if you’ve never even seen a line of programming code, you can use the Macro Recorder to take your first step into a new realm of possibilities in Excel.

In simple terms, a macro is one or more lines of programming code that can be played back. A macro can be as simple as centering text across a range of columns, or an elaborate arrangement of programming code that takes minutes or even hours to complete. Many users shy away from macros in the same way that they eschew pivot tables in Excel—the terms are seemingly incomprehensible and unapproachable. Fortunately the macro recorder offers an easy on-ramp for anyone ready to get his or her feet wet. As for Pivot Tables, Excel 2013 has your back there as well. Click anywhere within a list of data, and then choose Recommended Pivot Tables from the Insert tab of the ribbon to receive point-and-click assistance.

Back to macros—you’re only moments away from creating your first one:

  • Excel 2007 and later: Choose View, Macros, and then Record Macro.
  • Excel 2003 or Excel for Mac 2011: Choose Tools, Macro, Record New Macro.

Carry out these actions in the Record Macro dialog box:

  • Enter MyFirstMacro in the name field.
  • Choose This Workbook from the Store Macro In list.
  • Click OK to start recording.

Next, type the words “Hello, World!” in any blank worksheet cell, press Enter, and then turn off the Macro Recorder:

  • Excel 2007 and later: Choose View, Macros, and Stop Recording.
  • Excel 2003 and Excel for Mac 2011: Choose Tools, Macro, Stop Recording.

To test your work, carry out these steps in a blank worksheet:

  • Excel 2007 and later: Click the Macros button on the View tab.
  • Excel 2003 or Excel for Mac 2011: Choose Tools, Macro, Macros.

Next, double-click on MyFirstMacro in the Macro dialog box to run the macro. The words “Hello, World!” should appear in the active cell within your worksheet.

To save a macro for later playback, activate the workbook within which you recorded the macro, choose File, Save As, and then:

  • Excel 2007 and later: Choose either Excel Macro-Enabled Workbook or Excel 97-2003 Workbook from the File Type list, and then save your workbook.
  • Excel 2003 and Excel for Mac 2011: Simply save the workbook in the usual fashion.

Your macro can now be played back anytime the workbook is open in Excel, and even better, the macro can be run in any other workbook that is open at the same time as the macro workbook. You’re now among the initiated, so try using the Macro Recorder to automate repetitive tasks that you encounter, such as removing extraneous rows from an accounting report that you’ve exported, or adding your contact information to the bottom of a spreadsheet that you’re about to send out of the building. You’ll quickly find that there are limits to the Macro Recorder, but if you find creating macros intriguing, there’s a whole new world in Excel available for you to explore. If nothing else, being aware of Excel’s automation capabilities means you can seek help in the future when repetitive spreadsheet tasks become part of your daily grind.

Do note that in Excel 2007, 2010, or 2013 that if you save your workbook as an Excel Workbook—with the .xlsx extension—that your macros will be discarded. If you catch yourself doing this accidentally, immediately resave your workbook as a Macro-Enabled Workbook (.xlsm extension) or Excel 97-2003 Workbook (.xls extension) to preserve your work.

For more Macros tips, view David’s online CPE tutorials Introduction to Excel Macros or join him in his Live Webcasts.

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.

Tweet about this on TwitterShare on FacebookShare on LinkedIn

Recovering Unsaved Excel Workbooks

It’s every spreadsheet user’s worst nightmare – you’ve worked on a workbook for a period of time, and then accidentally close it without saving. Or the power goes out, or Excel crashes . . . the list of spreadsheet hazards goes on and on.

If you’re using Excel 2010 or later, there’s a pretty good chance you can mitigate much – but not all – of the risk related to unsaved workbooks. Regardless, in any version of Excel, you can raise the odds of having a recovered copy of your work available after a software crash. Read more at Accounting Web.

This article is written by CPE Link Instructor 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.

Tweet about this on TwitterShare on FacebookShare on LinkedIn

Tricks for Hiding and Unhiding Excel Rows and Columns

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.

Tweet about this on TwitterShare on FacebookShare on LinkedIn

What Version of Excel Is On Your Desktop?

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.

Tweet about this on TwitterShare on FacebookShare on LinkedIn

5 Things CPAs Should Know about Excel Macros

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!

Tweet about this on TwitterShare on FacebookShare on LinkedIn

Take a Day to Pump Up your Excel Skills

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?

Tweet about this on TwitterShare on FacebookShare on LinkedIn