Teach me Excel in an hour

[Seb1780]

As the local Excel Guru (in the land of the blind .....), I've been asked the run a training course for some of our staff.

 

The brief is "Train us in Excel" and I have one, one hour session in which to do this! I've asked what, specifically, they want training to cover and have received responses such as "tell me what it does"; "start at the beginning" and "does this count as CPD?".

 

My problem is knowing where to start, should I take them at their word and start with cell references, moving around and simple arithmetic or should I jump in at the deep end with examples of the spreadsheets we use in school, explain how to use them and ignore what is happening behind the scenes?

 

Any advice welcome.

 

Thanks

[featured_spectre]

Take them through what they should expect to see:

 

Go over the tabs and what is in them (basic overview)

 

Then go over how to do basic tables and using basic formulae. After this, if they wish to go further, that is down to them. We covered the basics here as it is rarely used and gave out a small sheet with "top tips" on Excel (mind you, we did this when we upgraded everyone to Office 2010)

[tmcd35]

I'd say pick off some commonly used functions they're likely to find useful - SUM(), VLOOKUP(), CONCATENATE(), COUNT(), IF(). 99% of all spreadsheets I've ever created never use anything other than one or more of these, or at worst one of the subforms of COUNT() (COUNTA, COUNTIF, etc).

 

Finish of with a bit of Word mail merging and you've got the basics functions most users need on a day to day basis.

[VeryPC]

▲ Very useful stuff @tmcd35 I agree, but I reckon the likes of IFs and VLOOKUPs will be way too advanced for the OP's learners. If they are asking to be told "what it does" then I think simple data tables with a few basic arithmetical functions like =A1*B1 =SUM(A1:A10) plus autosum etc, dragging values and formulae down columns and across rows, the way that cell references are relative and the use of $ to make them absolute, freeze panes, basic and simple conditional formatting maybe and basic charts for visualising data. Oh, and run them through page layout and page break preview so they don't get frustrated with not being able to get their work onto paper.

 

That's what I would do, but I'm not sure if 1 hour is enough to cover all this for beginners. I guess you could summarise in a mini "how to" that they take away.

 

Al

Edited June 20, 2014 by VeryPC

[elsiegee40]

I agree.

 

The bare basics for a one hour session are finding your way around the tabs - font, colour, wrap, merge, format (row height, column width, cells inc borders), Page Layout (inc Page Breaks) and how to do Print, Sort, Filter and SUM

 

That will stretch them to their limit. You won't have time for anything else... especially as they will need to practice some of this there and then

Edited June 20, 2014 by elsiegee40

[sonofsanta]

Of all the Office packages, Excel is the one least amenable to an hour long session. The concepts are so alien, and the features so in-depth, that you're only going to scratch the surface at best, I fear :/

 

Definitely keep it simple. Show them how to resize rows and columns (especially double-click to autosize). Show them the merge & centre button and the Border buttons (don't go as far as right-click to do borders, keep it to the ribbon options I'd say).

 

Show them some basic formulae - A1 + B1, A2 * B2. Absolute references are really useful, but I wonder if they might confuse the issue too much.

 

Show them some basic functions - SUM, COUNT, AVERAGE, MEDIAN are handy (the latter two get used here for grade analysis that all teachers have to do, and they're no more difficult to use than the first two).

 

Show them how to Format as Table, then sort and filter on the columns.

 

Aaaaand that'd be your hour, I bet. You're going to need a lot of "you can do a lot more with this/like this, ask me afterwards if you want to know" - making them aware that options exist without overloading them with information. Spreadsheets are weird if you've never really used them.

[tmcd35]

I think you should go in with a practical spreadsheet that needs creating and have them follow along, create it with you. You can cover off the basic formatting at the same time as introducing useful formula and functions on the way to the finished article.

 

Create a list of students. Add in a column for gender. Add filters. Count number of boys/girls. Sort by surname alphabetically. Add column for grades. Add column for targets. Add column to compare grades to targets. Count students who are below target. etc.

 

I'm sure there are other examples depending on what functions you want to show off. Personal finance spreadsheets can be some of the best to cover formatting, multiple inter-linked calculations, possibly over a couple of sheets. And yet be surprisingly simple to demonstrate and for the audience to grasp because the subject matter is relevant to them.

Edited June 20, 2014 by tmcd35

[Seb1780]

Thanks all, some good ideas here.

[DPrince]

The first thing to establish is are they after training in Excel, or in Spreadsheets.

 

If Excel, then I would go over the topics mentioned earlier (the toolbar, etc). Stuff specific to Excel.

If it's spreadsheets generally, then I would explain the concept of "What-if" software, how cells are relational, how you can use the spreadsheet in different ways to achieve different ends. Then expand from there. As has already been pointed out, 1 hour isn't going to give you much to work with.

[vikpaw]

A worked example that they can recreate with you is a really good method. If they're not doing it they're not learning it.

 

We used to use the DiDA course skills gym for CPD with staff. They could work independently and at their own speed. Looking at a course like that will give you an idea of what to do and in what order.

 

Microsoft also have some videos you can check out to get an idea of what they recommend. Starting with the basics.

 

It's easy to overlook things like saving, opening, file extensions, icon etc. which we take for granted.

 

We always used to have someone that would try to open a spreadsheet from Word and not find it because that was their only experience of accessing a file.

[Leeoakley]

This is a tough thing to answer without knowing what they already know.

 

I have a training needs form for Excel, happy to email it to you if it will help.

 

They basically answer the questions and it should help identify where you need to start.

[Seb1780]

I have a training needs form for Excel, happy to email it to you if it will help.

 

PM Sent

[pcstru]

My problem is knowing where to start, should I take them at their word and start with cell references, moving around and simple arithmetic or should I jump in at the deep end with examples of the spreadsheets we use in school, explain how to use them and ignore what is happening behind the scenes?s

When I did an intermediate excel course, I spent 15 minutes with each of the participants to see where they were, the kind of tasks they were doing and from that I tried to figure out what they might get most benefit from. If you can do that even with one or two people it might help.

 

I found most people didn't know the difference between relative and absolute cell referencing and that caused them a lot of work - because they were building sheets in ways that made copy/paste or drag copy, difficult. Named ranges were also a big boon to people in shunting around data and then using them for validation. Sort and filtering is generally useful to heads of faculties dealing with mark sheets and attainment data. Setting print ranges, things like fit to page etc, all useful if you don't know it.

[Leeoakley]

Should be with you now. Good luck!

[vikpaw]

For those that already use it but want more I have found custom sort lists super useful particularly when your year group structure goes into letters e.g. YN, YL6, YU6 etc and you want to keep data in school year order.

 

Also using the subtotal function so they can quickly do boy/girl and form counts without even needing formulae.