Custom Formats for Spreadsheet Numbers

Key to Instructions
To help readers follow the instructions in this article, we use two different typefaces.
Boldface type identifies the names of icons, agendas, URLs and application commands.
Sans serif type indicates instructions and commands that users should type and file names.

Q. I want to format my spreadsheets to show negative numbers in two different ways: in red when I view them on the screen and, because I don’t use a color printer, in parentheses when I print them. How do I do this?

A. Excel provides a wide assortment of formatting possibilities for numbers. I’ll show you how they work so you can set up any combination you wish.

First, highlight the cells containing the numbers you want to format and then go to Format (right-click and then select Format Cells ), and you will get this screen (see right).

The Category box contains a wide assortment of formatting choices. If none of them suits your needs, click on Custom at the bottom of the list. That will give you many possibilities—plus the option of creating your own format.

Here’s how to create custom formats: Under the Type box, scroll down the list of choices. Notice that some lines have several formats, each separated by a semicolon.

If you click on such a line, as shown above, the entire line appears at the top of the Type box—semicolon and all. In the example above, that combination of code tells Excel to put the comma in for thousands (1,234); it also sets negative numbers in both red and with parentheses.

You can change the code in many ways—adding colors and conditionals. Whatever you enter automatically is added to the Custom category and can be used anywhere within the workbook.

For some reason—or maybe for no good reason at all—Excel hides a good tutorial on customizing numbers. To find it, go to Help (F1), click on the Index tab and under Type keywords , enter format . Then scroll down the drop-down list until you come to Create or delete a custom format number.

In the meantime, here are some frequently used codes:

Category Form Value Displayed as
Number #,##0.00 .01 0.01
    0.1 0.10
    1234 1,234.00
    –5678 –5,678.00
Percentage 0.00% .01 1.00%
    0.1 10.00%
    1.234 123.40%
    –5.678 –5,67.80%
Currency $#,##0.00_); [Red]($#,##0.00) .01 $0.10
    0.1 $0.10
    1234 $1,234.00
    –5678 ($5,678.00)

Likewise, you can format fractions, too.

Be aware that no matter which format you use to display numbers, you aren’t affecting the accuracy of the number. Internally, Excel maintains all numbers to 15 places. So if a number is displayed using two decimal places, Excel still maintains it internally to 15 decimal places, and it calculates using the internal representation.


CPEOs provide peace of mind around payroll services

The creation of these new IRS-certified service providers for small businesses clarifies some issues around traditional professional employer organizations.


Pronoun practice to help polish your prose

Using pronouns correctly in writing and speech can help you make a good impression. Try our 10-question quiz.