How can I get text on merged cells to align with single-cells in Excel 2010?

[sugarmercy]

Hello,
I'm working on a dynamic spreadsheet in Excel 2010. It is a bylaws amendment document, and so each line has to be numbered. I used a center column to display the ongoing numbers. On either side, I have merged cells so that every paragraph could be in one merged cell together, but I want each line of text within these merged cells to correspond to a line number in the center column.

What I've noticed is that the first 10 or so lines in the merged cell will align perfectly with the line numbers in that central column, but as the paragraph in the merged cell continues, the alignment falls out of place, so that my last sentence in the merged cell, for instance, looks like it could be either 504 or 505 based on the center column. This is especially noticeable when I print it.

This isn't an auto fit issue--I don't want to auto fit the cells as I need to have each line of type numbered. I've tried changing the vertical alignment but that only changes where in my paragraph the line-to-line alignment starts to not correspond.

I've also tried changing the row height--it is currently set at 0.53cm, and I've tried both making it smaller and larger, and Excel just reverts it back to 0.53cm

Any help is very sincerely appreciated!

 

[BenMiller]

Try using a monospaced font, like Courier.

Does that help?

 

[sugarmercy]

Try using a monospaced font, like Courier.

Does that help?

Hi Ben,

Thanks so much for your response. I tried out your suggestion of changing the font to Courier with no success. It seems that Courier spaces out horizontally, but my concern is vertically. I just want my text in the merged cells to go line-for-line with the unmerged numbered column down the middle. Instead, it will only sometimes be line-for-line, effectively numbering every line of information in my document, while other times, the last few lines of a merged cell will be "in-between lines," making the whole document pointless.

These are some of the ideas I have/things I've tried to give you a better picture of the problem:

1. I tried resizing the row height. There seems to be a glitch as I try to make it shorter from 0.53cm to 0.51 or 0.5, but Excel then reverts to a much lower number, like 0.48. I tried making it bigger and had similar problems. Does Excel revert the sizing of "row height" based on font size, or something? What is going on here?

2. The font of the entire document is all TNR size 11, so I don't think changing the font size will alleviate anything, but maybe I'm wrong?

3. I've tried adjusting the vertical justification from top, middle and bottom but it only changed where the text starts going "out of line" with the numbered column.


So, basically, what do I need to do to get every line to have a corresponding number?

Thank you in advance for your time! I'm at my wits end!
- Colleen

 

[BenMiller]

Your cell can't be that long, because Excel only displays the first 255 characters in a cell. When you merge cells vertically, the text will align with the rows alongside it. Try it on a new sheet, with default settings.

Arial size 10 will align with a row height of 12.75

 

[shg]

... because Excel only displays the first 255 characters in a cell

@Ben: Excel will display about a thousand characters in a cell, and many thousands if interspersed with line feeds

@sugar: Why not do this in Word, which supports line numbering directly?

 

[sugarmercy]

Your cell can't be that long, because Excel only displays the first 255 characters in a cell. When you merge cells vertically, the text will align with the rows alongside it. Try it on a new sheet, with default settings.

Arial size 10 will align with a row height of 12.75

No, it's only about 20 or so lines at most, but for some reason the cells that have been merged vertically do not correspond with the rows beside it. I tried it on a new sheet, but I just copy + pasted... how would I get it to have default settings? Rookie question, I know

 

[sugarmercy]

@Ben: Excel will display about a thousand characters in a cell, and many thousands if interspersed with line feeds

@sugar: Why not do this in Word, which supports line numbering directly?

Thanks for the response! Ugh, I know, but the task my boss gave me what to take a huge 30 page table from microsoft word and *transfer* it into Excel. My work also wanted it to look *exactly the same*... but in Excel. They didn't seem overly pleased when I said it can't look EXACTLY the same (as they had used a lot of formatting options in Excel that just aren't available in Word), but I've been doing my best. This is the first real hiccup I've had after work on this for almost two weeks. (Yes two weeks because whoever made the original in Word used the SPACEBAR instead of TAB or other forms of proper indentation for everything.) I want to die.

 

[BenMiller]

No, it's only about 20 or so lines at most, but for some reason the cells that have been merged vertically do not correspond with the rows beside it. I tried it on a new sheet, but I just copy + pasted... how would I get it to have default settings? Rookie question, I know

Default settings: I meant the standard typeface and font size, row height, etc. Those are built to fit. I gave you one measurement which will work.

 

[shg]

If you merge the cells containing the line numbers, and separate the line numbers with line feeds, and use the same font face and size for the numbers are the text, the rows between text and numbers will align.

Your boss needs training in remedial computer.

 

[sugarmercy]

Your cell can't be that long, because Excel only displays the first 255 characters in a cell. When you merge cells vertically, the text will align with the rows alongside it. Try it on a new sheet, with default settings.

Arial size 10 will align with a row height of 12.75

12.75 what? mm? Mine used to be in mm then suddenly changed to cm with no knowing alteration from me. I would definitely like to give this suggestion a try! I changed all the font to Arial size 10 (sadly this means I will have to re-do my cell merges for the entire long document, but if it works in the end, I'm happy) and the row height is set at 0.53cm. What should I change it to? Thanks.