excel-feature

How To Create Two-Line Custom Formats

Two-Line Custom Formats

Excel is very flexible in how it will allow you to set up custom formats for displaying all manner of values. Most custom formats are straightforward and easy to figure out once you have an understanding of how custom formats work.

Suppose you wanted to create a two-line custom format, though? For example, you might want to format a date so that the abbreviated day of the week and day of the month is on the first line, and the full name of the month is on the second line. Using such a format, your date would appear as shown below.

 

Most of this can be done with the custom format “ddd d mmmm“, but you have to figure out a way to add a line break between the “d” and the “mmmm”. Excel will not let you press Alt+Enter between them, which is what you normally do to add a line break.

The solution is to use your numeric keypad to enter the desired line break in the format.

Follow the steps below to learn how:

  1. Select the cells you would like to format.
  2. Display the Home tab of your Ribbon.
  3. Click the small icon at the bottom-right of the Number group to display the Number tab of the Format Cells Dialog Box.
  4. In the Category list, select Custom.

  1. Delete whatever is in the Type box.
  2. Key in ddd d.
  3. Depress the Alt key while you key in 0010 on your numeric keypad. This will enter the line feed character, and it looks like the portion of the format you typed in Step 6 disappears. It is not really gone but has moved up above what can be displayed in the Type box.
  4. Key in mmmm.
  5. Click the Alignment tab.

  1. Be certain the Wrap Text box is selected.
  2. Click OK.

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

2 thoughts on “How To Create Two-Line Custom Formats”

  1. Sorry, but I don’t understand what it means to “create a two-line custom format”. I tried following the instructions and they didn’t work…specifically, things went south starting with step 7.

  2. Chris.Partezana

    Two line date format displays the date on two lines within a single row (cell).

    The day date format gives you
    d = one numeric character for the day of the month (and two for double digit days) 1-9, 10-31
    dd = forces two digit character for the day of the month 01-09, 10-31
    ddd = short day name of the week. Mon, Tue….
    dddd = long day name of the week. Monday, Tuesday…

    The month date format gives you
    m = one numeric character for the number of the month (and two for double digit months) 1-9, 10-12
    mm = forces two digit character for the number of the month 01-09, 10-12
    mmm = short month name. Jan, Feb….
    mmmm = long month name. January, February…

    In this example (ddd d) assume the cell contained 1/28/2019, the cell would display:

    Mon 28

    adding the force line code “Alt 0010” and then entering mmmm, the cell would have

    Mon 28
    January

Comments are closed.

Exit mobile version

WHY NOT SUBSCRIBE TO OUR NEWSLETTER?

Get great content like this delivered to your inbox!

It's free, convenient, and delivered right to your inbox! We do not spam and we will not share your address. Period!