Create a custom cell format

You can create your own cell formats for displaying numbers, text, and date and time values. Your custom formats are listed in the Data Format pop-up menu so you can use them again.

Create a custom number format

  1. Select a cell or a range of cells you want the format to apply to.

  2. Click the Cell tab at the top of the sidebar on the right.

    If you don’t see a sidebar on the right, or it doesn’t have a Cell tab, click Format button in the toolbar.

  3. Click the Data Format pop-up menu, then choose Create Custom Format.

  4. Type a name for your format, then click the Type pop-up menu and choose Number.

  5. Do any of the following to specify the format:

    • Type text in the field. The text you type appears in the cell.

    • Drag tokens to the custom format field. A token represents the type of information you will enter in a cell. For example, if you’re entering US zip codes into a custom format, you can use an integer token with five digits.

      You can also drag tokens within the field to rearrange them.

    • Customize a token element. Click the arrow on a token to show formatting options, such as the type of currency symbol or the number of digits to display.

      Click a disclosure triangle on a token to choose formatting options.
    • Add up to three rules to format a cell differently based on specific conditions you define.

      In the example below, all positive numbers you enter are displayed as a phone number with the area code (952). When you type “5555103” in a cell, it’s displayed as “(952) 555-5103.” When you type “0” in a cell, the cell shows “No number on file.”

      Custom number cell format with rules

Create a date and time format

  1. Select a cell or a range of cells.

  2. Click the Cell tab at the top of the sidebar on the right.

    If you don’t see a sidebar on the right, or it doesn’t have a Cell tab, click Format button in the toolbar.

  3. In the Data Format pop-up menu, choose Create Custom Format.

  4. Type a name for your format, click the Type pop-up menu, then choose Date & Time.

  5. Define your custom format by doing any of the following:

    • Type text in the field. The text you type appears in the cell.

    • Drag tokens to the custom format field. A token represents the type of information you will enter in a cell.

      You can also drag tokens within the field to rearrange them.

    • Customize a token element. Click the arrow on a token to show formatting options for the data.

      The example below uses the day of the year token (“5”) and custom text so that when you type “2/25/2014” in a cell, the cell displays “56 days into the year.”

      Custom date and time cell format

Date and time formats are applied to a cell if you enter a value that contains a hyphen (-) or a slash (/), such as “1/4/2014.”

Create a text format

  1. Select a cell or a range of cells.

  2. Click the Cell tab at the top of the sidebar on the right.

    If you don’t see a sidebar on the right, or it doesn’t have a Cell tab, click Format button in the toolbar.

  3. Click the Data Format pop-up menu, then choose Create Custom Format.

  4. Type a name for your format, click the Type pop-up menu, then choose Text.

  5. In the text field, type text you want to appear automatically in each cell that uses this format. The blue “text” token represents the text that you type in the table cell.

In the example below, the text “Notify customer:” appears before whatever you type in a cell. If you type “Need to update address” in the table cell, the cell displays “Notify customer: Need to update address.”

Custom text cell format