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.
As with part one, there’s an accompanying video in which I demonstrate the features.
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
