Formatting Numbers (Details & Quirks)

Last week I introduced you to the basic concepts involved in formatting numbers, and which programs have which pre-set options. This week I’m going to explain what these options are (and aren’t), and discuss the quirks in how they behave.

General/Normal: This option is fairly straightforward: it’s “no formatting”. This might seem like a strange thing to include, but basically it’s how you tell the program to simply get rid of whatever existing number formats you’ve applied.

Decimal Numbers: In the “decimal number” format your number is displayed in what most people consider the “normal” one. 0.5, 1, 1.0, and 1.5 are all decimal numbers, and the number of digits after the period is called the number of decimal places. If you specify a format with fewer decimal places than the number has, it will round the number appropriately in the cell. The formula bar will remain unchanged. If you specify more decimal places than the number actually has, it’ll add the places as zeroes at the end of the number – but again, only in the cell. 5.55 could be shown as 5.550 with 3 decimal places, 5.55 with 2 or unspecified, 5.6 with 1, and 6 with no decimal places.

Specifying decimal places is great if you’ve got a column of numbers and want “9” to visually look like a higher number than “1.85”. Without specifying the number of decimal places the smaller number looks bigger at a glance, because it’s longer. You could instead make them “9” and “2”, or “9.0” and “1.9”, or “9.00” and “1.85”.

No matter how many decimal places you have, if you have a number between zero and one it will automatically put a 0 in front of the decimal whether you put it there or not. So if you enter “.5” it will convert it to “0.5”. This conversion, unlike most others, is actually in the formula itself.

Also, spreadsheets will usually drop trailing zeroes from the formula. That is to say, if you say a cell is equal to 1.0000000 it’ll only actually store 1.

A common option (in the US at least) for this format is whether or not to include commas to mark the thousands. For example, 1000000 vs 1,000,000. Obviously commas take up more horizontal space, but can make it easier to read. Other countries may use a different marker — I saw periods used when I was in Italy a few years back.

Currency, Accounting, & Financial: This format basically just puts the local currency symbol next to your number, and sets it to two decimal places. In the US “1” would appear as “$1.00”. Generally if I’m doing a table with columns of money amounts I’ll only put the currency formatting on the totals and subtotals, and just set the rest to two decimal places. That makes for a cleaner table that’s easier to read, while still clearly conveying that it’s all money.

One common option for this type of format is whether to display negative numbers with a -, or with (), with red text, or with a combination.

Dates & Times: Most spreadsheet programs actually store dates and times as a single number reflecting the number of days since a base date. In Google Docs that base date is December 30th, 1899. Therefore “1” would be December 31, 1899, “2” would be January 1, 1900, and 33 would be February 1. With a minimum of 365 days in a year, that number gets pretty large pretty quickly. Times are stored in the part of the number after the decimal point. Technically “1” is January 1, 1900 right at/after midnight. 1.5 would be January 1 at noon. Theoretically you can figure out what the decimal points should be by converting the desired time into a decimal number, multiplying by 100, and dividing by 24. That’s complicated and tedious, though, so in a week or two I’ll cover the wonderful world of functions for manipulating dates and times.

Some programs can handle zero and negative numbers in dates and times. In Google and Open Office a zero is December 30, 1899. In Excel, however, it’s January 0, 1900. Similarly, Google and Open Office treat -1 as December 29, 1899, but Excel can’t handle negative numbers and will just give you an error.

All three programs let you choose from a wide variety of formats for the date and time, and will also allow you to display both at the same time, though you may have to go looking for that option.

Time formatting options are relatively simple. You can have it set as 24 hours, or with AM/PM, and you can specify seconds (00:00:00).

Dates, however, have a LOT of options. You can can have slashes (11/25/12)or dashes (11-25-12). Days may be natural (1, 2, … 10, 11) or set to always have two digits (01, 02, … 10, 11). Months may also be natural or have two digits, but they can also be spelled out in full, or in 3-letter abbreviation, or in 1-letter abbreviation. Years can be 2 or 4 digits (12 or 2012). And, of course, you can switch around the order of the day, month, and year. Some programs will also let you disply the name of the day (Sunday, Nov 25, 2012).

Percentages: Percentages are a tiny bit tricky because reformatting as a percentage doesn’t just tack a percentage sign on at the end, like you might assume. The spreadsheet will assume it’s converting from a decimal number to a percentage. Therefore 1 would be 100%, and 1% would actually be 0.01. There’s not many formatting options for percentages, other than simply changing the number of decimal places that show.

Fractions: Fractions are generally displayed in the format “3 1/2” or “1/100”. Those would be 3.5 and 0.01, respectively. Whole numbers are separated out and put to the left side, and the remainder is separated into a numerator and a denominator, with a / in between. Excel can handle denominators up to three digits, and also gives you the option to round to the nearest half (1/2), quarter (2/4), eighth (4/8), sixteenth (8/16), tenth (5/10), or hundredth (50/100). Open Office can handle denominators up to 2 digits, which turns 0.01 into 1/99. Gooogle Drive cannot currently handle fractions at all.

Scientific Notation: If you know what scientific notation is, and are comfortable using it, this is going to make you twitch. Spreadsheet programs DO NOT use scientific notation, they just have a format that abbreviates long numbers that happens to look like (and claim to be) scientific notation. In true scientific notation you carry the decimal out to the most significant digit, and you definitely don’t round up or down just to make it fit. However, for spreadsheets scientific notation is really just a way to convey really large or really small numbers in a relatively small space. If you think about it, it’s actually impossible for them to have true scientific notation for a reason I mentioned before: they drop trailing zeroes. Ergo, if your results are accurate to five decimal places, and that last decimal place is a zero, you have no way of keeping that information in the formula. You can fake it by telling the program to give you five decimal places (if you’re using a program that allows that), but it’s no longer in the data itself.

Excel and Open Office can handle exponents up to a little more than 300, (e.g. 5.00E+307), and allow you to specify how many decimal places you want showing. Google Drive can handle exponents up to a little below 300, and will always show 2 decimal places.

Special Excel Formats (Zip, Zip+4, Phone, SSN): These are very basic, with basically no customization options. Type in the appropriate number of digits (5, 9, 7 or 10, and 9 respectively), and you get something that looks like what it’s supposed to be. For example: 12345, 12345-6789, 123-4567 or (123) 456-7890, 123-45-6789. If you put in too few digits, it’ll fill in with 0 at the beginning (e.g. 0001-2345 if you entered a zip in a zip+4 formatted cell). If you put in too many digits, it just tacks them on at the beginning: 123456-7890 or (1234) 567-8910.

Boolean: Boolean is true/false. If a field is zero, it’s false. If it’s any other number, it’s true. Currently Open Office is the only program that lets you format numbers this way through the normal number formatting menu, although the others offer it through auto-formatting, as I’ll discuss next week..

Text/Plain Text: This is a way to tell the program that it’s NOT a number, or a formula, even if it looks like it. For example, if you’re in a program that doesn’t have a format for ISBNs and you want to enter 978-0786415168, you don’t want the program to calculate it as 978 minus 0786415168.

I also find it helpful when pasting text from another source: often the bullets in bulleted lists get turns into minus signs, and the spreadsheet will give me an error saying it can’t find the named reference I’m trying to call.

That’s it! The last thing I want you to remember is that all number formats are exactly that: formats of numbers. The numbers themselves can still be treated as numbers – you can add, subtract, multiply, divide, or whatever else you want to do with them (even ones in Text format, IF they only have numbers and no letters or symbols). There’s obvious reasons why you’d want to add and subtract most of them, especially currency, but this fact can also be useful with other formats.

For example, SSN+333333333 (or some other number) would be a very simple way to encrypt a list of social security numbers in a spreadsheet you’re sending somewhere. It would turn 123-45-6789 into 456-79-0122, and 987-65-4321 into 1320-98-7654. Then strip the formatting and you have 456790122 and 1320987654, which don’t even look like social security numbers since they’re not both nine digits long. Then if you make sure that the column header doesn’t give away the fact that these are social security numbers, you’ve just made a risky information transfer situation safer. Whoever receives the file, if you’ve told them in advance what number you added, will be able to de-code the numbers by subtracting it from the numbers they received. This is, if you’re curious, a simple example of shared key encryption.