formatting/merging cells

[macinrow]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I want to merge a series of cells at the bottom of a worksheet, to create (a seperate area of) full rows to write in, below columns.

When I do so, the outside borders are removed. When I replace those borders, Excel creates 4 pages with random cells on them.

This is driving me mad! What am I doing wrong?

 

[CyberTaz]

According to many, the first thing you're doing wrong is merging cells in
the first place Merged cells are particularly troublesome to deal with
and are a primary cause of workbook corruption. The problem you're running
into is a prime - though mild - example of the type of headache I'm
referring to. I'd avoid them at all cost.

As an alternative, consider using Format> Cells - Borders. It's a much more
solid way to construct the sheet & (especially for printed work) provides an
identical effect.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I want to merge a series of cells at the bottom of a worksheet, to create (a
seperate area of) full rows to write in, below columns.

When I do so, the outside borders are removed. When I replace those borders,
Excel creates 4 pages with random cells on them.

This is driving me mad! What am I doing wrong?

First, merged cells are the spawn of the devil. Friends never let
friends merge cells. They screw up formatting, sorting, selections,
filters, etc.

Text will naturally flow to the right of a cell anyway, as long as there
isn't anything in the adjacent cell, so can you get away with just
making entries in the left-hand column?

If you feel you must proceed along the path to perdition:

- what do you mean by "outside borders are removed"? Are you merging
entire rows? In that case any borders other than the left-hand border of
column A and the right-hand border of column XFD will be lost since your
new merged cell extends beyond the original bordered area.

- if you're just merging cells within a row, how did you try to
"replace" the borders?

- I can't imagine what you're seeing with "4 pages with random cells"
- what do you mean by "page"? Are these "random cells" inside or outside
of the merged area?

- Can you give a step-by-step description of what you're doing?

 

[macinrow]

Thanks for the help. I pledge not to endure another 4AM curse fest

I'm trying to build my 2009 cycling log. In past years I've used Word; last year Pages (w/ auto column total function) and this year purchased Excel so I could have the latter function and access to easy clip art function (missed from Word) for weather icons.

I use a table/spreadsheet 9 columns wide by 13 rows high. The right hand columns tabulate distance, time, vertical gain, etc. Leftward columns contain date and weather icons and a wide middle row for route description (w/ scrolling text).

The last 3 rows are used for notes -- mechanical, observations, mind/body -- and this is where the problem arises. I would like the text to go right across the chart and, of course, where necessary, wrap down the cell. That's why I merged the cells. When I did so, the outside borders disappeared and I replaced them with the Format> Cells - Border function. But that created cells outside and 3 more pages.

Thanks to your info, I realize that text just goes across cell divs (duh!) and I can simply remove the lines dividing those so they don't print, but that doesn't solve my problem -- text continues outside the cell and creates another page, just like my unworkable attempts. If I ask the text there to wrap, it just wraps in the first cell, of course.

I gotta go for my second ride of the year before I damage my desk :-(

 

[CyberTaz]

text continues outside the cell and creates
another page, just like my unworkable attempts. If I ask the text there to
wrap, it just wraps in the first cell, of course.

<snip>

Didn't take that into consideration since your op used the phrase " rows to
write in " - I thought you meant hand-written notes after printing.

Have you given any thought to drawing a text box or some other shape the
height, width & placement you need? You can leave it filled with white (or
some other color) & border it to suit yourself. Much easier to deal with in
any number of ways than merged cells.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[macinrow]

Thanks very much for the tips. I went for the text box solution and it works OK.

I must say that, but for the easy clip art function, I nearly went back to Numbers, where cell merge is not an issue and formatting glitches are nearly non-existent.

In case anyone else runs up against this, what I did was inserted text boxes, fitted them closely within the cells; then made the text boxes transparent (in colors, weights, and fills palette) and formatted text to wrap in box.

 

[JE McGimpsey]

I must say that, but for the easy clip art function, I nearly went back to
Numbers, where cell merge is not an issue and formatting glitches are nearly
non-existent.

Formatting is the among the least of the problems with merged cells, and
merged cells in Numbers is certainly NOT "not an issue".

Merging cells plays havoc with formulae, sorts, selecting columns,
copying and pasting, as well as formatting.

If you want to see a real risk with merged cells in Numbers, try:

In A1, enter =C11
In B11 enter 3

Note that C11 = 0

Merge B11:C11

Note that Numbers has, without informing the user,
changed the formula in A1 to =B11, returning '3'

Split B11:C11

Note that Numbers has retained =B11 as the formula in A1

Changing the formula *MAY* be a good assumption much of the time.
Imagine, though, the frustration of finding the error in a large and
complicated workbook if the cells were accidentally merged and then
"corrected" by being immediately split. Or the problem if the change
wasn't noticed - decisions could then be made on invalid info - again,
without any warning.

Another:

In B1 enter =COUNTA(C)
in C12 enter 5

Note that B1 returns 1

Merge B11:B12

Note that there is NO visual indication other than
the gridline between B12 & C12 disappearing (i.e, '5'
still appears under column C, but now B1 returns 0

Absolutely HORRIBLE application design - one (of several) that should
disqualify Numbers from use in any critical application setting (that
particular problem doesn't exist in XL, fortunately).

But then, that's just an example of why Merged Cells are problematic in
ALL spreadsheet apps.

In case anyone else runs up against this, what I did was inserted text boxes,
fitted them closely within the cells; then made the text boxes transparent
(in colors, weights, and fills palette) and formatted text to wrap in box.

Excellent solution. In general, text *labels* belong in cells.
Significant blocks of text belong in text boxes.

 

[CyberTaz]

<snip>

But then, that's just an example of why Merged Cells are problematic in
ALL spreadsheet apps.

<snip>

Not just in "ALL spreadsheet apps" - They'll corrupt a Word doc in a
heartbeat if you use them in a Word table The only use I've found for
them is to design a form to be printed & not used "live" at all.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[JE McGimpsey]

Should have been merge B12:C12, obviously...