# Top 10 most useful functions for beginners?



## Oorang (Mar 1, 2006)

In about a week I have to give a 30 minute talk on Excel and I was requested to come up with the 10 ten things I thought I user should know how to do. And then they would pick the their favorite four. I was thinking maybe in this order:
IF
COUNTA/COUNTBLANK/COUNTIF
SUMIF
How to use VLOOKUP alone and with INDIRECT
How to evaluate a formula with the auditing tools. 
SUMPRODUCT
How to use MATCH/INDEX in situations that VLOOKUP won't work in. 
How to compare lists and find duplicates.
How to use a UDF to sort by color.
How to use RIGHT/LEFT/MID/TRIM/FIND/SUBSTITUTE

I was hoping to get some input, I've never had to do this before


----------



## Smitty (Mar 1, 2006)

How literate is your audience?

For mine things that we tend to overlook everyday (SUM, NOW, IF, etc.) are pretty advanced (I've just recently seen an =A1+B1+C1+D1+ad infinitum formula from a "higher-up's" secretary who "knew what she was doing", that from her and her boss...Uh huh...)

Most (of mine) are more well served being taught the "extreme" basics, like how to open, save, save as, format, sort, filter, etc.  Although some business math functions have also been important, like how to calculate a percentage.  Since we're a weekly business, some simple concepts have helped too, like =A1+7; yes, I have managers ask me if Excel can do such a thing and they're amazed!

I wish I had an audience to talk to about your subject list (Oh yeah, that's why I'm HERE... )

Just my two cents...

Smitty


----------



## Oorang (Mar 1, 2006)

Well the manager is at pivot table/vlookup level. So she's doing decent. But the staff is at the sum level. So maybe I ought to scale it back to IFs COUNTIFs and SUMIFs. You can make a pretty good analytical sheet with those.


----------



## Smitty (Mar 2, 2006)

> Well the manager is at pivot table/vlooup level. So she's doing decent. But the staff is at the sum level. So maybe I ought to scale it back to IFs COUNTIFs and SUMIFs. You can make a pretty good analytical sheet with those.



Your manager is at a place that I wish more of us were in...As for the rest that'd be great, and better for you and your company (and customers) if they can adapt.

I've found that if you can introduce some of the concepts, like efficient design and planning what they're trying to convey, it goes a long way (like "Did you know that daily sales report I see you working on with a calculator in the breakroon can be automated?") and people all of a sudden get really interested.  

I think it's less of what you know and more of finding out what you're audience does (and doesn't) and catering your training to that assessment.

But do a board search for "lesson plans" or something along those lines; Mark O'Brian and countingapples were in similar positions a while back.

Good luck!

Smitty


----------



## Felix Atagong (Mar 2, 2006)

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!


----------



## Cbrine (Mar 2, 2006)

Oorang,
  I've run a few of these with my company, and I've found that one of the biggest hits are keyboard shortcuts.  End-cursor down to get to the end of a column of data, copy & paste, holding the shift key to highlight. etc.. etc..  They were more amazed by how fast I was able to move around the worksheet, then learning how to do a vlookup

Cal


----------



## Greg Truby (Mar 2, 2006)

You're off to a decent start there.  
Tier #1 formulae would have to be what I'd call the very basics, SUM, COUNT, COUNTA, AVERAGE. 

Assuming competence in the very basic then the lookups (vlookup, lookup, match, index(match()) are most useful.

After that I'd go with Autofilter.  Very useful and very easy to use.

The *basics* of named ranges (I wouldn't get too fancy, i.e. no dynamic NR for greenhorns.)

As Cal has already mentioned, show them the basic keyboard shortcuts, selecting using the shift and end keys.  

If, SumIf & CountIf would be Tier 3 on formulas.

I wouldn't touch SumProduct.  I'd go over pivots twice before I'd mention sumproduct.  More intuitive, easier to change, easier to filter, need I go on...  Around here (the MrExcel board) we are all so good with sumproduct that's what we often suggest; but I try to mention pivot tables to board newbies because I think they're one of the most overlooked tools in Excel.

And as you already mentioned, the formula auditing toolbar and its tools (another oft-overlooked bit of Excel).
HTH


----------



## klb (Mar 2, 2006)

If you are looking for useful things to know: 

How to use Excel's built-in "help" 

How to use "insert function" (A lot of people don't know what that "funny little fx" is right above the spreadsheet. )

That if you hold you cursor over a button you get a "tool tip"

That formatting and rounding are not the same thing.  As an example, 1.4+1.4 = 2.8 and 1.7+1.7 = 3.4 but if formatting is wrong 1+1 = 3 and 2+2 = 3   

How to get to www.mrexcel.com

The basics of range names

You can't divide by 0

How to insert or remove a hyperlink

If you password protect anything, you better remember the password or you are out of luck!

For a newbie, these can be helpful things to know.


----------



## RichardS (Mar 2, 2006)

I'm with Greg on the Pivot Tables. If you're analysing a list, it's got to be one of the most useful tools there is. I am constantly showing people around here how to create and manipulate them. Of course, lots of time I have to re-build their source data, as the layout is not conducive to analysis. So I guess the most important thing in using Excel is understanding what you're using it for, what you hope to get out of it, and designing your worksheet to achieve the desired reults.

Richard


----------



## Aladin Akyurek (Mar 2, 2006)

> You're off to a decent start there.
> Tier #1 formulae would have to be what I'd call the very basics, SUM, COUNT, COUNTA, AVERAGE.
> 
> Assuming competence in the very basic then the lookups (vlookup, lookup, match, index(match()) are most useful.
> ...



Pretty much what I teach in my freshman classes.



> I wouldn't touch SumProduct.  I'd go over pivots twice before I'd mention sumproduct.  More intuitive, easier to change, easier to filter, need I go on...  Around here (the MrExcel board) we are all so good with sumproduct that's what we often suggest; but I try to mention pivot tables to board newbies because I think they're one of the most overlooked tools in Excel.
> 
> And as you already mentioned, the formula auditing toolbar and its tools (another oft-overlooked bit of Excel)...



I take these up along with advanced uses of lookup functions, data validation, some auditing, spreadsheet organization, etc. in my second year classes. I still favor teaching them F9 instead of the formula auditing toolbar.

BTW, I never teach the studs the Fx wizard...


----------



## litrelord (Mar 3, 2006)

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 (Mar 3, 2006)

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 (Mar 3, 2006)

> 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 (Mar 9, 2006)

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 (Mar 9, 2006)

Data Validation... Good one


----------



## Michael M (Mar 9, 2006)

*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 (Mar 9, 2006)

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.


----------

