Seven Quick Excel Tips

By David Ringstrom
 
Here are seven quick Excel tips for: converting data, amortization tables, spanning Excel across multiple monitors, filtering for blank rows in Excel 2010, recovering unsaved workbooks in Excel 2010, identifying duplicate values in Excel 2007/2010, and recovering damaged Excel workbooks. 
 
Data Conversion Tricks
From time to time you may encounter worksheets where dates are stored in yyyy-mm-dd format, such as 2012-12-31 as opposed to 12/31/2012. You can convert such inputs with just a few mouse clicks. 
  1. Select the column or cells containing the dates you wish to convert.
  2. Choose Data and then Text to Columns.
  3. Click Next twice, then on the third tab choose Date, and then YMD.
  4. Click Finish to complete the process.
Instant Amortization Table
You can add an amortization table to any Excel workbook with a few mouse clicks:
  1. Right click on any worksheet tab and then choose Insert. 
  2. Navigate to the Spreadsheet Solutions tab and then double click on Loan Amortization.
Spanning Excel Across Multiple Monitors
Multiple monitor users often launch two different sessions of Excel when they need to see two spreadsheets side by side. Doing so can be viable in certain circumstances, but typically it’s more effective to have a single Excel session that spans both monitors. To do so, open any version of Excel and then double click on the words Microsoft Excel at the top of the screen. Your Excel window should now only cover part of a single monitor. Position your cursor over the title bar and hold down the left mouse button to move Excel up to the top corner of the screen. Next, use your mouse to drag the right-hand corner of Excel across the second monitor.
 
To then see two different workbooks side by side, choose View, Arrange All, and then double click Vertical. In Excel 2003 and earlier, choose Window, Arrange, and then double click Vertical.
 
Excel 2010: Filtering for Blank Rows
The Filter command, which appears on both the Home and Data tabs in Excel 2007 and 2010, allows users to pick and choose which rows to display within a list. However, it can be tedious trying to isolate blank rows from a lengthy spreadsheet. You must click the Filter arrow, unclick All, and then scroll down to the bottom to click Blanks. In Excel 2010, simply type the word Blanks in the search box instead.
 
Excel 2010: Recovering Unsaved Workbooks
It's painful when you accidentally (or even purposefully) close a workbook without saving and then regret it. In Excel 2010, you can sometimes recover your work. To do so, choose File, Info, click the Manage Versions button, and then choose Recover Unsaved Workbooks. Excel 2010 automatically keeps a copy of many (but not all) unsaved workbooks for a few days. 
 
To make the most of this feature, set your AutoRecover option to two minutes instead of the default of ten. To do so, click File, Options, and then Save. Make sure that Save AutoRecover Information settings is set to two minutes and that Keep the Last Autosaved Version if I Close Without Saving is checked as well.
 
Identifying Duplicate Values in Excel 2007/2010
Select a range of cells. Then, on the Home tab, click Conditional Formatting, Highlight Cells Rules, and then Duplicate Values. Select a formatting option and then click OK. To isolate duplicate instances, right click on a highlighted cell, then choose Filter, and then Filter by Selected Cell's Color.
 
Recovering Damaged Excel Workbooks
Many users overlook the Repair Workbook feature in Excel. To access it, navigate to the Open window in the usual manner, and then click once a workbook name. Click the arrow on the right-hand side of the Open button, and then choose Open and Repair. This will sometimes correct problems with a malfunctioning workbook. If this step doesn't resolve your problem, install the free OpenOffice product, and use the Calc spreadsheet program to try to open your damaged Excel workbook. 
 
Related articles:
 
Read more articles by David Ringstrom. 
 
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, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

 

 

You may like these other stories...

How are you planning? What tools do you use (or fail to use) for forecasting? PlanGuru is a business budgeting, forecasting, and performance review software company based in White Plains, N.Y. AccountingWEB recently spoke...
Event Date: October 30, 2014, 2 pm ETMany Excel users have a love-hate relationship with workbook links. For the uninitiated, workbook links allow you to connect one Microsoft Excel spreadsheet to other spreadsheets, Word...
Event Date: September 9, 2014, 2:00 pm ETIn this session we'll discuss the types of technologies and their uses in a small accounting firm office. Included will be:The networked office: connecting everything together for...

Already a member? log in here.

Upcoming CPE Webinars

Aug 21
Meet budgets and client expectations using project management skills geared toward the unique challenges faced by CPAs. Kristen Rampe will share how knowing the keys to structuring and executing a successful project can make the difference between success and repeated failures.
Aug 26
This webcast will include discussions of recently issued, commonly-applicable Accounting Standards Updates for non-public, non-governmental entities.
Aug 28
Excel spreadsheets are often akin to the American Wild West, where users can input anything they want into any worksheet cell. Excel's Data Validation feature allows you to restrict user inputs to selected choices, but there are many nuances to the feature that often trip users up.
Sep 9
In this session we'll discuss the types of technologies and their uses in a small accounting firm office.