Create a custom number format

1 year ago

4 minutes

In Excel, you can create custom number formats to change how numbers are displayed in cells without changing the actual values. Custom number formats are useful when you need a specific way to present data, such as adding special symbols, formatting dates, or controlling how negative numbers are displayed.

Structure of a Custom Number Format

Custom number formats in Excel are made up of up to four sections, separated by semicolons (;). The sections control how positive numbers, negative numbers, zeros, and text are displayed.

Basic Custom Number Format Structure:

[Positive Format];[Negative Format];[Zero Format];[Text Format]

Positive Format: How positive numbers will appear.
Negative Format: How negative numbers will appear.
Zero Format: How zero values will appear.
Text Format: How text values will appear.

Each section is optional, and if a section is omitted, Excel uses the format from the previous section. You can customize each section to meet your needs.

Steps to Create a Custom Number Format:

Select the Cells to Format:
- Highlight the range of cells where you want to apply the custom number format.
Open the Format Cells Dialog Box:
- Right-click on the selected cells and choose Format Cells.
- Or, press Ctrl + 1 (Windows) or Cmd + 1 (Mac) to open the Format Cells dialog box.
Go to the Custom Category:
- In the Format Cells dialog box, click on the Number tab.
- In the list on the left, select Custom.
Enter the Custom Number Format:
- In the Type box, enter your custom format code.
- For example, you can use a format code like #,##0.00 for displaying numbers with thousands separators and two decimal places.
Click OK:
- Once you’ve entered the custom format, click OK to apply it.

Examples of Custom Number Formats:

1. Displaying Numbers with Thousands Separators

To format a number with a thousands separator and no decimals:

#,##0

Example: 1000 becomes 1,000.

2. Displaying Numbers with Two Decimal Places

To display a number with exactly two decimal places:

0.00

Example: 5 becomes 5.00, and 5.678 becomes 5.68.

3. Adding Currency Symbols

To display numbers with a currency symbol (e.g., $ for USD):

$#,##0.00

Example: 1000 becomes $1,000.00.

4. Formatting Negative Numbers

To display negative numbers in red or with a minus sign:

#,##0;[Red]-#,##0

Positive numbers will be shown normally (e.g., 1,000), while negative numbers will appear in red with a minus sign (e.g., -1,000).

5. Showing Zeros as Blank

To hide zeros and display them as blanks:

0;[Red]0;"";@

This format hides zeros and leaves the cell blank (empty).

6. Custom Date Format

To display dates in a specific format:

mm/dd/yyyy

Example: 2025-01-12 becomes 01/12/2025.

7. Adding Text to Numbers

You can add text labels to numbers:

0 "items"

Example: 5 becomes 5 items.

8. Using Color in Number Formats

You can change the color of positive, negative, and zero values:

[Green]0;[Red]-0;[Blue]0;[Yellow]"Text"

Positive numbers will appear in green, negative numbers in red, zeros in blue, and text will appear in yellow.

Special Symbols in Custom Number Formats:

#: Placeholder for digits. It does not display anything if the number doesn’t require it.
0: Placeholder for digits. It displays zero when the number has fewer digits than required.
, (Comma): Thousands separator.
. (Period): Decimal point.
$: Currency symbol.
%: Percentage format.
E+00: Scientific notation.

Custom Format Examples for Specific Needs:

Phone Numbers: Format a 10-digit phone number as (###) ###-####:
```
(###) ###-####
```
Social Security Number (SSN): Display an SSN as XXX-XX-XXXX:
```
###-##-####
```
Percentage Format: Show numbers as percentages with two decimal places:
```
0.00%
```

Conclusion

Creating a custom number format in Excel gives you flexibility in how you present data. By using different format codes, you can display numbers with specific symbols, control decimal places, hide zeros, and more. This can help make your worksheets look more professional and tailored to your needs.

Post Views: 24