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



## Oaktree (Jul 18, 2006)

I haven't decided yet if I want to writeup (and steal writeups for  ) these items yet, but I wanted to make a list of things that I'd teach a Noob about Excel.  Not an "all-inclusive" list by any means, and not a book that's 100s of pages long.  I think J-walk, Bill Jelen, Steve Bullen, etc. fill the need for books...

But, I kept thinking that I wish there was a (somewhat short) list out there somewhere that had things to teach the "new guy" that would make his job easier and combine some of the bigger lessons learned and things we've seen on this board.  Perhaps there's a better one out there and I'm just reinventing the wheel...?

I came up with this and was curious as to what I've missed that you would have included:


```
1.  Navigation
   a.  Shortcut Keys and how learning them can save you oodles of time
   b.  F5 is your friend
   c.Freezing Panes
   d.PasteSpecial
2.  Cosmetics
   a.Number Formatting
   b.Conditional Formatting
   c.  The few cases where it may be appropriate to use merged cells
   d.  F4 is your friend
   e.  Formatting cells outside your used range will cause your file size to balloon
3.  Printing
   a.Print area
   b.  Fit to
   c.  Repeating rows/columns on each page
4.  Entering formulas
   a.  Using F9 to audit formulas
   b.F2
   c.  CTRL { and CTRL }
   d.Trace Dependents / Precedents
5.  Dates and Time
   a.  =A2 - A1 calculates the # of days between two dates
   b.  NETWORKDAYS function
   c.Weekday
   d.  EDATE/EOMONTH
6.  Calculating and summarizing
   a.Conditional calculations
       i.CountIf
       ii.SumIf
       iii.SumProduct
       iv. How to use an array formula to do AVERAGEIF, PERCENTILEIF, etc.
   b.  How the manual/automatic calculation setting works and affects other workbooks
   c.  SHIFT+F9 is your friend with large workbooks
   d.Data   ' subtotals
   e.  Add something about random numbers here
   f.  The few cases where it may be appropriate to hard code data
   g.Goal Seek And Solver
7.  Retrieving data
   a.Filters
       i.AutoFilter
           1.  The SUBTOTAL function
           2.  Alt + ; is your friend
       ii.Advanced Filter
           1.  Getting unique records
           2.  I'm debating whether I want to go further with this
   b.Linking Cells
       i.  Why this is a great thing
       ii. Why this is a terrible thing
   c.VLookup
       i.  The difference between an "Exact Match" and an "Inexact match" and the assumptions for each
       ii. How to use { , ; }'s to put a list in your formula
   d.  INDEX/MATCH
   e.Choose
   f.INDIRECT
       i.  Warning for needing to keep other referenced workbooks open
   g.  LOOKUP and returning the last item
   h.Array formulas
       i.  Match on two columns
       ii. TRIM/LEFT within VLOOKUP
   i.Pivot tables
       i.What they  're for
       ii.Grouping dates
8.  Charts
   a.  Linking chart aspects (heading/etc.) to cells
   b.  Add more stuff here
9.  Named ranges
   a.  Dynamic named ranges
10. BOOK.xlt, SHEET.xlt, and PERSONAL.xls
11. Security
   a.Data Validation
       i.Dependent lists
       ii.Input messages
   b.Protecting Worksheets / Workbooks
       i.  How to
       ii.Limitations
       iii.xlVeryHidden
   c.  Locking your code
   d.Digital Signatures
       i.  Scheduling automatic tasks
12. Do I want to go into details of the data analysis offerings here…?
13. VBA
   a.Events
       i.Open
       ii.Change
       iii.Calculate
       iv.BeforeSave
       v.BeforePrint
   b.Input box And MsgBox
   c.  Add something about the macro recorder here
   d.BackgroundQuery = False
   e.  A short introduction to userforms
14. References
   a.Obviously , www.mrexcel.com / board2
   b.  http://www.mrexcel.com/board2/viewtopic.php?t=131224 (recommended add-ins and links)
   c.www.msdn.com
   d.  http://support.microsoft.com
```

Thanks for reading,
Cheers,
Matt


----------



## Jay Petrulis (Jul 18, 2006)

1.  The distinction and proper use of absolute/relative cell referencing is a must.  Also, F4 for toggling between the options (make sure they know the other use of F4 to repeat a previous action).

2.  Don't expand any more than unique records for the Advanced Filter option.

3.  Get rid of the Events section in VBA -- go with the basic subroutines only at first.

I would expand on the reason for VBA in the first place -- initially to automate repetitive tasks, then to expand the capabilities of the program and user.

Above all, stress that to use VBA wisely, the user must have a good handle on Excel as a standalone product.  S/he should be comfortable building Excel models before using VBA.  Why try to run before you can walk?

4.   Use of the Indent option and Alt-Enter!!!!!  If I see one more workbook where the spacing is done by using the space bar, I may go postal.


----------



## Joe4 (Jul 18, 2006)

Matt,

I would include using Text to Colums and importing Text Files.

Also, if it is a Excel NOOB, I probably would not get into VBA at all, other than the Macro Recorder and running pre-written Macros (and discussing Macro Security Levels).  That is more of an intermediate skill, in my estimation.


----------



## Oaktree (Jul 18, 2006)

Good call, guys. 

I agree with your points, though I'm still on the fence with VBA.  It's almost too important to leave out all together, too complex to go into any detail with, and brushing over it with just the macro recorder doesn't seem quite right either.  Goldilocks syndrome, I suppose.

Nice Statler and Waldorf retro throwback, Jay.


----------



## Joe4 (Jul 18, 2006)

Matt,

I think VBA is a great thing to teach and learn, just not in a beginner class.  Maybe have a follow-up class for that, where you can really dive into it.

I don't know the demographic of your environment, but I have taught beginner classes at my work, and I don't think most of them would understand or do well with VBA.  Formulas are hard enough for them to figure it.  

I guess it really depends on the audience you are trying to target...


----------



## tusharm (Jul 18, 2006)

Commendable effort.  But, I suspect it will go down the road of so many FAQs.  No one reads them because the regulars don't insist on it.  Nonetheless, if you do persist...

I prefer to emphasize doing things right the first time around.  That influences my comments below.

#2: One place where MS broke its own rule about number formatting not affecting the kind of information is with the Text format.  If a cell is pre-formatted as text, new information entered in the cell will be treated as text.

#4: The Evaluate button on the Auditing toolbar.  Nothing better than it to figure out what XL is is actually doing with a formula.  For an unintended introduction to the capability see
Array formulas and Excel’s “Evaluate Formula” capability
http://www.tushar-mehta.com/excel/tips/array_formulas.htm#_Array_formulas_and_Excel%92s%20%93Evaluat

#4: R1C1 addressing.  IMO, a "must" for XL2007.

#4: *Mixed* absolute/relative addressing.

#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.  See

An introduction to array formulas
http://www.tushar-mehta.com/excel/tips/array_formulas.htm
and
Building and using a relational database in Excel (with a little help from MS Query)
http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html

#8: Dynamic (self-adjusting) charts

#9: Names for ranges defined with relative addresses.

#9: Define names at the most restrictive scope possible, i.e., use worksheet names rather than workbook names whenever possible.

#9: Point out the Name Box (to the left of the formula bar).

#9: Name shapes by selecting them and entering a name in the Name Box.  Naming an embedded chart doesn't help much.  Instead name the container (called a chartobject).  To select the chartobject, select any cell, then hold the CTRL or SHIFT key and click the chart.

#9: Use names to define cells and use them in formulas making the workbook model that much more readable.

#10: Not a fan of any of those approaches.  IMO, these were mistakes on MS's part.  I know I am in a minority of a minority but I would do this "right" and discuss templates and logically segmenting code into funtionally organized add-ins (rather than have everything dumped in personal.xls).

#10: The correct way to use a template is to create a new workbook based on the template, *not* open the XLT file and then do a "Save As"

#11: A major weakness with data validation is people copying data into those cells.  For a more comprehensive validation scheme see
http://www.mrexcel.com/board2/viewtopic.php?p=1068023#1068023
and a soon to be published add-in on my website.

#13: Please emphasize the downside of VBA.  Too many, including many regulars here, jump to VBA for problems that are best solved in XL itself.

#13: Option Explicit is not an option.  It is mandatory.  Set the appropriate setting in the VBE.

#13: Declare variables at the most restrictive scope possible.

#13: Use arguments rather than global variables to exchange information between procedures.

#13: Modularize code.  I should share the concepts in a chapter I wrote for "VBA and macros for Microsoft Excel"

#13: Introduce OnTime.  See
http://www.cpearson.com/excel/ontime.htm
except that RunWhen should be a Date not a Double

#13: Introduce UDFs.  Too many including those who use VBA regularly, don't know enough about the power of UDFs.  See
Excel formulas and User Defined Functions
http://contrarianview.blogspot.com/2006/02/excel-formulas-and-vba-user-defined.html

and another unintended and somewhat incomplete introduction to writing array aware UDFs:
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/vba.html#from_worksheet

#13: If you must introduce events, point out the significant problems of worksheet and workbook events. See
Monitoring events
http://www.tushar-mehta.com/excel/vba/vba-XL events.htm

#13: Leverage the XL OM.  Invest time learning it.  F2 is your *best* friend in the VBE.  See
Beyond Excel's recorder 
http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

and

Case Study – Understanding code
http://www.tushar-mehta.com/excel/vba/vba-Understand code.htm

#13: Emphasize writing good code from the beginning.  See
Case Study – Defensive Programming
http://www.tushar-mehta.com/excel/vba/vba-defensive_programming.htm

#13: Use mnemonics (MS and user-defined constants and enumerations) and not numbers whereever possible.  See a page that is neither complete nor linked into the site navigation scheme:
Mnemonics or Constant Names
http://www.tushar-mehta.com/excel/vba/mnemonics.htm

#13: I hate InputBox and the method is rather user-hostile if one needs more than 1 piece of information.  Have folks invest in developing a properly designed userform.

OK, I should have covered almost everything, though I doubt everything, I would have liked to.  Not to mention that an hour+ on a topic that may not even get off the ground is not necessarily the smartest use of my day. {grin}  But, I guess it reflects the passion I have for the subject.


----------



## Jay Petrulis (Jul 18, 2006)

> Nice Statler and Waldorf retro throwback, Jay.



Those guys are my heroes!

For those who don't know/recall, Statler and Waldorf were the two hecklers in the audience of "The Muppet Show."

Tushar has made a very nice list.  If I were to teach a class on this, I would emphasize the "right" way to structure a workbook over the specific tools and techniques.  A correct design helps a lot.  Below are some additional thoughts.

1.  Separate data from processing from output, although the calculation and output is often one and the same in Excel.

2.  If you know that the model parameters will expand, build that in at the outset.  As the model incorporates enhanced features, it is far better to have planned for that extra item(s) rather than adding it haphazardly in an out-of the way cell or column.  Of course, no one always has unlimited foresight here, but steps can be taken to ensure that flexibility is gained.  

3.  Get the functionality first, then the formatting.  I know that I may be in the minority here, but the presentation aspect of spreadsheet output is given too much thought to the detriment of the functionality, ease of use, maintainability, etc.  

4.  I have inherited/examined a number of spreadsheets where color coding conveys important information, but the scheme is not explicitly defined and listed anywhere.  I would recommend against any data analysis that has color coding as a critical component of the logic (outlier data one exception).  For final reports/presentation, coloring is fine, but not for data analysis.

5.  Discuss consistency in formula building (don't mix within a row or column, for example).


----------



## tusharm (Jul 18, 2006)

Oh, one more "must have" is data organization/architecture/structure.  None of these structures that on their face are naively-appealing: no one sheet/book per person/week/whatever.

Put the data in a single table with an additional column that identifies the person/week/whatever.  This should go between #1 and #2.


----------



## SydneyGeek (Jul 18, 2006)

Some great ideas in here. 

This is a very simple one: Extend a range to include a single blank column / row, press Alt = , and you have column / row totals. 

Denis


----------



## tusharm (Jul 19, 2006)

Another basic idea that doesn't seem the easiest to grasp.  Shows up in many forms:

What's a string literal?
What's a zero length string?
When's a number not a number?
Why is an IP address not a number?
What's the difference between a string literal and a variable?
How does one take a string literal and replace a piece of it with a variable?
How does one get a double-quote into a string?


----------



## NateO (Jul 19, 2006)

> #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 (Jul 19, 2006)

> > #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}
> 
> 
> ...


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 (Jul 20, 2006)

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 (Jul 20, 2006)

> 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 (Jul 20, 2006)

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 (Jul 20, 2006)

> 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 (Jul 21, 2006)

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 (Jul 21, 2006)

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 (Jul 21, 2006)

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 (Jul 22, 2006)

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...


----------



## riaz (Aug 16, 2006)

In Luxembourg where I live, the Ministry of Employment encourages further education to improve people's chances of getting a better job through better skills.  Among other things, they organise courses in excel, spread over 10 to 12 weekly sessions of three hours each per module, so absolute novices, sometimes learning in what may not be their mother tongue, are eased into the use of whatever skill they are trying to obtain.

The site is in French, but I have copied the links to the detailed descriptions in english of the three modules they offer for excel - which one can join only after one has passed the module on knowing your way around the pc.

This may give an idea of where to pitch for which level of students.

http://www.lllc.lu/up/01-csoir/2006/modules/1054-EN.pdf
http://www.lllc.lu/up/01-csoir/2006/modules/1055-EN.pdf
http://www.lllc.lu/up/01-csoir/2006/modules/1056-EN.pdf


----------



## DreamAlchemist (Aug 18, 2006)

Being an instructor of Excel these are the things i teach my newbs err  new students outside the basic: new open save close.

Proper structure (When and when not to skip rows and columns).
The evilness to merging cells.
Formating numbers( dates, commas, custom )
Explaing when a number is not a number (When it's ajar of course :D oh wait that is a door)
Absoulte, mixed and relative referencing.
Order of operations (PEMDAS)
Printing, print areas, rows/columns repeating
Named ranges
Charts (basic charts, not pivots)

I rarely will give keyboard shortcuts (unless they ask specifically)
I reference VBA and what can be done, but hold off til later to show them coding or the recorder.


----------



## riaz (Aug 18, 2006)

> The evilness to merging cells.


Hi Dream

Why do you consider merged cells an evil?  I am an accountant and use them to write entire financial statements.  We have to follow a set template, which I have written out using merged cells and auto row height, and a macro to hide unused rows and renumber notes accordingly (with grateful acknowledgement to this board's members - oops, wrong thread  ).  I think merged cells are beautiful.

Do you know anything I should?

Kind regards


----------



## Cbrine (Aug 18, 2006)

riaz,
  I think Dream is referring to the problems with trying to insert and delete rows and columns, and how this is prevented by merged cells.(Which I've found to be a major pain in the past, usually with someone else's workbook, where there was no ryhme or reason to the merge's)  Merged cells are OK, if you know how to use them, it's when you don't that they become a problem.

Cal


----------



## lenze (Aug 18, 2006)

Answering for Dream
Merged cells make a sheet LOOK pretty, but are not friendly to the many data analysis tools in Excel. I avoid them when I can. You can usually use Center Across Range instead.

lenze


----------



## riaz (Aug 18, 2006)

I see your point about data analysis, but I only merge cells with text in them, where there is no data to analyse.  I then protect the sheets and hand them out like confetti for everyone else to fill in underlying data sheets, and they are ever so grateful to the Excel guru they have just discovered in their midst - little do they know I am an avid reader of this forum  :wink:

That is also the reason centre across selection does not do the job for me.


----------



## Oaktree (Aug 18, 2006)

It's not just the data analysis tools...

1) Merged cells are VERY annoying for keyboard shortcut users.  (e.g. if A1:B1 are merged and you are in A2, CTRL+Spacebar to select column A will unwantingly select column B as well)  

2) Copying and pasting with merged cells is a PITA.  

3) Autofitting row height, while possible, requires (IMO) non-intuitive workarounds.

etc...


----------



## just_jon (Aug 18, 2006)

Good spreadsheet and workbook layout.

You can save yourself hours of convoluted formula writing by starting with good up-front design. Get 'em to think through the project, not only today's specs but what the beast might look like in a year or two.


----------

