Training Session Ideas/Suggestions? 6

[VBAjedi]

I have been given the (rare) opportunity to take 2-3 hours to train a group of my users (about 20 of them) on Excel. Their current XL abilities vary widely, and I need to structure my approach to have the greatest impact on the largest number of people.

Anyone done anything like this recently? I certainly have some ideas about topics I would like to cover (like Paste Special, the AutoFilter, and Sorting) , but I'd like to hear from others experience!

Thanks,


VBAjedi

 

[Zathras]

1. VLOOKUP function

2. Pivot Tables

3. Double-clicking the cell handle (square at bottom right-hand corner) to copy formula down.

 

[BlueHorizon]

Hi VBAjedi,
As an Instructor myself, I'd offer the following suggestions in addition to Zathras:
Subtotals
Sorting left to right
Conditional Formatting
Data Validation
Adding Comments
Linking formulas
Page Break Preview
Freeze Panes

Good luck!
BH

 

[bluedragon2]

In addition:

Some of the help functions like clicking on a cell and hitting the function button to bring up the function wizard.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[MisterC]

Just a few suggestions...

-I'd spend at least 10 minutes on basic stuff, just so you don't completely lose everyone in the dust. (Kind of a general intro to Excel)
-You'll have to get a feel for the group's proficiency as a whole to decide how much time to spend on each topic or specific function.
-Try to make sure you cover topics that are relevent to their jobs... Use existing spreadsheets as examples and show how they work or how they can be improved...
-Give them a quick assignment and observe their solutions.

Some general topics you could cover:

Basic
-----------------------------
What is a spreadsheet?
What do I use it for?
The Menu and Toolbars.
Entering & Formatting Data.
Simple functions (SUM, Average, etc...)
General design guidelines.

Intermediate
-----------------------------
More advanced functions (IF, VLOOKUP, etc...)
GoalSeek
Using the Macro Recorder

Advanced
-----------------------------
Data functions
Pivot tables
VBA

Definitely not an exhaustive list, but you get the idea. Good luck!

 

[SkipVought]

Proper TABLE construction.

Named Ranges - Insert/Name/Create method

OFFSET Function to do ALL SORTS of things including dynamic named ranges

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

I should have continued on Named Ranges --

the use of which in FORMULA EXPRESSIONS to help make the formulas more nearly SELF DOCUMENTING

ALSO

STRONGLY advise against segmenting data into separate sheets/workbooks -- ie daily/weekly/monthly whatever per sheet/workbook


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

Excellent input, all! In light of the fact that each of these concepts will probably be worth a star to at least one of my students - Stars around! Also, that will hopefully help flag this post as good reading material for anyone wanting a list of topics to study for their own development.

More suggestions welcome, but that's already a good starting list.

VBAjedi

 

[SkipVought]

Hey, thanx for the glowing heavenly body! ;-)

In my opinion, you have 2 broad areas that you could possibly cover...

A. workbook/spreadsheet functionality

B. workbook/spreadsheet design & use

So you start out learning how Functions and PivotTable Wizards and Subtotal Wizards and Charts work.

THEN the next step is putting it all together in a cohesive manner so as NOT to paint youself into a corner

'cuz....

Spreadsheets are SO EASY TO USE, that they are SO EASY TO MISUSE!

And before you know it, and I have seen it BOTH in little mom 'n' pop shops and in huge corporations, the spreadsheet is hopelessly broke! (he he! 'cept for someone with some smarts, hmmmmmmmm?)

'cuz....

Spreadsheets are SO EASY TO USE!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Stroppy]

Just like to add a couple of basics, (without a star if that's ok)

- Protection
- Controlling Page breaks
- Heading repeats
- Auditing toolbar

M

 

[KenWright]

One of the most useful functions IMO is SUMPRODUCT. That combined with SUMIF and COUNTIF are often invaluable for anyone with tables of data that needs interrogating.

One very useful tip is how to convert what should be numerical data that has been copied in from somewhere else, but has come in as text, and as such will give a 0 when summed etc. That tip is the 'copy an empty cell, select the data conncerned and do edit / paste Special / add' which coerces it back to numeric.

Another thing you might consider is to advise them of resources such as this forum, where anyone with Web access can go and get help.

I completely agree with the comments re Pivot tables - as these are one of the most powerful, yet simple to use features Excel actually has.

There are also a number of good websites that you may want to let them have, which once their appetite has been whetted will allow them to go and expand on what they have seen:-


Chip Pearson's Site, one of the best all round references on the web:-

http://cpearson.com/excel/topic.htm

John Walkenbach's site - Probably the most prolific Excel author today, with a whole string of superb Excel books to his name:-

http://j-walk.com/ss/excel/usertips/index.htm

Debra Dalgleish's site - A great reference for a number of things, but especially for introducing filters and pivot tables:-

http://www.contextures.com/tiptech.html

Dave McRitchie's site - A prominent Microsoft MVP with a whole host of goodies on his site - my favourite being his 'Trimall' macro, which will clean up any data brought in from the web into Excel:-

http://www.mvps.org/dmcritchie/excel/excel.htm

Ron De Bruins's site, another Microsoft MVP, with some great addins, especially his Google search addin that allows you to search newsgroups directly from Excel, and his sendmail addin.

http://www.rondebruin.nl/

The Excel MVP page - a collection of free utilities from a number of Microsoft MVPs:-

http://www.bmsltd.co.uk/MVP/Default.htm

Tushar Mehta's site (another MVP) - a huge compendium of all things Excel - Some great Technical and mathematical stuff in there:-

http://www.tushar-mehta.com/

A superb freely downloadable set of examples from Peter Noneley:-

http://homepage.ntlworld.com/noneley/

Another excellent Tek-Tips type forum

http://www.mrexcel.com/

An oft asked question is how do I convert a file from Works to Excel - Worry no more:-

http://www.rl-software.com/indexjs.htm?/converter/wkscnvxls_e.htm

The Excel Newsgroups themselves, from within a Google interface:-
Standard Interface

http://groups.google.com/

Advanced Interface

http://groups.google.com/advanced_group_search?hl=en

(All this is available through Ron De Bruins addin, and is probably easier to use)

The Microsoft KnowledgeBase

http://support.microsoft.com/default.aspx?scid=fh;EN-US;kbhowto&sd=GN&ln=EN-US&FR=0

Direct access via the web to Microsoft's newsgroups:-

http://support.microsoft.com/newsgroups/default.aspx

A free Excel viewer that allows people without Excel to view Excel files (useful if sending docs to other people):-

http://www.microsoft.com/downloads/...49-91DA-4110-8595-4A960D3E1C7C&displaylang=EN

Either a page on an intranet with examples of some of the suggestions you have had, or perhaps a sample workbook with a lot of the examples listed, plus some good weblinks might be a nice giveaway for the class.

Hope this helps:-

Regards
Ken.....................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[JeniS]

Great thread! Explaining absolute references is often missed when teaching basic formula writing....

 

[nberryman]

Grab their attention.

Start with the basics and try to cover the time saving features like using the Autofill handle with the Right mouse button. I often show this by typing in Monday and dragging with the right button and the shortcut menu then offers choices for Weekdays or Working days.

Using Autofill for non sequential dates by typing in 2 dates a week apart and selecting them and then using Autofill which fills dates with the same frequency. Custom lists also go down well.

Simple formulaes showing the the cell range can be selected with the mouse and not just typed in.


Overall, keep the examples simple so they concentrate on learning the technique and not what you are trying to do.

Good luck



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[Tiglet]

Using F4 to scroll through the referencing options on a preselected range. Something that is easily demonstrated as to what the impact is of putting $ signs in before row/column reference or both.

Spreadsheet protection

Vlookups, IF formulae, The formula wizard and the fx button which explains what each formula can do.

Finally, it sounds obvious but it amazes me how many people don't think to look at it when they have a problem(including myself ) - the inbuilt Excel Help - explains lots such as custom conditional formatting, formulae etc etc.

Tiglet

[blue]Living on Earth is expensive, but it does include a free trip around the sun every year.[/blue]

 

[Dsuperc]

try this site

http://www.theofficeexperts.com/excel.htm