Back when I was first talking about how to do text formatting, I said you could do it all from a single menu/window. That’s still true of number formatting, but unlike any other kind of formatting, number formatting can be done by typing in the cell.
To invoke automatic number formatting, two things need to be true. First, there must not be an existing number format on the cell. Second, the number you’re entering must look like a valid number format to the program.
Once these two conditions are met, however, automatic number formatting is very powerful. Sometimes numbers in a spreadsheet aren’t actually numbers. They might look like numbers to you, but to the spreadsheet they’re just a bunch of characters that happen to be numerical digits. For example, if you see 01 in a spreadsheet then it’s probably been stored as text, not as a number, because there aren’t any number formats which display leading zeroes.
However, if the text looks like a valid numeric format to the program, then when you enter it the automatic formatting will kick in, and convert it to a number. This is by far the easiest way to convert text to numbers. By “enter them” I mean typing them in without any extra characters, importing from a text file, or pasting them from a non-spreadsheet. If you paste numeric text from a spreadsheet to a spreadsheet, it will “remember” that it’s supposed to be text, and won’t convert it. You can get around that by pasting to some other program such as a word processor (notepad is good enough for this purpose), then copying the text from the other program and pasting it back into the spreadsheet. That’s not worth it if you’ve got just a couple of numbers to convert, but if you have a whole table with dozens of columns and rows, then it could save you some headaches.
As a note, decimal numbers and scientific notation are the only number formats you can use in formulas. Furthermore, a decimal number with too many decimal places may, depending on the program, automatically convert to scientific notation, and scientific notation with a small enough exponent may convert automatically to a decimal number.
So the question you might be wondering now is: What will a spreadsheet recognize as a valid number format, and what will each format convert to? Glad you asked. Here’s some examples. The “valid” and “invalid” examples may vary from program to program, but should be mostly consistent.
As a general rule, if you type in a decimal number then the spreadsheet will assume that you want it formatted to display as many decimal places as you entered, excluding any trailing zeroes. (A decimal number in this case is anything that has exactly one period which at least one digit to the right of it, and no spaces or other non-number characters. It may or may not have one or more digits to the left of the period.)
Valid: Positive: 0.01, .01, 1, +1, 1000, 1,000; Negative: -1, – 1, (1), ( 1 )
Invalid: 0.0.1, Section 1.1, 1,00, <space>1
Note 1 (Decimal & Scientific): In Excel and Open Office, if you exceed the number of decimal places that can be displayed in the default-sized cell, the format will automatically shift to scientific notation. In Google, however, it will round it to the closest number of decimal places that can be shown. This means that if you have 0.0000000000000004 or below, it will get rounded to 0. Also, if you use decimal numbers in a formula (e.g. =1+.1) then the results will be automatically formatted
Note 2 (Double negatives): Remember your English teacher griping about double negatives? “I ain’t never done that,” or worse yet, “I ain’t never done nothing wrong.” Well, in spreadsheets you can mark negative numbers with a minus sign -, and you can mark them with parenthesis (). Parenthesis are also used to dictate order of operation: (2+5)*0 = 0, but 2+5*0=2. So if you have -(#) or (-#) there’s really no predicting how a given program will deal with it, without just trying it. The same goes for combining + and -. In Excel and Open Office -(1) = -1, but (-1)=1 in Open Office, and won’t convert in Excel. Conversely, —1, +-1, and -+1 all equal -1 in Excel, but won’t convert to numbers in Open Office.
In the US, if you put a dollar sign ($) in front of your number, then pretty much any program will assume you want it formatted as money. Open Office will assume you want two decimal places, no matter how many you actually entered. Excel won’t give you any decimal places if you didn’t enter any, but will give you exactly two if you entered one or more. Google, on the other hand, will give you exactly as many decimal places as you entered.
Valid: $1, $1.01, $.01,
Invalid: 0.0.1, Section 1.1
Notes: The three programs also treat subsequent/adjacent entries differently. Open Office will assume that any number you entered is still meant to be a money amount, and leave it formatted the same. Excel will also make that assumption and leave the formatting as-is, but this means that if the first number you entered was a whole dollar amount, and there are no decimal places, then even if you enter decimal places it won’t change the formatting to show them. Likewise, it will continue to show two decimal places in a cell that had them, even if you enter a whole number. Lastly, Google will assume that if you don’t put the $ in front of the new entry, you no longer want it to be formatted as currency. (This has the side effect of meaning that if you want the results of a formula formatted as currency, you’ll have to do it manually.)
As a general rule, if you enter something that looks like a date a spreadsheet will interpret it as a date for you. That means it will convert it to the number for that date, and format it in whatever that program’s default format is. If you don’t specify a year, it will assume the current year. If you don’t specify a day it will assume that it’s 1. If you’re in the US then the order is month/day/year, so the first number has to be 12 or less. However, if the second number is higher than the number of days in that month, the spreadsheet will assume you’re doing month/year instead of month/day.
Valid: 12/16, 12-16-2012, Dec 16, December 16, 2012, 12/2012, 16-Dec-2012, 12/32
Invalid: 13/1, 12.16.2012, Dec. 16, Dec 16th, 2012/12/16
Note 1: The downside of this nifty conversion process is that it happens whether you want it to or not, and you can’t just re-format the results to look the way you want them to because 1/2 will now be stored as 40910 (the number for January 1, 2012), not 0.5. You can avoid this by putting = in front of your formula, but if you’ve already automatically formatted as a date then =1/2 will give you December 31, 1899 (day 0), at noon (halfway through the day). If you want it to appear as anything other than a date you’ll have to manually re-format, since automatic number formatting doesn’t override existing number formats.
Note 2: In Open Office: 1/2 will convert to ½, not January 2,. Likewise, 1/4 will convert to ¼, and 3/4 will convert to ¾. This isn’t because fractions are taking precedence over dates. It’s because by default AutoCorrect changes these particular things into a single character representing them. These aren’t even fractions: if you try to add 1 to ½ you’ll get an error. The only way to avoid this is to turn off AutoCorrect for 1/2, 1/4, and 3/4.
Like dates, if you enter something that looks like it should be a time then it will be converted to a time. The format is hour:minute:second, and if you only have two numbers then it’s assumed to be hour:minute with 0 seconds. In some programs you can specify more than 24 hours, and it will store it as if you’d specified days and hours, but display it as time.
Valid: 0:30, 00:30, 00:30:59, 12:30, 23:59, 11:59 PM, 11:59 am, 48:00, 11:60 (but this will convert to 12:00).
Invalid: 11:59pm, 11:59 EST, 11::59
Note: This auto-formatting interferes with typing in ratios.
Since the usual format for a fraction has been co-opted by dates, there’s no way to automatically format fractions. Even if you enter something that cannot possibly be a date, such as 13/1, it won’t become a fraction – it’ll be treated as text. So if you want the result to look like a fraction then you’ll need to do two things. 1) Always make it a formula by starting it with =, and 2) Manually use the menus to change the formatting to fraction.
If you type a number with a % at the end, it will format it as a percentage, but store it as the decimal number that percentage would represent. This means that the number which is stored is actually the number you typed, divided by 100. 1% would be stored as 0.01.
Valid: Positive: 0.01%, .1%, 1%, +1%, 1,000%; Negative: -1%, -<space>1%, (1)%
Invalid: 0.0.1%, 1,00%, <space>1%
Scientific notation is a number, followed by an E, followed by another number. 1.2E3 means 1.2*10^3, or 1200. In fact, because 1200 easily fits in a cell, the result would be formatted as a decimal number, not as scientific notation. It’s only if the results are too long to fit in the cell that they are automatically formatted as scientific notation.
Valid: 1e100, 1.1E100, 1E+100, 1E-100, -1E100
Invalid: 1 E100, 1E 100, 1E100.1
If you put a ‘ in front of what you’re typing, then the spreadsheet will treat the contents as text. The ‘ will be visible in the formula bar, but won’t count as part of the text if you use text manipulation function such as left() and right().
Valid: ‘whatever, =’whatever
Invalid: Anything that doesn’t start with ‘.
As mentioned last week, Boolean is whether something is true or false. It’s only sort of a number, and the only way to format something as Boolean in most programs is to use automatic formatting.
Valid: true, false, TRUE, FALSE
Invalid: t, f, yes, no, 1, 0, true<space>
That’s all the number formatting you can do automatically! I’ll be back next week to discuss some non-automatic ways of converting text to numbers.