Category: AccountingWEB Excel articles

What Version of Excel is on Your Desktop?

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.



A previous version of this article first appeared on www.accountingweb.com .
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

Excel Tip: Text Boxes

 
By David Ringstrom, CPA


The lowly text box is one of many unsung features in all versions of Excel. While it lurks on the Drawing toolbar of Excel 2003 and earlier, the Text Box icon resides prominently on the Insert tab of Excel 2007 and 2010.

 

Use text boxes anytime you need to put a paragraph or more of text into a spreadsheet. This saves you from having to manually merge cells and wrap text within your worksheet.
In addition, Excel 2007 and 2010 users can create bulleted lists within a text box by selecting text within a text box and making a choice from the right-click menu. To create a text box, simply click the icon, draw a box on your worksheet with your mouse, and then add your text.



A previous version of this article first appeared on www.accountingweb.com .
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.

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.



A previous version of this article first appeared on www.accountingweb.com .


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

Free Spreadsheet-Based Form 1040 Available for 2011 Tax Year

By David H. Ringstrom, CPA


Glenn Reeves of Burlington, Kansas, recently released his fifteenth annual spreadsheet-based version of the U.S. Individual Tax Return, commonly known as Form 1040. Since 1997, Reeves has pursued this “labor of love,” which means he allows any taxpayer to download and use the spreadsheet for free.


Reeves' spreadsheet empowers tax payers to use their tax return spreadsheet applications, such as Microsoft Excel, OpenOffice, or Gnumeric Portable. As you can see in Figure 1, the form closely mirrors the official IRS format, but Reeves doesn't guarantee that the IRS will accept printed versions of the form. He personally files his return online after using the spreadsheet to compute his return.


Figure 1: Reeves has updated his Excel-based version of IRS Form 1040 for the 2011 tax year


The 2011 version of the spreadsheet includes both pages of Form 1040 as well as the following supplemental schedules:
  • Schedule A – Itemized Deductions
  • Schedule B – Interest and Ordinary Dividends
  • Schedule C – Profit or Loss from Business
  • Schedule D – Capital Gains and Losses, along with its worksheet
  • Schedule E – Supplemental Income and Loss
  • Schedule L – Standard Deduction for Certain Filers
  • Schedule SE – Self-Employment Tax
  • Form 6251 – Alternative Minimum Tax – Individuals
  • Form 8949 – Sales and Other Dispositions of Capital Assets


The spreadsheet also includes several worksheets:
  • Schedule D Worksheet
  • Line 10 – State and Local Income Tax Refund Worksheet
  • Lines 16a and 16b – Simplified Method Worksheet taxable annuities and pension benefits
  • Lines 20a and 20b – Social Security Benefits Worksheet
  • Line 32 – IRA Deduction Worksheet
  • Line 44 – Qualified Dividends and Capital Gain Tax Worksheet
  • Line 51 – Child Tax Credit Worksheet


Five additional worksheets round out the tool:
  • W-2 input forms that support up to four employers for each spouse
  • 1099-R retirement input forms for up to four payers for each spouse
  • SSA-1099 input form to record Social Security Benefits
  • A tax table
  • Change log that records revisions to the spreadsheet


All of the worksheets in the 1040 workbook are password protected. Most of the underlying formulas are hidden, but you're free to add new worksheets to the file or create links to other workbooks. Reeves notes that the spreadsheet is constructed in such a fashion that proper use of the spreadsheet means a user won't need to access any of the protected cells. New this year, though, is the ability to add comments to the unprotected cells, which wasn't possible in previous versions. As shown in Figure 2, the spreadsheet also offers some limited error checking.


Figure 2: The spreadsheet offers limited error-checking prompts.


Although Reeves gives the spreadsheet away, he does accept appreciation contributions, which he reports on his tax return as income. He also donates 10 percent of any contributions to his church. Spreadsheet-based versions of Form 1040 are available for tax years 1996 through 2011 on Reeves' website.



A previous version of this article first appeared on www.accountingweb.com .


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

Excel Tip: Table Feature Enables Effective Analysis

By David H. Ringstrom, CPA


Excel 2007 and 2010 users often overlook the useful Table feature, which can streamline data analysis. This feature is a major overhaul of the List feature that has long lurked on the Data menu of earlier versions of Excel. In this article, I'll introduce you to the Table feature and demonstrate several ways it can help you work faster in Excel.
When you convert a range of data in a worksheet to a table, several things happen simultaneously, as shown in Figure 1:
  • Excel places a Filter arrow at the top of each column.
  • Excel applies a table-style format, which shades alternating rows.
  • A Design tab appears in the ribbon, from which you can manage aspects of the table.

Figure 1: Excel's Table feature simplifies data analysis.

Other benefits of tables include:
  • The headings from the first row move up into the worksheet frame when you scroll down if the entire list can't be displayed on the screen. This means you don't have to freeze the worksheet panes to keep the title row in view.
  • Pivot tables based on tables have better integrity, as tables automatically expand to incorporate new data that you append. You must still refresh the pivot table when you add data to the table, but you won't need to manually resize the source data range.
  • Formulas that you add within the table get copied down the entire column automatically, as shown in Figure 4.
Long-time Excel users may remember a Table command on the Data menu of Excel 2003 and earlier. This command was renamed Data Table in Excel 2007 and later, and it appears under the What-If Analysis button on the Data tab of the ribbon. A Data Table shows you how changing one or two variables in a formula will affect the outcome. Conversely, the Table feature is used to analyze lists of data.
To create a table, follow the numbered steps in Figure 2.


Figure 2: You can convert a list to a table with four mouse clicks.



You can then add a Total Row as shown in Figure 3. Formulas within the Total Row only tally the visible rows, making it easy to view real-time statistics as you filter the table.
Figure 3: The Total Row in a table only tallies the visible rows.



Figure 4 illustrates another special characteristic with regard to formulas that you add to tables. You may wish to turn off the Use Table Names in Formulas setting, which appears in the Formulas section of Excel's Options window – the formula in Figure 4 would appear as =[@[Total Sales]]/[@[Cases Sold]] if I had left this option on.
Figure 4: Formulas that you add to a table are copied down automatically.



At any point, you can convert a table back to a normal range – click the Convert to Range button on the Design tab. Keep in mind that this command doesn't remove the formatting or total row. To save time, you may wish to turn off any undesired table features before you convert your data back to a normal range.



A previous version of this article first appeared on www.accountingweb.com .
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.

5 Things CPAs Should Know about Excel Macros

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!

Click here to read the full text of the article at AccountingWEB.com.

Excel Tip: Multiple criteria SUM, MIN, and MAX formulas

Free spreadsheet-based Form 1040 available for 2010 tax year

By David H. Ringstrom, CPA


Glenn Reeves of Burlington, Kansas, has created a free Microsoft Excel-based version of the 2010 U.S. Individual Tax Return, commonly known as Form 1040.
The spreadsheet includes both pages of Form 1040, as well as these supplemental schedules:
  • Schedule A – Itemized Deductions
  • Schedule B – Ordinary Interest and Ordinary Dividends
  • Schedule C – Profit or Loss from Business
  • Schedule D – Capital Gains and Losses, along with its worksheet
  • Schedule L – Standard Deduction for Certain Filers
  • Schedule M – Making Work Pay Credit
  • Schedule SE – Self-Employment Tax
  • Form 6251 – Alternative Minimum Tax – Individuals


The spreadsheet also includes several worksheets:
  • Line 10 – State and Local Tax Refund Worksheet
  • Lines 16a and 16b – Simplified Method Worksheet taxable annuities and pension benefits
  • Lines 20a and 20b – Social Security Benefits Worksheet
  • Line 32 – IRA Deduction Worksheet
  • Line 42 – Deductions for Exemptions Worksheet
  • Line 44 – Qualified Dividends and Capital Gain Tax Worksheet
  • Line 51 – Child Tax Credit Worksheet


Five additional worksheets round out the tool:
  • W-2 input forms that support up to four employers for each spouse
  • 1099-R Retirement input forms for up to four payers for each spouse
  • SSA-1099 input form to record Social Security Benefits
  • A tax table
  • Change log that records revisions to the spreadsheet


As of this writing, Reeves is putting the finishing touches on the 2010 versions of Schedules D (Capital Gains and Losses), E (Supplemental Income and Loss), and Form 2441 (Childcare and Dependant Expenses). The schedules will be available for free download in a separate workbook.


All of the worksheets in the 1040 workbook are password-protected, and most of the underlying formulas are hidden, but you’re free to add new worksheets to the file, or create links to other workbooks. As you can see in Figure 1, the form mirrors the official IRS format. The protection also means you can’t add comments to cells within the forms, or make notes out to the right. The spreadsheet also includes some basic error-checking features, as shown in Figure 2.


 
Figure 1: Glenn Reeves has updated his Excel-based version of IRS Form 1040 for the 2010 tax year



Figure 2:The spreadsheet contains basic error-checking prompts.
 


Reeves clearly states that the spreadsheet is available for free, but he does accept appreciation contributions. As you might expect for someone so intimately familiar with U.S. tax law, Reeves will report all contributions as income, but will also donate 10 percent of any proceeds to his church.


This free spreadsheet enables just about anyone to use Microsoft Excel to prepare and print his or her entire 1040 return. Along with the actual forms, the spreadsheet includes some IRS documentation, as well as links to download official IRS forms and instructions. Excel-based versions of Form 1040 are available for all years from 1996 through 2010.


The spreadsheet is available at www.excel1040.com.



A previous version of this article first appeared on www.accountingweb.com .
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

Time for Smaller Companies to Learn XBRL

You can run, but you can’t hide! The three-year phase-in period for the use of eXtensible Business Reporting Language (XBRL) is about to hit the last group in the cycle. Effective June 15, 2011 smaller reporting companies (and all other remaining filers using U.S. GAAP) must now attach an exhibit with all their periodic filings. The XBRL “tags” in the exhibit will allow users to easily search for and access specific financial information.

Click here to read the full text of the article at AccountingWEB.com.

Microsoft celebrates 25 years of Excel

By David H. Ringstrom

Microsoft Excel has come a long way from its humble beginnings in September 1985 as a spreadsheet program for the Mac environment. This year, Microsoft is celebrating 25 years of Excel with a Facebook-based contest that encourages users to post their favorite Excel memory – with a social media twist.

 

Contestants must, in turn, encourage their friends to vote for their memory.  Two winners will receive an Xbox & Kinect package. Be sure to read the rules and conditions for the contest, which runs through January 31, 2011.

 

Microsoft Excel was actually a late-comer to the spreadsheet party, preceded by Visicalc in 1981, Microsoft’s MultiPlan in 1982, and Lotus 1-2-3 in 1983. In November 1987, Excel 2.0 for Windows debuted, along with an updated Mac version. At the time, Lotus 1-2-3 continued to rest on its MS-DOS laurels, and within a year Microsoft Excel sales began outstripping Lotus 1-2-3.

 

Several catch-up attempts were made by Lotus – does anyone remember WYSIWYG? – but Microsoft Excel already had won the spreadsheet war. Interestingly, Lotus 1-2-3 still can be purchased today as part of IBM’s Lotus SmartSuite, although the last update to the software was in 2002. To get a true sense of how much spreadsheets have evolved in the past 30 years, give Visicalc a quick spin (Tip: press the / key to access the menu, and use /SQ to exit the program).

 

There have been 11 Windows-based versions of Excel over the years, culminating with the latest version Microsoft Excel 2010. In the past 25 years, the competitive landscape has changed dramatically, with the head-to-head competition with Lotus 1-2-3 and Quattro Pro shifting to a face-off between free alternatives such as OpenOffice and GoogleDocs.

 

Who knows what the next 25 years will bring with regard to spreadsheets?



A previous version of this article first appeared on www.accountingweb.com .
 

 

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.

Beyond VLOOKUP: Mastering Advanced Excel Formulas

Many users rely on VLOOKUP to return data from other locations in a worksheet, but is that the most efficient approach?

Click here to read the full text of the article at AccountingWEB.com.

Under the Radar Part 1: Five new features in Excel 2010

By David H. Ringstrom


I have to admit, I was underwhelmed when Office 2010 was released. You’d think that an Excel expert would like nothing more than a shiny new version of Excel, but my initial experience with the new user interface in Excel 2007 left me feeling chastened.


Granted, after about two weeks, and with the use of this transition tool from MicrosoftI was humming along with Excel 2007. There’s also a set of tools available to help with the transition to Office 2010.


At any rate, I installed the beta of Office 2010 last year, but didn’t initially see much allure in Excel 2010. However, once I started using Excel 2010 regularly a couple months ago, I started noticing subtle, but significant improvements that the average user might not notice. In this first of a two-part series I’ll discuss five of my favorites that I call Under the Radar features. This series also heralds the start of a new feature on AccountingWEB, where I’ll be writing articles about Excel, but also demonstrating the techniques in an accompanying video.


1.In Excel 2010, you’re free to tinker with the ribbon to your hearts delight. Customizing the Excel 2007 interface was limited to the Quick Access Toolbar, but if you get more than about eight icons on it, it became unusable because you’d lose track of which icon did what. If you have Excel 2010 and, like me, were frustrated because there’s not a Pivot Table icon on the Data tab, simply add one, as shown in Figure 1. Even better, I’ve added over a dozen frequently used commands to my Home tab so that I spend far less time traipsing through the tabs. To get started, right-click on the ribbon and choose Customize the Ribbon.


 
Figure 1: Excel 2010 allows you to add or remove sections of the ribbon.


2.The File menu is back. Office 2007 apps all have a round logo in the top left-hand corner known as the Office button. The Office button functions like a File menu, but it’s clunky to describe. I’m glad I can say “click on File” again, instead of “click that round button up in the left-hand corner.”


3.The Recently Used File list has a new Recent Places list, too, as shown in Figure 2. This makes it easy to get to frequently used files and folders. Even better, these lists are scrollable. Choose File, Options, and, in the Advanced section, change the Show This Number of Recent Documents setting to 50, and you’ll always have anything you recently worked on at your fingertips.


 
Figure 2: The Recent Folders list is a helpful addition in Excel 2010.


4.Excel 2007 introduced the ability to pin items to the Recent Items menu, but pinned items would move down on the list as you opened other files. In Excel 2010, pinned files, or folders, for that matter, always remain at the top of the list, as shown in Figure 2.


5.Ever open a blank spreadsheet, noodle around, and then close without saving, and have one of those “D'oh! I should have saved that!” moments? Excel 2010 minimizes those by automatically archiving files, as shown in Figure 3. To access copies of unsaved files, choose File, Info, Manage Versions, and then Recover Unsaved Workbooks.


 
Figure 3: Excel 2010 often saves a temporary copy of a workbook when you choose Don’t Save.


Intrigued by what’s new in Excel 2010? You can download a free 60 day trial from Microsoft. If you’re already using Excel 2010, please post your own favorite Excel 2010 features in the comments section below.



A previous version of this article first appeared on www.accountingweb.com .
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

Under the Radar Part 2: Five new features in Excel 2010

By David H. Ringstrom


In part one of this two-part series, I introduced you to five subtle changes that Microsoft added in Excel 2010 that I’ve found quite helpful. In this second part of the series, I’ll discuss five more features that have boosted my productivity in Excel.



1.Excel 2007 introduced the ability to filter more than one item at a time, which was a great advance. However, it’s also tedious clicking and unclicking checkboxes in the dropdown list. As shown in Figure 1, filter lists in Excel 2010 have a Search box in which you can type a keyword and automatically select just those items from the list. No need to touch a tiny checkbox!


 
Figure 1: The Search field makes it far easier to select from a large list.


2.The venerable right-click menu is on steroids now, particularly with regard to the Paste Special command. As you can see in Figure 2, icons mean you can generally avoid launching the Paste Special dialog box.


 
Figure 2: Look at this fabulous right-click menu!


3. The Table feature was a great advance in Excel 2007. Take a list of data, choose Insert, and then Table, and Excel adds filtering arrows, formats your data for readability, and automatically copies formulas down the length of the table as you enter them. Further, scroll down the worksheet and you’ll see that the column headings move into the worksheet frame. As shown in Figure 3, Excel 2010 takes this a step further by also moving the filtering arrows to the worksheet frame. This means you no longer have to keep scrolling to the top of the list to change filter criteria.


 
Figure 3: Filtering arrows within tables move into the worksheet frame automatically in Excel 2010.


4.There’s a much smarter fill-handle in town now. You might not have realized that double-clicking the fill handle (that little notch in the lower-right-hand corner of the selected cell) would copy a formula or value down the length of a column and stop when a blank cell is encountered in the adjacent column. However, I often want to double-click and copy data when there’s not anything in the adjacent column. As shown in Figure 4, as long as there’s a row of headings in the section of the spreadsheet that you’re working in, you can double-click and Excel 2010 will copy the data down. This is particularly helpful when you’re compiling data to import into an accounting package, where certain columns are blank and others have required inputs that are the same on every row.


The double-click improvements don’t stop there, though. In Figure 5, if I double-click to copy the formula in cell B3, Excel 2010 stops at row 10 and does not overwrite my SUM formula in the total row. Try this in any other version of Excel and your total row will get copied over.


 
Figure 4: Double-clicking the fill handle no longer requires immediately adjacent data in Excel 2010.


 
Figure 5: Excel 2010 doesn’t overwrite your totals when you double-click the Fill Handle.


5.I’m a big fan of using Data Validation to create in-cell dropdown lists. I’m also a fan of storing the contents of such lists on a separate worksheet for safekeeping. Up through Excel 2007, the Data Validation feature wouldn’t let you refer to a list on another worksheet. Of course, you could work around this by using a named range, but it’s nice to have the option when you need it to just refer to a list anywhere in your workbook.

Figure 6: Data Validation lists can now reside on other worksheets.


That’s my rundown of my favorite improvements in Excel 2010. If you have a favorite Excel 2010 feature that I didn’t mention, click the Post a Comment button below and share your thoughts. If you’re not using Excel 2010 yet, download a free 60 day trial from Microsoft.



A previous version of this article first appeared on www.accountingweb.com .


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

Excel 2010 Tips and Tricks: Part 1

Excel guru David Ringstrom shares some of his favorite tips to make your time spent in Excel easy and efficient.
David Ringstrom, CPA, shares some new features of Excel 2010.

Click here to read the full text of the article at AccountingWEB.com.

Excel 2010 Tips and Tricks: Part 2

Part Two of Excel guru David Ringstrom's tips guaranteed to turn you into an Excel pro!
David Ringstrom, CPA, shares more features of Excel 2010.

Click here to read the full text of the article at AccountingWEB.com.