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

[Norie]

Which is why you should make formulas and code as transparent as you can - you aren't the only one who might need to understand it.

 

[SuperFerret]

My workplace has what I feel is a very strange view on the Excel Knowledge scale.

There levels are:
Introductary - The Excel screen, Opening a blank workbook, Saving, Closing, Navigation, Entering data, Cut and Paste, Printing a document.

Intermediate - Charts, Data Validation, Sorting, working with multiple worksheets

Advanced - Using a template, IF function, Lookup, working with multiple workbooks, comments and protection

Given that 98% of our office works on Excel 90% of their day... you would think people would be at the 'Intermediate', possibly 'Advanced' levels... but no, everyone seems to be at Introductary. They know what Excel is and how to move around, but they have no idea about formulas, data validation, or conditional formatting.

They see this every day (in spreadsheets I've updated) and are amazed how things magically change colour or how it knows that what you've put in doesn't fit (barcodes that should be 13 digits when they try to put in 8).

The person providing the training doesn't help, I got sent on an Advanced course... and suggested a different method of acheiving the same result and was told No it wont do that (which it did) and when I mentioned putting images in comments he informed me that you can't do that in Excel so I did it...just to prove you can.
It as got to be bad when someone else on the course shouts that you should be doing the training instead

 

[Norie]

Did they actually do that?

Did you take over the class?

Might have lead to a new career avenue.

 

[arkusM]

I got sent on an Advanced course... and suggested a different method of acheiving the same result and was told No it wont do that (which it did) and when I mentioned putting images in comments he informed me that you can't do that in Excel so I did it...just to prove you can.
It as got to be bad when someone else on the course shouts that you should be doing the training instead

nice.

my experiance in an "advanced" excel course taught be CTRL + ":" put the date in a cell at a cost >$400 (CND) not a great value.
The "advanced courses" in this place often baffle me!

 

[SuperFerret]

Did they actually do that?

Did you take over the class?

Might have lead to a new career avenue.

Yes, he wasn't a happy bunny having someone there who knew more than him in there and wouldn't let me take over, or show anyone else any shortcuts (which they didn't teach) or variations on the formulas he was teaching. Miserable so-and-so!

I wouldn't mind teaching others, I'd probably do a better job than some places by the sounds!

my experiance in an "advanced" excel course taught be CTRL + ":" put the date in a cell at a cost >$400 (CND) not a great value.
The "advanced courses" in this place often baffle me!

The people on my team were amazed when I showed them CTRL+: maybe they think it's a big secret?

 

[SydneyGeek]

I've always treated the manual as a very rough guide. I start with about an hour of hints and tips in a blank workbook. Usually the feedback is that that section was the most useful part of the course -- most of the good stuff is not in those manuals.

Denis

 

[Smitty]

Which is why you should make formulas and code as transparent as you can - you aren't the only one who might need to understand it.

I'm in the process of decoding a few Access databases that a no-longer-employed developer purposely obfuscated (query sql hidden in hidden tables, user passwords hidden in random tables, no comments, etc.)

My former supervisor hated when I did array-entered INDEX/MATCH formulas to match on multiple conditions. They much prefered that I made a concatenate helper column for what I need to match, and then index/match off of those. Even after trying to explain it in depth and how my method is more accurate, I was still told to use the concatenate method because "If I don't understand it, I don't want you to use it."

Nothing wrong with helper columns as long as it's done for the right reasons.

As for the rest, I've got to agree that Cindy is pretty much dead on. With the exceptioin that if you want to learn more, don't let your employer hold you back by sticking to what's comfortable to them. Yesterday I paid a visit to the Fortune 500 I worked with for years, and am still the only one who writes VBA (and very few there, even in finance, even used recorded macros).

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!

 

[RoryA]

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

Do you have any for 2010? I think I'm about to get lumbered with training for our upgrade at work and I really don't have the time to put anything together (new baby to get ready for).

 

[Fazza]

From my perspective, an expert knows more than me. In the workplace, I am yet to meet such people - or even people anywhere close to my level. I do encounter them on line & in books - there are thousands & thousands of experts with amazing knowledge. It is enjoyable to learn from these experts & share my knowledge with others. regards

 

[Greg Truby]

Do you have any for 2010? I think I'm about to get lumbered with training for our upgrade at work and I really don't have the time to put anything together (new baby to get ready for).

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.