Creating Two-Line Custom Formats

Excel is quite flexible in how it allows you to set up custom formats for displaying all sorts of values. Most custom formats are straightforward and easy to figure out, once you understand how custom formats work. (Custom formats and how to set them up has been discussed fully in other issues of ExcelTips.)

What if you want to create a two-line custom format, however? For instance, you may want to format a date so that the abbreviated day of the week and day of the month is on the first line, followed by the unabbreviated name of the month on the second line. Using such a format, a date would appear in a single cell in this manner:

Sat 4
March

Most of this can be done by the custom format "ddd d mmmm", but you need to figure out a way to add a line break between the "d" and the "mmmm". Excel won’t let you press Alt+Enter between them, which is what you normally do to add a line break.

The solution is to use the numeric keypad to enter the desired line break in the format. Follow these steps to set it up:

1. Select the cells you want to format.
2. Display the Number tab of the Format Cells dialog box. (Click here to see a related figure.) In Word 2007 display the Home tab of the ribbon and click the small icon at the bottom-right of the Number group. In older versions of Excel choose Format | Cells.
3. In the Category list, choose Custom.
4. Delete whatever is in the Type box.
5. Type “ddd d” without the quote marks.
6. Hold down the Alt key while you press 0010 on the numeric keypad. This enters the line feed character, and it looks like the portion of the format you typed in step 5 disappears. It is not really gone; it has just moved up above what can be displayed in the Type box.
7. Type “mmmm” without the quote marks.
8. Click the Alignment tab. (Click here to see a related figure.)
9. Make sure the Wrap Text check box is selected.
10. Click OK.

After setting up the format in this manner, you will need to adjust the row height of the formatted cells so that the entire two lines of the date will display.