Excel: Merge Cells and Multiple Lines in Cells

A couple of related formatting tricks that you can use within a spreadsheet this time.

Let's start with a look at a standard bit of data.

Column A holds the collective heading - the people in Column B in the first four rows belong to Team A and then Team B personnel are held in rows 5 to 8.

We could just repeat the descriptive header by selecting cell A1 and copying it down to the next three rows. But it then starts to become less clear because there is too much confusing text.

So instead we'll merge cells A1 to A4 together as a single cell. In Excel a range of cells is written using the syntax A1:A4 - the colon means "through to".

So we need to select the four cells by clicking into A1 and then click and dragging until the range A1:A4 is selected.

Then we select the Merge & Center whilst tutting slightly at the American spelling and hey presto, the four cells become one whilst at the same time the text has been centred in the horizontal plane. The text though has moved to the bottom of the new cell, to the spot previously occupied by cell A4.

This is because Merge and Center (tut!) is more often used to merge cells next to each other horizontally - grouping a number of column headings for instance.

We can do a couple of things now to format that and they include centring (darn it - the American spelling would look better there...) the text vertically and twizzing it round (Lancashire/technical term for aligning it vertically).

Select cell A1 (the merged cell still has that designation) and right-click the mouse to bring up the dialogue box shown.

Now select Format Cells.

The resulting dialogue window allows us to format lots of things but for now we are interested in the Alignment tab (pointy hand 1).

We can alter the text alignment to centre the text both horizontally and vertically (pointy hand 2) and we can twiz (or is it twizz?) the text (pointy hand 3).

Having clicked the Ok button and resized the cell by double-clicking the right cell wall to get the best fit the result is as shown below.

The pointy hand shows the simple way of aligning text left, right or centred in both horiziontal and vertical planes. The AB button with the little arrow to the right of the pointy hand can be used to twiz text - it opens the same dialogue box we have just used.

That's one way of doing it. Here's the other - which may appear simpler, but may not meet your needs. It's always good to have an alternative!

If whilst typing the names of the team members we used the ALT-Enter key combination we can force cell B1 to accept multiple lines within the single cell.

So we type John Tharmy then hold down the ALT key and whilst it's held down, tap the Enter key. Now without moving from the cell type Sally Vaite and use ALT-Enter again until we read the last name. Now tap the Enter alone as you would normally do and the list of names each occupy a line within the single cell.

Job done!

Tweet