Custom Formats for Spreadsheet Numbers

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:

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.