Numbers, text and dates

Type 007 into a cell and both Excel and Google Sheets store the number 7. The two zeros are gone unless you start with an apostrophe.

What a cell can hold

When you type into a cell and press Enter, the spreadsheet decides what kind of value you typed. There are four kinds you meet every day. A number is something it can calculate with, such as 42, 3.5 or 12%. Text is everything else, such as a name or a product code. A logical value is TRUE or FALSE. An error value, such as #DIV/0!, is what a formula produces when it cannot give an answer. A cell can also be empty, which is different from a cell that holds 0.

You can usually tell the kind by looking. With the standard settings, numbers line up against the right edge of the cell and text lines up on the left, so a column of prices with one value stuck on the left is a warning sign: that value was stored as text and will be skipped by SUM. In the lab below, the panel names the kind of every cell you select, which saves guessing.

Leading zeros and the apostrophe

Some things look like numbers but are really labels: ZIP codes such as 02134, employee numbers such as 007, phone numbers that start with 0. Type them plainly and the spreadsheet turns them into numbers and drops the leading zeros. To keep them, type an apostrophe first: '02134. The apostrophe tells the program to store text, and it is not shown in the cell. Long identifiers are a second reason to use text. Microsoft gives Excel a number precision of 15 digits, so a 16-digit card number stored as a number loses its last digit.

Percentages work the other way. Typing 12% stores the number 0.12 and shows it as 12%, so =A5*100 gives 12. Text that looks like a number is not hopeless either: Microsoft's page on operators shows that ="1"+"2" gives 3, because the plus sign makes Excel convert text it can read as a number. Functions such as SUM are stricter and skip text in a range, which is why a column of numbers stored as text can quietly add up to 0.

Dates are numbers in disguise

A date in a spreadsheet is stored as a serial number: a count of days. Microsoft's page on date systems gives the example that July 5, 2011 is stored as 40729 in Excel's 1900 date system, the default in current Excel for Windows and Mac. Older Mac versions used a 1904 system, where the same day is 39267, exactly 1,462 days less. Google's DATE help says Sheets counts the days since December 30, 1899, which gives the same 40729 for that day. Because dates are numbers, subtracting two dates gives the days between them.