Top 10 most useful functions for beginners?

Oorang

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
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 :o
 
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
 
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.
 
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.
Click to expand...

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
 
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!
 
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
 
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
 
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 :huh:

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.
 
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
 
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.
Click to expand...

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)...
Click to expand...

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...
 
You must log in or register to reply here.

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top