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.


Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.