What is (or should be) considered "advanced" knowledge in Excel?

[SydneyGeek]

I'm afraid all of my materials are from when I was a contract trainer with Fred Pryor seminars and they developed pretty much all of the materials I used.

@ Super Ferret - one thing I would never do when doing training is say "you can't do that in Excel". I know a lot of Excel tricks and tips and yet I don't know it all. There's all sorts of little nuggets all around that I'm not aware of. People pull rabbits out of hats around here all the time.

Having said that -- this thread has already alluded to the fact that even though essentially 100% of most office personnel have Excel install on their computers, > 95% underutilize it. I'm sure Smitty's & Denis' experiences are similar to mine as far a public seminars goes -- 1/3 of attendees are on an all-day coffee break; 1/3 are neutral and about 1/3 are actually engaged and trying to learn something. Don't start teaching seminars thinking that 100% of the class is going to think you're an answer to their prayers. Some will actually hate you because you love Excel. I'm not saying don't do it -- the 1/3 that are there to learn make it worth it; and sometimes you can spark interest in the neutrals.

Teaching seminars is a very interesting business and an amazing learning opportunity. But it's not for the feint of heart either.

@rory, I'm in a similar position to Greg. The materials that I used were provided by the firms that I contracted for.
The hints and tips that I run through are pretty generic, but usually the sort of things that drive people slightly nuts. Examples:

(don't laugh -- heaps of people don't know this stuff)
Double-click the fill handle to fill formulas down
Alt+= to total multiple columns / rows
Navigation / selection using the keyboard
Using GoTo | Special for...
Filling in blanks with zeros
Deleting rows with blanks
Highlighting cells with formulas / constants
Using the various Paste Special options
...and whatever questions arise from these demos

What else for 2010?
Ribbon
Backstage
Sparklines
Pivot Tables
Slicers
SUMIFS / COUNTIFS
IFERROR

Particularly if you are running something internal, use "real" work examples.

@Greg,

Yep -- sometimes you really have to work to get any sort of reaction. But when you see people switch on it's a buzz. And I love it when somebody shows me something I didn't know before. Why shouldn't the trainer also be learning stuff?

Denis

 

[SuperFerret]

It as got to be bad when someone else on the course shouts that you should be doing the training instead

So get some training material from me, Denis or Truby and start doing it!

Any training materials I really would appreciate!! I've got an ever increasing army of people now all protesting to the higher up's that I should be providing the training haha.

Only yesterday I showed someone CTRL+Return and they've been on the in-house course.

 

[Jaafar Tribak]

From my experience, most people in the workplace never get past the beginner level - Yet, they somehow often manage to get the best jobs in the office

I truely believe that excel is under utilized in the workplace.What is more, lots of office personnel are resistant to improving their existing spreadsheets because they have the perception it will make their spreadsheets too technical and therefore more difficult to handle specially if something goes wrong with them when you are not around.

Also, there is the fact that each departmenet has its own main piece of software to get the job done which further contributes to the loss of interest in enhacing excel skills.

Apparently, advanced/expert excel users live in the virtual world,not the real world

 

[SuperFerret]

From my experience, most people in the workplace never get past the beginner level - Yet, they somehow often manage to get the best jobs in the office

I hear that! I've lost count of the number of Managers and Directors which come to me, the lowly Admin Assistant to provide them with a solution to their Excel woes.

Apparently, advanced/expert excel users live in the virtual world,not the real world

It appears so!

 

[Sandeep Warrier]

From my experience, most people in the workplace never get past the beginner level - Yet, they somehow often manage to get the best jobs in the office

That is soooo true!!!

 

[Greg Truby]

@ Rory (or anyone else training) - a good technique is to demonstrate the painful method before giving them the tip. This one was probably my most popular...

{show table with six or eight columns}
"OK - we need to move this column [E] over here [between A & B]. How many of you would have done this?
{Insert column between A & B [NOT using Ctrl+Space then Ctrl++]}
{Copy & Paste or Drag E to new column}
{Delete the now-blank Column E [again NOT using Ctrl+Space then Ctrl+-]}

Now check this out!
{Go to a second copy of worksheet}
{Demo the drag while holding Shift}
{listen to oooh's and aaaah's - wait until they say "do that again" or if about six or eight seconds go by ask "would you like to see it again?"}
{Do it again}

 

[arkusM]

@ Rory (or anyone else training) - a good technique is to demonstrate the painful method before giving them the tip. This one was probably my most popular...

{show table with five or eight columns}
"OK - we need to move this column [E] over here [between A & B]. How many of you would have done this?
{Insert column between A & B [NOT using Ctrl+Space then Ctrl++]}
{Copy & Paste or Drag E to new column}
{Delete the now-blank Column E [again NOT using Ctrl+Space then Ctrl+-]}

Now check this out!
{Go to a second copy of worksheet}
{Demo the drag while holding shift}
{listen to oooh's and aaaah's - wait until they say "do that again" or if about six or eight seconds go by ask "would you like to see it again"}
{Do it again}

huh, just learned something new. Shiny.

 

[RoryA]

Last time I did it, I think the best received bits were the bits I threw in as I was going along - double-click filling and right-click filling, and formatting subtotal rows (after the Data Subtotals method) in one go.

 

[Darren Bartrup]

Now check this out!
{Go to a second copy of worksheet}
{Demo the drag while holding Shift}
{listen to oooh's and aaaah's - wait until they say "do that again" or if about six or eight seconds go by ask "would you like to see it again?"}
{Do it again}

Oooohhhhh.

That's 2 things I've learnt from this thread now - didn't know the Ctrl+: either.

 

[Darren Bartrup]

Best response I've had was explaining SUMPRODUCT. Took a few months for people to get it, but got there in the end.
Best recent response I've had was explaining that if you hold shift down while dragging data around......

(sorry, would've put that as an edit to last post - but my 10 mins was up).