Teaching coworkers excel basics

[tkaplan]

So I'm the excel geek in my company. and people come to me to help them format the spreadsheets, but after they have messed them up so bad that it's easier to start from scratch: merged cells galore (so array formulas dont work), blank columns to create space (instead of just indenting the data), using spaces to get to the next line instead of alt+enter, etc.
So I am on a mission to teach people who know very very very little about excel some basics to make life easier for them and save my sanity.
I have started weekly tips and am compiling a list of basics to teach and am asking for some input of any other very simple things that i can teach them (like alt+enter, center across selection) etc.
Here is the list I have so far. Any input would be greatly appreciated!
1. alt+enter
2. center accross selection
3. no blank columns - use resize and indent
4. wrap text
5. & sign dont show up in header and footer - need to use &&
6. paste vs. paste special
7. drawing borders (for presentations)
8. round() function
9. validation
10. sumproduct() funcation (I will only teach the very basic - not all the advanced stuff you can do with it. it will fly above their heads)
11. set print area
12. printing multiple tabs at the same time or entire workbook instead of clicking every tab individually (they couldnt figure out how i could print a 30 tab workbook in 2 seconds ....then i realized they were clicking every tab individually!)
13. conditional formatting based on cell value (i think based on formula may be too complicated for now)
14. referencing other spreadsheets within the same workbook so no redundant data

Any input would be greatly appreciated!

[Bob Phillips]

Is 9 data Validation? If not DV.

Custom number formats.

Difference between actual and superficial values (what is as against what you see - see next).

How dates are stored and presented.

Forget SUMPRODUCT for now, teach them COUNTIF and SUMIF and VLOOKUP.

Use of colour - keep it simple and muted.

[tkaplan]

thank you!
the only sumproduct i will teach them now it the very basic because we do a lot of having to add up one column multiplied by the other column so i constantly see formulas like this:
= (a1*b1)+(a2*b2)+(a3*b3) going on an on for 15 rows so I think they'd be able to handle learning the basic =sumproduct(a1:a3,b1:b3) but nothing further than that.

thanks for the input!

[mdmackillop]

Have a look at ECDL. I'm sure there are others as well.

[Gingertrees]

--> Wrapping text. Make sure to include that in your Alt + Enter lesson (i.e. "Why can't I see this paragraph I typed in a this-wide cell?")
--> Paying attention to message boxes, conditional formatting, etc

Good luck - may you fare better than I have in this endeavor.

[Dr.K]

Bill Jelen has a skinny book called "Guerilla Data Analysis with Excel".

I already knew most of whats in it, but the chapter on LOOKUPs is so good that I xeroxed it and handed it out to my corworkers. That was the number one thing I used to get questions on.

So, thats my suggested topic:
VLOOKUP, HLOOKUP, INDEX & MATCH

[Gingertrees]

Don't forget DSUM ! Great for when the boss asks to make your spreadsheet into useable data.

[Cyberdude]

Make sure they know how date/time is stored in Excel.

When a date "operation" doesn't work, make sure the date value is a "date" and not "text".

Show how to remove "time" from a "date" value.

[pcstru]

The differences between relative and absolute references (so that copy works for them, rather than against them!).

[James Niven]

How about Concatenate, very useful indeed.
Quickly moving around large spreadsheet.
Copy large areas of data in the cells with crtl+shift+end
Basic Pivot Tables and charting