Show Two Values in a Split Cell

September 05, 2023 - by Bill Jelen

Problem: My manager wants me to show two values in one cell, as shown here.

An oddity: One cell with 33 in the top left, 44 in the bottom right, and a diagonal border from bottom left to top right. It would seem there are two values in this one cell. — Figure 1305. One cell shows 33 & 44.

Strategy: This formatting requires a little bit of cleverness.

1. Type 33, then Alt+Enter, about 9 spaces, then 44, then enter.
2. Select the cell, then Ctrl+One to access Format Cells.
3. On the Border tab, use the diagonal border.
4. You need to adjust the column width or the number of spaces in the cell to get it all lined up. If you add too many spaces, the row height will increase by 50%.

Gotcha: You won’t be able to do any math on numbers stored this way.

A better method would be to store the low and high in columns. Then, use the formula =A2&CHAR(10)&REPT(“ “,14)&B2 in cell D2. Turn on Wrap Text. Add the diagonal border.

The trick for two values in one cell: Store the values in A2 & B2. In D2, use a formula of =A2 & CHAR(10) & REPT(" ",14)&B2 — Figure 1306. A & B can be totaled.

This article is an excerpt from Power Excel With MrExcel

Title photo by Lucas Davies on Unsplash