Formatting numbers (About)

Back when I was talking about formatting I mentioned that there are ways to format numbers, but I didn’t go into what they were or follow up the way I meant to.

Essentially, number formatting allows you to enter a number, say 1 for example, and have it appear as $1, $1.00, 100%, or January 1, 1890.

Like any other formatting changes, they will appear on your screen and they’ll print out, but they won’t actually change the contents of the cell.  If cell a1 has the date “11/25/2012” in it, then if b1=a1+7, b1 will have 12/2/2012 in it. If you re-formatted a1 as a dollar amount it would be $41,238.00, but b1 would still have 12/2/2012.

One small wrinkle is that text manipulation may or may not be affected by number formatting. Some programs, like Excel, ignore the format of numbers when manipulating them as text. Excel will tell you that $41,238.00, 11/25/2012, 4.1238E+04, and 4123800% all have 5 characters in them because as far as the program is concerned they’re all just different formats of the same number: 41238. Google Drive, on the other hand, thinks that 4.12E+04, $41,238.00, and 4123800% all have 5 characters, but 11/25/2012 has 10 characters in it, and if you add 7 to it you’ll get 12/2/2012, which has only 9.

At minimum, all spreadsheets should have number formats for percentages, currency, dates, times, and scientific notation.  They will also allow you to set how many decimal places you want showing, and whether you want commas or not, and how you want negative numbers to look.  Some programs will also allow you to display fractions, zip codes, Boolean values, etc.

Some of these formats can also be combined.  Currency and percentages will both let you also specify decimal places and/or commas, but for obvious reasons dates and will not.  Scientific notation may or may not, depending on the program.

Here’s a quick little chart showing what formats various programs have pre-defined for you.  If the program calls them something different, I’ve noted that, and otherwise just marked yes or no. 

Format Excel Google Open Office
No formatting General Normal General
Decimal numbers Number

(max 30 places)

Rounded, Two decimals

(max 10)

Number

(max 20)

Money Currency, Accounting Financial, Currency Currency
Date, Time Yes Yes, and Date time Yes
Percentage Yes Percent, Percent rounded Percent
Fraction Yes No Yes
Scientific Yes Yes Yes
Text Yes Plain text Yes
Zip Code, Zip+4 Yes No No
Phone Number Yes No No
Social Security Number Yes No No
Boolean Sort of Sort of Yes

 
Next week I’ll go into more detail about these formats, including how they differ from each other, what their main options are, and any quirks they have that might affect how you use them.