A Technique to Quickly Develop Custom Number Formats

From Chandoo.org

A Technique to Quickly Develop Custom Number Formats:

In the past Chandoo has written about custom Number Formats for cells:

http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/

http://chandoo.org/wp/tag/custom-cell-formatting/

and I have written about Custom Number Formats for Charts:

http://chandoo.org/wp/2011/08/19/selective-chart-axis-formating/

http://chandoo.org/wp/2011/08/22/custom-chart-axis-formating-part-2/

This post examines a technique for quickly developing Custom Number Formats for Cells, Charts or any other Number location in Excel.

A Technique for Quickly Developing Custom Number Formats

Instead of Selecting the cell, chart axis etc, Ctrl 1, Format Cells/Properties, Number Tab, Custom and then entering a Custom Format and Apply, only to find out that the format is incorrect, try this simple technique below.

1. Enter a few Numbers in 3 cells

Enter 3 numbers, a positive, zero and negative which have values you will expect to receive in your model.

2. Add a Custom Format Cell

In D3 I have entered ##,;-(##,);”Zero”

3. Display Numbers using the custom Format

Each Number to a display cell with a simple =Text(B3,$D$3)

Copy down

This will display the 3 numbers using the Custom Format in Cell D3

4. Develop Your Custom Format

Play around with your own Custom Number Formats to your hearts content

5. Use your new format

Once you have completed your new Custom Number Format, copy the cell contents of D3 in this case.

Select your cells/or other Excel Numbers,

Ctrl 1,

Format Cells/Properties,

Number Tab, Custom

Enter the Custom Format and Apply.

6. Extending the Technique

This technique can be extended by adding several more rows with a larger range of values.

The values are all evaluated at the same time

LIMITATIONS

The above technique does not show the effects of the Color Modifiers in the test cells

But I think it is a safe bet that you will understand what the Modifier [Red] will do

There are also reserved characters such as E

So in the above example if I had used Zero instead of “Zero”

It would have displayed Ze1900ro, where the E in Zero is taken as 10^x and x=0 so Excel interprets e as 0 or 1900, a date?

You can avoid this by using the code “Zero” or Z\ero

DOWNLOAD

You can download the worked Example File used above.

NUMBER FORMATS

For more on Number Formats check out the above links or those below:

http://www.ozgrid.com/Excel/excel-custom-number-formats.htm

http://www.ozgrid.com/Excel/CustomFormats.htm

http://peltiertech.com/Excel/NumberFormats.html