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.
 
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...

    Accountants who specialize in forensic and valuation services point to electronic data analysis, or big data, as the most pressing issue they’ll face in the coming months, according to results of a new survey released...
    As complex as federal tax can get, at least you're only dealing with one agency: the IRS. But when you get into state and local sales tax, you're coordinating hundreds of jurisdictions that are constantly changing....
    All that was needed on Tuesday was a voice vote for the House of Representatives to pass a bill that would prevent state and local governments from taxing access to the Internet.Now the ball is in the Senate’s court....

    Upcoming CPE Webinars

    Jul 23
    We can’t deny a great divide exists between the expectations and workplace needs of Baby Boomers and Millennials. To create thriving organizational performance, we need to shift the way in which we groom future leaders.
    Jul 24
    In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the integrity of your spreadsheets, but reduce maintenance as well.
    Jul 31
    In this session Excel expert David Ringstrom helps beginners get up to speed in Microsoft Excel. However, even experienced Excel users will learn some new tricks, particularly when David discusses under-utilized aspects of Excel.
    Aug 5
    This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.