Excel topics you'd want to teach a Noob you were mentoring

[NateO]

#7: I am not a big fan of array formulas for extracting data. Introduce people to a very powerful and highly unused tool: MS Query.
{snip}

Hmm, strong language... Is that bait I smell?

No problem pulling in data without arrays, but MS Query is 'very powerful'? Well, I guess... You can unwittingly take out mission-critical DBs with it...

In my opinion, if you're going to show them this 'tool', you'd better explain the entire thing, which is kind of complex. Simply showing someone how to set up a Query might be setting them up for some serious headaches more than anything else...

I.e., you have to explain the difference between a background Query (which should almost always be set to false), QueryTable Objects, ODBC connections, where the SQL and Connection strings are located, how the developer might be pumping 1,000s of lines of code into their App while not realizing it... And think twice about the RefreshAll Method, dare I say do not use this Method?

If you don't know how to interact with QueryTable Objects with VBA, your chances of maintaining these things are drastically reduced. Questions pop up all the time with stuff... E.g., "What database is this thing pointing to?" And "I moved my DB, and I can't get back into the MS Query Editor...", on and on, etc...

I suspect one is better off learning how to use VBA/OLE DB/ADO to interact with DBs, there's nothing you can't do with this that you can do with MS Query, and the intent/code is all sitting right there in front of you.

Of course, this is all well beyond the scope of Excel 101. :wink:

 

[tusharm]

#7: I am not a big fan of array formulas for extracting data. Introduce people to a very powerful and highly unused tool: MS Query.
{snip}

Hmm, strong language... Is that bait I smell?
{snip}

Well, it wasn't meant as bait but there was a possibility you might consider it as such and I still left the language in place. So, if one wants to be picky, one could argue that leaving in language that I knew *might* be considered bait was itself bait, even if it was "second level effect" as it were. {grin}

Honestly, MS Query doesn't deserve the kind of abuse heaped on it. Introducing it to people to query XL workbooks is far from showing them how to take out mission-critical databases. Frankly, I would hope that real mission-critical databases (and systems) are better defended than in the example you raised. Maybe, they cannot be...I cannot be sure since I haven't had security/performance responsibility for a national/global database for about a decade and a half now.

There is no doubt that MSQ has several problems. Of course, for some it provides alternatives (one of them is a question it asks is along the lines of "this query can share a connection with that query. Should it?" If some bozo insists on separate connections for each query...

Yes, one needs to educate consumers about the good, the bad, and the ugly of MSQ but to diss it because it *might* lead to problems is doing it, and those who may benefit from it, a disservice.

You are absolutely right. The stuff you want to do is way beyond an introductory course. Maybe, someone needs to resurrect/improve/streamline the SQL.REQUEST capability that is a separate download from MS. I haven't used it in a long time so I don't know anything about its advantages/disadvantages or why it hasn't been kept "up to date."

 

[Domski]

If they're a complete beginner I think you'll scare the crap out of them trying to teach them all those things too quickly.

Shortcut keys/VBA/array formula etc are great once you know the basics and are confident using them.

I think the tunnel through Mont Blanc is a great time saver but I'm glad I've taken the long way round a couple of times to have gained the full experience.

 

[Joe4]

If they're a complete beginner I think you'll scare the crap out of them trying to teach them all those things too quickly.

That's a good point. I fall "victim" to this myself when teaching people things. There is so much you want to teach them, but if you give them too much at once, it will have an adverse affect and they will tune you out.

If you want to teach them all that you have there, you may want to spread it out over an extended time period (I wouldn't try to do all of it in a day or two).

 

[Oaktree]

No, I completely agree with that.

As I told Tushar in PM:

I don't really have an idea where I'm going with this yet, it was just the type of thing I thought might have some value as a training corse of some sort... Not the business training where a bunch of people go to training they're not interested in just so we can all check the box and pat ourselves on the back, but the type of training where there's someone with a genuine interest to learn and who could be taken under one's wing in 1:1 type training. Or, when I get a subordinate into whom a love for spreadsheets can be instilled (i.e. beaten ).

 

[NateO]

Well, it wasn't meant as bait but there was a possibility you might consider it as such and I still left the language in place. So, if one wants to be picky, one could argue that leaving in language that I knew *might* be considered bait was itself bait, even if it was "second level effect" as it were. {grin}
{snip}

*LOL*

Okay, well you'd definitely make someone's eyes gloss over if you went into this kind of detail with MS Query, and I think that's my point... You want people to push the envelope and ramp up productivity, but you also want them to have a handle on what they are actually doing, as well.

Perhaps MS Query does not deserve some of the flames I've been throwing at it, recently... I'm pretty sure my example of MS Query/End User creating a problem is extreme, yet I've seen others trying to replicate the construct, and it is eye opening in terms of what this thing is capable of when one doesn't consider/know what it is actually doing...

I'm not too worried about MS Query, I know how this thing is working, via the school of hard knocks...

My thinking is that when one does use this tool, they're implementing some [more] advanced concepts in programming, and they should be cognizant of this to some extent. You are housing SQL strings, ODBC Connections, etc...

And my experience of trouble-shooting MS Query maintenance issues over the years tells me that you will need to interact with QueryTable Objects with VBA to keep these interfaces up and running over the years... Or rebuild...

So, show people MS Query? Sure. Excel 101? Negative, i.e., I think this has to fall in the intermediate++ category...

 

[PaddyD]

I have recently been asked to do some 'ask the guy who knows about excel' sessions for some university students - effectively an excel 101 for those who have a need to know.

for this group of university students who've apparently already had need to use excel in previous years, the 101 is going to have to include 'what the equals sign signifies & how to enter a formula' - never underestimate how simply you may have to start, nor how quickly seemingly obvious features can become overwhelming...

 

[Greg Truby]

I'd have to agree with Domski and Paddy. K.I.S.S.!!! I developed an Excel course for my coworkers a couple years back. I used a user survey to break users into skill categories. I had 3 or 4 people that were adept at Excel, and about a dozen real tenderfoots. For those in the true beginner-level, I really had to struggle to meet their needs and not blow past what I thought were things that were sooooo simple as to not even warrent mention. For example: for the beginners, tools like the "insert formula wizard" (that little fx button on the formula bar) were helpful whereas I, who use Excel pretty much every day, use that tool maybe once or twice a year. Even things like showing how to define named ranges can be "daunting" to some (let's not even mention trying to discuss naming formulas (dynamic named ranges; constants, etc.)). And don't be disappointed if everyone in the group doesn't get all excited about some of the "nifty" tricks you try to show them. A few may understand just how really slick your little "gems" are, many won't.

Best of luck. You'll find that it is a learning experience for you too!

 

[PA HS Teacher]

I've taught several Excel "In-Service" days to fellow teachers. I too am humbled by how quickly they get overwhelmed, and consequently bored.

I've found it best not to walk them through too much as a group. They get all over the map really quickly. I like to take 5 or 10 minutes highlighting the various ways I've used in my teaching, planning, and general organization. (keeping it simple without explaining all of my "gems", even though I'd like to. If the other person doesn't get the joke, it's not as fun to tell the joke.)

I give them a CD of some of my spreadsheets, including one sheet that is a running list of descriptions with hyperlinks, an autfilter, and some useful formatting. I use features with the hope that someone will ask about it.
Some of the links are to my files

Some of the links are to useful websites, including of course this site, excel tutorials/explanations of the basics, and sites with example spreadsheets from other teachers

I then take 30 minutes or so a quickly open some of my files and show people the gist of the file. (many of the files, they can open as I'm taking and play along) I usually spend no more than a minute or two on each, but it varies depending on interest level.

I then challenge/ ask people to think of some mundane, boring, tedious task (though it could be interesting) that they wish they could do quicker, better easier. With some encouragement they usually come up with good ideas for projects to work on. Often involving student data, tracking things, creating forms, doing simple calculations hundreds of times etc. All stuff Excel is good at.

I then walk through some of the real basics for about 15 or 20 minutes and tell the others to go and tune me out, research/ work on their project if they are ready.

I spend most of the rest of the day giving individual and small group advice/instruction. I usually have a pretty good handle on what their trying to do and can can steer them away from some of the mistakes everyone makes in the beginning. People wind up helping each other. They usually leave with something useful, and they have a CD with some example files and hyperlinks to places they can get help.

If most of you are anything like me, you learned by trying to accomplish specific tasks, not proceeding through a book in sequence. I think most people learn that way.

 

[gwkenny]

Getting a little off topic towards teaching rather than mentoring...

But I do much of the same thing in both situations...

I tell folks that they aren't there to remember everything I'm going to go through. They are not supposed to write everything down or else we'll never get anywhere. They are there to absorb and understand the features I am going to illustrate so they know Excel's capabilities. So when they come across a situation they know what's possible with Excel because they've seen it before. Then it's just a matter of figuring out how to do it (that's where you really remember stuff ----> when you apply it).

Course I supply them with materials and examples. I also teach how to use the help and that help probably has the answer to almost all of their questions. The problem is how to get help to give you the right information quickly, heh.
___________________________________________

I think this is a great thread. I'd add some more basic stuff for charts.

1) Using the keyboard to cycle through chart objects rather than the mouse.

2) Primary and secondary y-axis. Primary and secondary x-axis.

3) Understanding how primary and secondary axis works in regards to two chart types in one chart.

4) Axis manipulation and formatting techniques. For example, let's say you have one point that's an outlier, how to represent a break in an axis of several tick marks so you can plot the outlier but not skew the rest of the observations.

5) Creative charting. Using "invisible" series to create waterfall charts, etc....

Spreadsheet structuring:

1) Limit all hard inputs to one area. Try not to mix formulas with hard inputs too much. Never mix formulas and hard inputs in the same row if possible.

2) Clearly delineate formulas from hard inputs (for example, coloring hard input text a different color or background).

3) Don't use circular logic if at all possible. Uncheck iteration in calculation and do the math. Otherwise troubleshooting large spreadsheets and understanding where the error in your circular logic when things are slightly askew can be extremely hard to track down. Can also dramatically slow your computer down by compounding the time it takes for custom user functions...