Help
Excel is the go-to choice for number crunching and spreadsheet creation, but those are the run-of-the-mill functions. There's a host of untapped tools and techniques within the program. In the following interview, "Excel 2010: The Missing Manual" author Matthew MacDonald discusses his favorite hidden gems and he looks at the new features in Excel 2010.
What are the best Excel tricks?
Matthew MacDonald: Here are a few of my favorites:
1. Page break preview -- In page break preview mode (choose View --> Workbook Views --> Page Break Preview) you can see how your printed worksheet will be split across multiple pages. But even more valuable is the ability to drag a page break to a new place. For example, if you spot some data off to the right side that doesn't fit on your page, you can drag the page break to the right so that it does. When you do this, Excel scales down your entire worksheet to fit the information you want.
2. Recovering unsaved work -- Ever start a new workbook, and then forget to save it when you close Excel in a hurry? Now you can get your lost work back. Just choose File --> Info, click the Manage Versions button, and choose Recover Unsaved Workbooks to find the unsaved spreadsheets that Excel stores automatically.
3. Lookup formulas -- If you understand how to use them, lookup formulas give you a powerful way to copy information from one part of a spreadsheet to another. For example, you can use lookup formulas to create an invoice that automatically inserts the correct product and price information when you type in a product code. Ordinarily, you'd expect this sort of solution to need macros or Visual Basic, but it doesn't.
4. Formula tracing -- Sometimes formulas go wrong, and the result is information that doesn't make sense (or an error code). Excel's formula tracing feature is a big help if this happens in a complex spreadsheet. When you use it, Excel adds arrows that point from the source cells to the formula that uses these cells. Essentially, formula tracing gives you a way to graphically "see" how your formula connects to the rest of your data, and it often helps you find the troublemaking cell that's causing the problem.
5. Charting tricks -- Charts tell a story with your data, and there are plenty of tricks that you can use to make them present that story more clearly and conclusively. For example: changing a chart's scale, adding an overlay, changing the fill of a specific series, inserting text and graphics directly on the chart surface, and so on, all have a dramatic effect. They make the difference between a chart that conveys a useful insight, and one that's just a bit of spreadsheet decoration.
Which Excel tools are often overlooked?
MM: Worksheet protection is a useful feature that lets you prevent other people from making changes to your spreadsheet, or allows them to make edits in specific cells only. It's tremendously useful when you need to share worksheets with other people (for example, if you're sending a customer a quote, contract, or registration form in an Excel spreadsheet, and you don't want them tampering with your figures). All you need to do is supply a password of your choosing. It's simple, but people often forget to use it.
Another detail that people miss is the difference between relative and absolute references in formulas. You don't need to understand the difference to write a formula, but if you want to copy a formula to several places and make sure each copied version is adjusted correctly, you should. Otherwise, you'll add extra work for yourself correcting the formula in each copied cell.
What's new in Excel 2010?
MM: Excel 2010 is more evolution that revolution. It doesn't add anything as dramatically different as Excel 2007, which introduced the tabbed super-toolbar called the ribbon.
That said, the most talked-about change to Excel 2010 is the new Excel Web App, which lets anyone with a browser view and edit Excel spreadsheets online. The Web App is free and it runs on a variety of web browsers (even Safari on a Mac), without any need to have the desktop version of Excel installed. The Excel Web App doesn't have all the rich features of the desktop version of Excel. You can't create charts, for example. But it does add one new feature that the desktop version doesn't have: collaborative editing. Multiple people can edit the same worksheet at the same time.
Beyond that, Excel 2010 adds a new "backstage view," gives users an all-in-one place to manage files. You go there to save, open, print, and upload Excel documents.
Excel 2010 also adds a few frills, like the ability to completely customize the ribbon, and sparklines, which are miniature graphs that you can pop into a single cell for an at-a-glance look at an important trend.
Finally, there's my favorite new feature: better AutoRecover. Excel now automatically backs up old versions of your current spreadsheet and unsaved documents, so you can recover lost work or back out of a catastrophic change.
What's the most unusual use for Excel you've seen?
MM: There's a well-known implementation of Pac Man and Space Invaders that uses the Excel worksheets grid for a playing board and drives all the gameplay through Excel macros (although I think something changed in Excel 2010 that prevents it from working properly). So if there's a limit to what you can do in Excel, it's far away from the limit of what you should do.
Related:
- How to use conditional formatting in Excel
- How to Summarize Data Using Sparklines in Excel 2010
- Full collection of Excel questions and answers
Fast-paced and easy to use, this guide shows you how to get the most out of Excel 2010. You'll learn how to develop a spreadsheet from scratch, create formulas, add data, and analyze and graph data so you can make informed business decisions. With clear jargon-free explanations, step-by-step instructions, tons of illustrations, and lots of undocumented tips and shortcuts, you'll get hands-on guided tours and explanations of Excel's new features.
