Software

The 10 most important things to teach your Excel users

You can help new Excel users become more productive and self-reliant if you explain these core concepts, efficiency tricks, and essential features.

Excel users come in all levels, from novice to power user to developer. But there are several Excel behaviors and features everyone needs to learn. Some of these seem basic. But to the novice, they can seem like rocket science -- at least until you explain them. With these skills conquered, even beginners can use Excel competently.

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.

Bonus tips: Data Validation and named ranges

A worksheet is only as good as its data. If users enter the wrong data, nothing else matters. Excel's Data Validation feature goes a long way toward protecting the validity of data by rejecting invalid entries. Your users should be able to implement this feature. At the very least, they should be able to recognize it at work and know not to circumvent it in distributed workbooks.

And one final tip: Learn to use named ranges. Your users can work reasonably well in Excel without knowing about named ranges, but creating, understanding, and maintaining formulas will be much easier if they use them.

More help for your Excel users

Excel support headaches?

What Excel problems do you hear about most often? Do you get more calls about Excel issues than about other Office apps?

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

18 comments
Matusko
Matusko

could be argued to be more important than one of the 10 listed above. Most people who don't use the PivotTable miss out because they don't understand how to build them or do not know what value can be achieved.

mikewor
mikewor

Firstly, the average user does not understand the concept of storing the date as a number and then displaying it in different formats. Then there is the issue of US style dates (MM/DD/YYYY) vs the rest (DD/MM/YY) and the scientific herd with YYYY/MM/DD. And finally the massive problem that can be caused by using 2 digit years - does 10/09/11 represent October 9th 2011, 10th September 2011 or 11th September 2010?

MikesComputerHelpdesk
MikesComputerHelpdesk

Do you have some links to good teaching sources to cover this? I'd like to be able to offer them to clients that have difficulty grasping the concept. - Great article! Thanks.

Suresh Mukhi
Suresh Mukhi

I have actually seen users manually put SUM functions in the middle of lists for subtotals. They are overjoyed when I tell them about the Data, Subtotals command.

Shanta
Shanta

When I clicked on the link I was hoping to see "Absolute vs. Relative" Thank you for not only putting it on the list, but putting it at the top. Just taught a student the other day about it in our first lesson. Not enough trainers teach this to their students.

CharlieSpencer
CharlieSpencer

I have a spreadsheet for a fantasy sports league that I'm sure would benefit from a PivotTable, but I haven't figured out just what one does or how to use it. I also feel it's one of the most mis-named features ever. For the first several years of its existence, I thought it 'pivoted' the spreadsheet around the diagonal axis, swapping rows for columns and vice-versa. I've never understood what it 'pivots'.

kitdaddio
kitdaddio

While this article may give useful itemization for someone who knows excel to instruct a newbie, it is disappointing to read since it gives "teasers" (e.g., vague reference to excel limitations) but no examples/guidance on those topics.

open_source_user_01
open_source_user_01

The correct format is this: 2011/11/28 and/or 2011.11.28 why on this green earth do people continue to use this flawed method of: 11/18/11 and/or 11.18.11 people are suborn and regardless of how many times you ask nicely they will do this just because they are so smart YYYY/MM/DD is the common-sense and most accurate way of distinguishing what is what.

jody.burton
jody.burton

The people I have instructed have an easier time understanding it if I tell them to substitute "Always" for the "$" in their mind. $A$23 becomes Always column A and Always row 23.

ssharkins
ssharkins

The truth is, I don't. When I wrote this, I searched for one to link to and just didn't find anything that I thought was succinct and clear. Perhaps I just need to write one myself! :) If anyone wants to link to a a good tutorial on the subject, please do!

ssharkins
ssharkins

It's one of Excel's strong-suits -- all those wonderful toys! :)

ssharkins
ssharkins

Even casual users take this for granted -- they don't even think about using it. You can use it without even really understanding it, but that gets you into trouble. It's probably the most important thing for a newbie to learn.

Suresh Mukhi
Suresh Mukhi

That's exactly what a Pivot Table does, and lots more.

ssharkins
ssharkins

What you're asking for would require an article for each point. Is there something you'd like help with -- I'd be glad to help you.

mchuntley1062
mchuntley1062

It has nothing to do with how smart they are it has everything to do with the way that they were taught. The best practice is to know the audience for whom the output is meant. If you are the only one seeing it then format your date however you want; if it is meant for a manager born and raise in the US then you had better us the formatting that they are used to seeing. The same thing goes for people born and raised outside of the US.

Editor's Picks