Excel Tips

Figuring actual warm bodies...we teach more Excel than any other single subject. Susan Harkins of Tech Republic had a great list of top 10 things Excel users should know.

1: Absolute and relative referencing

Without a good understanding of absolute and relative referencing, a user doesn’t stand a chance of setting up a reliable spreadsheet. Throw in 3-D and mixed referencing and the novice user will become thoroughly confused. Don’t assume your users know how to apply absolute and relative referencing correctly.

2: Limitations

All applications come with some limitations. The problem is these limitations can generate ambiguous errors that users won’t know how to troubleshoot. Even casual users should be aware of the software’s limitations for non-Ribbon and Ribbon versions to avoid hard-to-troubleshoot errors.

3: Quick help on functions

Excel has more functions than anyone can hope to commit to memory. Fortunately, that isn’t necessary. Built-in features help users pick the right function and use it correctly. First, during the data entry process, Excel displays a list of functions. Users can choose from this list or just use the list to help trigger their memory. In the Formula bar, users can click Fx to launch the Insert Function dialog, which helps them choose and enter the function correctly. Users also have quick online access to information on Excel functions.

4: Right-clicking

Right-clicking an object — cell, range, control, form, and so on — is the quickest way to gather information about something. A right-click will usually display a contextual menu that will apply to the clicked object. It’s the fastest way to get information and perform simple tasks. Even if nothing happens, it doesn’t hurt to try.

5: User preferences

Everyone works differently. And although Excel does a decent job of anticipating the needs of the average user, some users will prefer things to work their way. Show users how to explore these options. In Excel 2003, you’ll find them in the Options group off the Tools menu. In Office 2007, click the Office button and choose Excel Options. In Excel 2010, click the File tab and select Options under Help. There are many options, and users don’t have to know them all. They just need to know that they have choices.

6: The fill handle

Excel’s fill handle is an amazing tool that saves time when entering formulas or creating a series of values. You’ll have to show it to them, because it’s not exactly intuitive. They might or might not find it by themselves. Even if they do find it, unless they understand #1, their results will be inconsistent.

7: Formula auditing

A formula that doesn’t return the expected results is frustrating. Sometimes there’s a problem with the evaluated data, but it isn’t obvious. Sometimes, the formula’s just wrong. Either way, auditing the formula will usually pinpoint the problem. There are two ways to audit a formula and you’ll want users to know about both:

• Highlight references in the Formula bar and press [F9] to evaluate just the highlighted component.
• Use The Evaluate Formula tool in the Formula Auditing group on the Formulas tab.

Both methods will help users work through problems on their own, and it will help them learn about functions, formulas, and referencing.
Users should also know how to quickly find cells that formulas reference. First, select the formula cell and then press [Ctrl]+[. Excel will highlight all the referenced cells and move to the first reference in the formula. Use [Enter] to move through the highlighted cells. To highlight formulas that reference the current cell, press [Ctrl]+].

8: Stored values vs. displayed values

Formats determine how Excel displays values, and the displayed value doesn’t always reflect the stored value. Consequently, formulas often return unexpected results, which might confuse users — Excel’s not calculating right! What’s wrong? — when Excel is performing exactly as it should. It’s a simple guideline that users need to understand: Excel evaluates the stored value, not the displayed value. If they review the stored values in the Formula bar, the results will make sense.

9: Status bar info

Excel’s Status bar is full of practical information, and with a quick right-click, users can customize the display. Several indicators alert users to current use, such as selection and overtype mode, whether a toggle such as Scroll Lock is on, page number, and so on. Perhaps its neatest trick is the display of the Sum, Average, and Count functions, which automatically evaluate the selected values. It’s handy for those unexpected questions on the fly. Even a novice user can use this trick confidentially.

10: Paste Special options

This feature takes care of a number of problems, from pasting text without the original formats to returning values in place of the evaluating formulas to letting you work with filtered sets. Users don’t have to know all of its tricks, but they’ll certainly work more efficiently if they do.