Top 10 most useful functions for beginners?

[litrelord]

If I could teach certain people at my company two things it would be that you don’t need to open that fx wizard just to write a formula and you can use ctrl + shift to move around the worksheet and/or select. The woman sitting next to me, when copying a formula down the side of a sheet insists on moving one cell the left, repeatedly pressing end down end down end down until at the bottom, then going right one and down one and typing in end. Back to where she started, copy, hold shift and press end down, press up once and paste. There’s so much I don’t know that I shouldn’t criticise but it’s pretty frustrating to watch.

So yeah, keyboard shortcuts are a big help for people I think.

It really depends what you do at work though. One of the biggest time saving things I’ve taught people is using the column function as the column index number in a lookup when filling in a table. The way most people were doing it was to use 12 vlookups (one for each month). I guess you could translate that as teaching people they can use nested functions.

We also have a new member of staff in our department who asked me if I could teach her “all those whizzy things in excel”. I pointed her to this website via a trip to waterstones. She’s done neither so far

Nick

 

[Oorang]

I talked to 3 of the staff to get an idea of wha tpeople wanted to learn, and how they were using sheets, and the range is pretty broad. But I did add pivot tables to the list. I was a little hesitant to get into pivot tables, because I only have a half an hour. They do have one reporting analyst, so I definatly think he could benefit from if, countif, and sumif. I decided to steer clear of anything that would require using two functions in the same formula. (Like match/index, mid/find, or len/rept) I also think I am going to show them how to coerce numbers to text and text to numbers, as that was causing several of the staff problems. So maybe:
1.) Common hotkeys
2.) Number/Text Coercion
3.) IF/COUNTIF/SUMIF Functions
4.) Pivot Tables
5.) VLOOKUP

 

[nullZero]

Although I find it quite stupid, some 'managers' are really impressed when you show some saucy charts filled with images (dollar bills or the product the charts represent, etc...). Of course if you show them how to make these charts you're out of a job, that why I keep those things a very big secret!

I made a pivot chart for my director once and he spent the rest of the day in his office playing with it and showing it off to everyone who walked by. I got so much work done that day...

Another thing that people love and will take seconds to demonstrate is how to add a paste special values button onto your toolbar.

nZ

 

[Joe Was]

Validation, in-cell-drop-downs: Excel Toolbar: Data - Validation.

Validation messages as instant help: Excel Toolbar: Data - Validation.

The built-in database utility: Excel Toolbar: Data - Form...

Right-click menu, most people do not use this enough, they waste time moving up to the dropdown menu or toolbar.

And Excel ToolBar: Format - Row/Column - AutoFit.

Edit help with: ExcelToolBar: Edit - Replace...

And as pointed out: Excel Toolbar: Data - Filter - AutoFilter [One of my favorites along with in-cell drop-downs.]

 

[Oorang]

Data Validation... Good one

 

[Michael M]

Beginners

Hi Oorang
I think the keyword in your comment is beginners.
Most of the users that frequent this MB have advanced to a point that they think SUMPRODUCT, Array formulae, etc are the "simple stuff".
I remember agonising for hours over a VLOOKUP until the penny dropped.

I guess what I'm trying to say is:
SIMPLE formulae such as SUM, multiplication, etc.
Keyboard shortcuts
Using Alt F1 to create the instant chart.
Formatting of cells
Save, Save As
Cut,Paste
Headers & Footers
Inserting pictures
Comments.

This stuff mekes you feel real stupid when you are presenting, 'cause it's too simple. But, believe me, I taught Adult beginners in night school and after the type of stuff listed above, they thought I was god.
Make another list for a Plan B, just in case thay are a bit smarter than you expected.

Best of Luck
Michael M

 

[Oorang]

That's a really good idea too, set up two presentations, and one geared toward beginners and one geared toward intermediate. That way if they look bored to tears on the beginner stuff, we can move on to more advanced things.