O'Reilly logo

Excel Hacks by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #32. Create Custom Number Formats

Excel comes with built-in number formats, but sometimes you need to use a number format that is not built into Excel. Using the hacks in this section, you can create number formats that you can customize to meet your needs.

Before you try these hacks, it helps if you understand how Excel sees cell formats. Excel sees a cell's format as having the following four sections (from left to right): Positive Numbers, Negative Numbers, Zero Values, and Text Values. Each section is separated by a semicolon (;).

When you create a custom number format, you do not have to specify all four sections. In other words, if you include only two sections, the first section will be used for both positive numbers and zero values, while the second section will be used for negative numbers. If you include only one section, all number types will use that one format. Text is affected by custom formats only when you use all four sections; the text will use the last section.

Warning

Don't interpret the word number to mean custom formats applying to numeric data only. Number formats apply to text as well.

The custom number format shown in Figure 2-18 is Excel's standard currency format, which shows negative currencies in red. We modified it by adding a separate format for zero values and another one for text. If you enter a positive number as a currency value, Excel will format it automatically so that it includes a comma for the thousands separator, followed by two ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required