Microsoft Office Tutorials and References
In Depth Information
number; negative number; zero; text. Figure 9-43 shows how three different formats are
constructed using codes.
Figure 9-43 You can create your own four-part formats.
You’ll find the FormattingNumbers.xlsx file with the other examples on the companion
website. It contains many of the custom formatting code examples described in this section.
Among the built-in formats, only the Accounting formats use all four parts, as shown in
Figure 9-43, which breaks down each part of the third Accounting format in Table 9-2. The
following are some guidelines for creating multipart formats:
If your custom format includes only one part, Excel applies that format to positive,
negative, and zero values.
● If your custom format includes two parts, the first part applies to positive and zero
values, and the second part applies only to negative values.
If your custom format has three parts, the third part controls the display of zero
The fourth and last element in a four-way format controls text-value formatting. Any
formats with three or fewer elements have no effect on text entries.
If you prefer, you can suppress the display of all zero values in a worksheet, including
the displayed values of formulas with a zero result. Click the File tab, Options, and then
click the Advanced category. In the Display Options For This Worksheet area, clear the
Show A Zero In Cells That Have Zero Value check box.
Adding color to formats You can also use the Number formats to change the color of
selected cell entries. For example, you might use color to distinguish categories of
information or to make totals stand out. You can even create formats that assign different colors to