Create and apply a custom number format (2024)

If a built-in number format does not meet your needs, you can create a new number format that is based on an existing number format and add it to the list of custom number formats. For example, if you're creating a spreadsheet that contains customer information, you can create a number format for telephone numbers. You can apply the custom number format to a string of numbers in a cell to format them as a telephone number.

Important:Custom number formats affect only the way a number is displayed and do not affect the underlying value of the number. Custom number formats are stored in the active workbook and are not available to new workbooks that you open.

Create a custom number format

1. On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list .

2. In the Format Cells dialog box, under Category, click Custom.

3. In the Type list, select the built-in format that most resembles the one that you want to create. For example, 0.00.

   The number format that you select appears in the Type box.

4. In the Type box, modify the number format codes to create the exact format that you want. For example, 000-000-0000.

   Your changes will not alter the built-in format. Instead, your changes create a new custom number format.

5. When you have finished, click OK.

Apply a custom number format

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

2. On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list .

3. In the Format Cells dialog box, under Category, click Custom.

4. At the bottom of the Type list, select the built-in format that you just created. For example, 000-000-0000.

   The number format that you select appears in the Type box.

   See Also

   Edit & format a spreadsheet - AndroidFormat numbers in a spreadsheet - AndroidFix text-formatted numbers by applying a number formatFind or replace text and numbers on a worksheet

5. Click OK.

Delete a custom number format

1. On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list .

2. In the Format Cells dialog box, under Category, click Custom.

3. In the Type list, select the custom number format, and then click Delete.

   Notes:

   • Built-in number formats cannot be deleted.

   • Any cells in the workbook that were formatted with the deleted custom format will be displayed in the default General format.

See also

Number format codes

Display dates, times, currency, fractions, or percentages

Highlight patterns and trends with conditional formatting

Display or hide zero values

Display numbers as postal codes, Social Security numbers, or phone numbers