Problem: For each cell in column A, I want to have three rows in columns B and C, as here. I also want to be able to perform calculations with the values in column C.
- You can't easily calculate using numbers in column C.
Strategy: You might be tempted to use the Alt+Enter trick to enter three lines of data in columns B and C. However, this will not work well in column C. Although the numbers are displayed fine, there is no way to have the numbers in C calculate automatically.
A better option is to merge cells A1:A3 into a single cell. You can then let the data in B fill B1:B3. Here's how:
- Enter a value in A1. Leave cells A2:A3 blank. Select cells A1:A3.
- Select Home, Merge & Center dropdown. Choose Merge Cells.
- Merge Cells is hidden behind this dropdown.
- Cells A1:A3 are merged.
Gotcha: Notice that the vertical alignment defaults to the bottom. This looks okay in a normal-height cell, but not so good in a triple-height cell.
- Change the vertical alignment to top or center. Vertical alignment icons are now on the Home tab.
- Align top is now on the Home tab.
- Creative use of the Borders setting around each group will further enhance the illusion of three rows for each value in column A.
- Align to the top of the cell.
- If you have several rows that need this formatting, use Format Painter mode to copy the formatting. Select cells A1:A3. Double-click the Format Painter icon in the Home ribbon tab. The double-click will put you in Format Painter mode. You can now click in A4, then A7, then A10. Each click will copy the format from A1:A3 to the clicked cell. When you are finished, you can either click the Format Painter icon or press Esc to exit Format Painter mode.