ThisCalculator

Everyday

Format Number Cells in Excel: Other Options

Updated · Standard formula, shown below

This guide explains how to format number cells in Excel using options beyond the basic Number, Currency, and Percentage formats. You’ll learn about Custom formats, Special formats, and how to control the display of dates, times, text, and scientific notation. The answer to the question “Format number cells others?” is yes—Excel offers a wide range of alternative number formats, including Custom, Special, and Text, which give you precise control over how values appear.

Why Go Beyond the Default Number Formats

The default formats in the Home tab—General, Number, Currency, Accounting, Percentage, and Fraction—cover everyday needs. But they have limitations. For example, the Number format always shows a fixed number of decimal places (which can add trailing zeros), and the Currency format always includes a currency symbol. When you need to display a phone number with parentheses, a ZIP code with a leading zero, a fraction that isn’t simplified, or a number with a custom unit like “kg” or “pcs,” you need to use other format options. These alternatives let you control the exact appearance of a cell’s value without changing the underlying number, which is essential for calculations and data integrity.

Accessing the Other Number Format Options

To see all number format categories, select a cell or range, then press Ctrl+1 to open the Format Cells dialog. Go to the Number tab. The left panel lists all categories: General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, Special, and Custom. The categories beyond the basic ones—Special and Custom—are the “others” that give you the most flexibility. You can also access the same dialog by right-clicking a cell and choosing Format Cells, or by clicking the small arrow in the bottom-right corner of the Number group on the Home tab.

Custom Number Formats: The Power User’s Tool

Custom formats are the most versatile. They use a series of codes that tell Excel how to display positive numbers, negative numbers, zeros, and text. The format is written as up to four sections separated by semicolons:

  • Positive format; Negative format; Zero format; Text format

For example, the code 0.0; -0.0; "Zero"; "Text: @" displays positive numbers with one decimal, negative numbers with a minus sign and one decimal, the word “Zero” for zero, and the text “Text: ” followed by any text entered. You can also include colors in brackets, like [Red]0.0 for negative numbers. Common codes include:

  • 0 – displays a digit, with a placeholder for zeros.
  • # – displays a digit, but hides unnecessary zeros.
  • ? – displays a space for a digit, useful for aligning decimals.
  • . – decimal point.
  • , – thousands separator (or scale by thousands if placed after a digit).
  • % – multiplies by 100 and adds a percent sign.
  • E+ – scientific notation.
  • @ – place for text.
  • "text" – literal text to display.

For instance, to show a number with a unit, use 0 "kg". To show a number in thousands with one decimal, use #,##0.0, (note the trailing comma). To display a negative number in parentheses, use 0;(0).

Special Formats: For IDs and Codes

The Special category includes formats designed for common data types. The most useful are:

  • ZIP Code – displays a 5-digit number with leading zeros (e.g., 01234).
  • ZIP Code + 4 – displays a 9-digit code with a hyphen (e.g., 01234-5678).
  • Phone Number – displays a 10-digit number as (123) 456-7890.
  • Social Security Number – displays a 9-digit number as 123-45-6789.

These formats are locale-specific; the list may vary depending on your regional settings. They are useful for data entry and presentation, but note that they only work with numeric values, and the underlying number is still stored as a number.

Text Format: When You Want Everything as Text

The Text format tells Excel to treat the cell content exactly as typed, including numbers. This is useful for data like product codes, part numbers, or any value that you don’t want Excel to interpret as a number (e.g., a leading zero or a long ID). When a cell is formatted as Text, any number you type is stored as a string, not a numeric value. This means you cannot use it in arithmetic calculations unless you convert it back. To convert a text-formatted number to a real number, you can use the VALUE function or multiply by 1. A common issue is that numbers formatted as text may show a green triangle warning, and they don’t behave as expected in formulas like SUM.

How to Choose the Right Format

Choosing the right format depends on what you need to display and how you plan to use the data. Here are some guidelines:

  • If you need a simple decimal number with a fixed number of places, use Number.
  • If you need a currency symbol, use Currency or Accounting (the latter aligns the symbols and decimal points).
  • If you need to show a percentage, use Percentage.
  • If you need to show a fraction exactly (e.g., 1/3), use Fraction.
  • If you need a leading zero or a specific ID format, use Special or Custom.
  • If you need to store numbers as text, use Text.
  • If you need a custom display like a unit or a color, use Custom.

Common Problems and Misconceptions

One common problem is that the format changes the display but not the value. For example, if you format a cell to show 2 decimal places, the underlying value may still have more decimals, and rounding can cause totals to look off. To round the actual value, use the ROUND function. Another issue is that the General format sometimes shows numbers in scientific notation (e.g., 1.23E+08) when the column is too narrow. To fix this, widen the column or apply a different format. A frequent misconception is that the Text format is the same as the General format; it is not—Text forces everything to be treated as a string. Also, when you apply a Custom format with a color, the color only applies to the display, not to the font color of the cell (which is a separate property).

Frequently Asked Questions

How do I create a custom format that shows a number with a unit like “kg”?

Open the Format Cells dialog, go to the Number tab, select Custom, and type 0 "kg" in the Type box. The number will display with the text “kg” after it. Remember to include a space between the number and the unit. You can also use #,##0 "kg" to include a thousands separator.

Why does my ZIP code lose its leading zero?

Excel automatically removes leading zeros because it interprets numbers as numeric values. To keep the leading zero, format the cells as Text before entering the ZIP code, or use the Special format and select “ZIP Code.” The Special format will display the leading zero, but the underlying value is still a number (so it may not work if you need to keep the zero as a character).

What is the difference between the Number and Custom formats?

The Number format is a predefined set of options (decimal places, thousands separator, negative number display) that you can adjust. The Custom format is a blank slate where you write your own format codes. Custom formats can do everything the Number format does, plus more (like adding text, colors, or conditional sections).

How do I display a negative number in parentheses?

In the Format Cells dialog, under Number, select the Number category and choose the option that shows parentheses for negative numbers (the fourth sample). Alternatively, use a Custom format: 0;(0). This will display positive numbers as usual, and negative numbers in parentheses.

Can I use a custom format to show a number as a fraction?

Yes, but it’s easier to use the Fraction category, which offers several preset fractions (e.g., up to one digit, two digits, etc.). If you need a custom fraction like “1/16”, use a Custom format with the code # ?/16 to display the number as a fraction with a denominator of 16.

Why do my numbers show as #####?

The ##### symbols indicate that the column is too narrow to display the formatted number. Widen the column by dragging the right edge of the column header, or double-click the edge to auto-fit. This is not a format error; it’s just a display issue.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.