Categories
Subscribers Excel 2016

Subscribers: How To Create 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.

format-example

Most of this can be done by 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.

format-cells

  1. Delete whatever is in the Type box.
  2. Key in ddd d.
  3. Depress the Alt key while you key in0010 on your numeric keypad. This will enter the line feed character, and it looks like the portion of the format you keyed in 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.

alignment

 

  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.