Formatting Those Figures

Excel is all about numbers, so it comes as no surprise that you can format a number in Excel in about a million different ways. Well, maybe not a million, but you can get pretty creative. Believe it or not, though, you only need to use one property of the Range object to have complete control of the various ways you can format a number. That property is the NumberFormat property. Can you guess which tab of the Format Cells dialog box provides functionality similar to the NumberFormat property? The answer is shown in Figure 10.9.

Figure 10.9

Use the Number-Format property to format a range much as you would using the Number tab of the Format Cells dialog box.

Figure 10.9

Use the Number-Format property to format a range much as you would using the Number tab of the Format Cells dialog box.

If you understand how to create a custom format code in Excel, you won't have a problem transferring your knowledge here. You can simply assign the format code to the NumberFormat property to achieve the same result programmatically. A basic understanding of number format codes is definitely in order before you use the NumberFormat property. Number format codes are composed of a combination of special characters. Table 10.5 lists the most commonly used characters.

Table 10.5: Special Characters for Creating Number Format Codes

Character use

# A placeholder for significant digits only—doesn't display insignificant zeroes.

0 A placeholder for all digits. If a number doesn't have enough significant digits to fill the placeholder, a zero is displayed.

? A placeholder that adds space for, but doesn't display, insignificant digits. You use this to line up decimal points when you have a column of numbers that deal with differing numbers of significant digits.

. (decimal point) Placeholder used to indicate how many digits should be displayed before and after the decimal point.

, (comma) Placeholder used to indicate whether the thousands separator should be displayed or not. Also used to scale numbers (without changing the fundamental value of the underlying number).

_ (underscore) Used to create space in a number format. For example, if you format negative numbers in parentheses you need to add the space equal to the width of the closing parenthesis to positive numbers in order to have the numbers line up.

Table 10.5 is not an exhaustive list; rather it contains enough to get you on your way. I'd encourage you to look in Excel's help files for more information regarding number format codes because they afford a ton of flexibility.

Figure 10.10

Number format code examples

Figure 10.10

Number format code examples

ET MjcrosoO 11.1. th. ■ ZG03 E3eU - Uidylei 10 EXâmples.Klï

BE®

Fils Edt View Insert Fermât Teels Dat-j Wlndcw He^

Type a question ior heb .

..Sx

! £

JJul V : - - |~B 1 B s a

m % • S% 1 ^ p

A- Ê

i -J

ti -Li i i> -I -«' X SLAl fljj wihOsroes

I' ' iWf-M: W

A3 « S

A

B

c

D

E TT

1

Number Format Code Examples

2

The actual mirnhei ¡11 all cells is 1234.4321.

3

a

Format '

P o< ri. M 'J : ; . N nihil er

7 -r-0

5

D

1234

-1234

0

s

I334

-1,234

0

7

1,234

(1,234)

0

"M

3

*,ÄS0J;fRed](#.#W)

1.234

1,234)

0

9

#.#SOJ;|Reijp,#*a); "-"

1,234

(1,234)

10

???.???

1234.432

-1234.432

11

1,234.43

(1,234.43)

D.OO

12

1 -1

13

M

V

M 1

» Hß. Number Formatai Scallno / Baac Chart / |<

■_

> j 1

I -

__J

NOTE Check out the Excel help topic "Create or delete a custom number format" for more information regarding custom number format codes.

On more than one occasion, I've seen people go through an obscene amount of effort to display data a certain way that could have been easily achieved using a custom number format.

A format code can have up to four sections each separated by a semicolon (;): the first section specifies how positive numbers are formatted; the second specifies how negative numbers are formatted; the third specifies how zero is formatted; and the last details how nonnumeric values should be formatted. Figure 10.10 shows some examples of various format codes.

In order to experiment with format codes, why not put together a little procedure that attempts to apply a number format code to a range of numbers as you enter various format codes? Check out Listing 10.7. If you're going to try it out, you need to enter the code into the module associated with a Worksheet (so that you can catch the Worksheet Change event). Also, the worksheet should have two named ranges. "FormatCode" is the cell containing the number format code that you want to apply to the range named 'TestFormatCode". Figure 10.11 depicts the worksheet I used for this example.

0 0

Post a comment