2.1. Adding Different Types of Data One of Excel's most important features is its ability to distinguish among different types of information. A typical worksheet contains both text and numbers. There isn't a lot that you can do in Excel with ordinary text (other than alphabetize a list, perform a simple spell check, and apply some basic formatting). On the other hand, Excel gives you a wide range of options for numeric data. For example, you can string your numbers together into complex calculations and formulas, or you can graph them on a chart. Programs that don't try to separate text and numberslike Microsoft Word, for examplecan't provide these features. Most of the time, when you type information into an Excel spreadsheet, you don't explicitly indicate the type of data. Instead, Excel examines the information you've typed in and, based on your formatting and other clues, classifies it automatically. Excel distinguishes between four core data types: Ordinary text. This data type includes column headings, descriptions, and any content that Excel can't identify as one of the other data types. Numbers. This data type includes prices, integers, fractions, percentages, and every other type of numeric data. Numbers are the basic ingredient of most Excel worksheets. Dates and times. This data type includes dates (like Oct 3, 2004), times (like 4:30 PM), and combined date and time information (like Oct 3, 2004, 4:30 PM). You can enter date and time information in a variety of formats. True or false values. This data type (known in geekdom as a Boolean value) can contain one of two things: TRUE or FALSE (displayed in all capitals). You don't need Boolean data types in most worksheets. (The only time you need to use these values is in those ultra-rare cases where you want to add Visual Basic macro code or certain condition-evaluating formulas to your spreadsheet. Because these topics are super-advanced and don't apply to most spreadsheets, this book doesn't cover them. If you're interested, check out Excel: The Missing Manual.)
One useful way to tell how Excel is interpreting your data is to look at cell alignment. As shown in Figure 2-1, unless you explicitly change the alignment, Excel always left-aligns text (that is, it lines it up against the left edge of a cell). On the other hand, it always right-aligns numbers and dates, and it centers Boolean values.
Note: The standard alignment of text and numbers doesn't just represent the whims of Excelit also matches the behavior you want most of the time. For example, when you type in text, you usually want to start at the left edge so that subsequent entries in a column line up. But when entering numbers, you usually want them to line up on the decimal point so that it's easier to scan a list of numbers and quickly spot small and large values. Of course, if you don't like Excel's standard formatting, you're free to change it, as you'll see in Chapter 4.
As Figure 2-1 shows, Excel can display numbers and dates in several different ways. For example, some of the numbers include decimal places, one uses a comma, and one has a currency symbol. Similarly, one of the time values uses the 12-hour clock while another uses the 24-hour clock, and other entries include only date information or both date and time information. You might assume that when you type in a number, it appears in the cell exactly the way you typed it. For example, if you type 3-comma-0-0-0 you expect to see 3,000. However, that doesn't always happen. To see the problem in action, try typing 3,000 in a cell. It shows up exactly the way you entered it. Then, type over that value with 2000. The new number appears as 2,000. Excel remembers your first entry and assumes that you want to use thousand separators in this cell all the time. Figure 2-1. Excel left-aligns text, as in column A.; right-aligns numbers and dates, as in columns B and C; and centers Boolean values, as in column D. You can change the default alignment by clicking in a cell and then choosing Format  Cells Alignment. 
These differences may seem like a spreadsheet free-for-all, but don't despair you can easily set the formatting of numbers and dates. (In fact, that's the subject of Chapter 4.) At this point, all you need to know is that the values Excel stores in each cell don't need to match exactly the values that it displays in each cell. For example, the number 4300 could be formatted as plain old 4300 or as the dollar amount $4,300. Excel lets you format your numbers so you have exactly the representation you want. At the same time, Excel treats all numbers equivalently, no matter how they're formatted, which lets you combine them together in calculations. For example, in Figure 2-2, by selecting the B6 cell and then glancing at the Formula bar, you can see that Excel actually stores the value $299.99 without the dollar currency symbol, which it applies only as part of the display format. Similarly, Excel stores the number 2,000 without the comma; it stores the date 1-Jun-03 as 6/1/2003; the time 12:30 PM as 12:30:00 PM and the time 14:00:00 as 2:00:00 PM. Figure 2-2. You can see the underlying value that Excel is storing for a cell by selecting the cell (in this case, B6) and then looking at the Formula bar, which is just above the grid. 
Note: Excel assigns data types to each cell in your worksheet, and you can't mix more than one data type in the same cell. For example, if you type in 44 fat cats, Excel interprets the whole thing as text because it contains letters. If you want to treat 44 as a number (so that you can perform calculations with it, say), you need to split this content into two cellsone that contains the number 44 and one that contains the remaining text.
2.1.1. Controlling Your Data Types By looking at cell alignment, you can easily tell how Excel is interpreting your data. That's helpful. But what happens when Excel's interpretation is at odds with your wishes? For example, what if you type in something you consider a number but Excel freakishly treats it as text? The first step to solving this problem is grasping the logic behind Excel's automatic decision-making process. 2.1.1.1. How Excel decides your data is text If your cell meets any of the following criteria, Excel automatically treats the content as ordinary text: It contains any letters. Thus, C123 is text, not a number. It contains any punctuation that Excel can't interpret numerically. Punctuation allowed in numbers and dates includes the comma (,), the decimal point (.), and the forward slash (/) or dash (-) for dates. If you type in any other punctuation, Excel treats the cell as text. Thus, 14! is text, not a number.
Occasionally, Excel reads your data the wrong way. For example, you might have a valuelike a social security number or a credit card numberthat's made up entirely of numeric characters but that you want to treat like text because you don't ever want to perform calculations with it. But Excel doesn't know what you're up to, and so it automatically treats the value as a number. You can also run into problems when you precede text with the equal sign (which tells Excel that you have a formula in progress) or when you use a series of numbers and dashes that you don't intend to be part of a date (for example, you want to enter 1-2-3 but you don't want Excel to read it as January 2, 2003which is what it wants to do). In all these cases, the solution is simple. Before you type the cell value, start by typing an apostrophe ('). The apostrophe tells Excel to treat the cell content as text. For example, Figure 2-3 shows the result of typing 1-2-3, both with and without the initial apostrophe. When you store 1-2-3 as text, as in cell B3, Excel left- aligns it, as if it were any all-text cell. On the other hand, in cell B2, Excel interprets 1-2-3 (typed without the initial apostrophe) as a date, and so right-aligns it. Figure 2-3. To have Excel treat any number, date, or time as text, just precede the value with an apostrophe (you can see the apostrophe in the Formula bar, but not in the cell). 
When you precede a numeric value with an apostrophe, Excel checks out the cell to see what's going on. If Excel determines that it can represent the content as a number, it places a green triangle in the top left corner of the cell and gives you a few options for dealing with the cell. Here's how it works. In Figure 2-4, Excel stores the number 42 as text, thanks to the apostrophe that precedes it. Excel notices the apostrophe, wonders if it's an unintentional error, and flags the cell by putting a tiny green triangle in the top left corner. If you move to the cell, an exclamation mark icon appears, and, if you click that, a menu appears, letting you choose to convert the number or ignore the issue for this cell.
Note: Excel provides a similar menu if you type in a text date that has a two-digit year, as in '1-1-04. In this case, the menu lets you convert the two-digit date to a four-digit date that has a year starting with 19 or 20.
Figure 2-4. In this worksheet, Excel stores the number 42 as text, thanks to the apostrophe that precedes it. If that's not what you want, Excel gives you the opportunity to change it: just move to the cell and click the exclamation point icon to display a menu of options, including Convert to Number. 
Tip: If you type in either false or true (using any capitalization you like), Excel automatically recognizes the data type as Boolean instead of text, converts it to the uppercase word FALSE or TRUE, and centers it in the cell. If you want to make a cell that contains false or true as text and not as Boolean data, start by typing an apostrophe (') at the beginning of the cell.
2.1.1.2. How Excel decides your data is numeric Excel automatically interprets any cell that only contains numeric characters as a number. In addition, you can add the following non-numeric characters to a number without causing a problem: One decimal point (but not two). For example, 42.1 is a number, but 42.1.1 is text. One or more commas, provided you use them to separate groups of three numbers (like thousands, millions, and so on). Thus 1,200,200 is a valid number, but 1,200,20 is text. A currency sign ($ for U.S. dollars), provided it is at the beginning of the number. A percent symbol at the beginning or end of the number (but not both). A plus (+) or minus (-) sign before the number. You can also create a negative number by putting it in parentheses. In other words, entering (33) is the same as entering -33. An equal sign at the start of the cell.
The most important thing to understand about entering numbers is that when you choose to add other details like commas or the dollar sign, you're actually doing two things at once: you're entering a value for the cell and you're setting the format for the cell, which affects how Excel displays the cell. Chapter 4 provides more information about number styles and shows how you can completely control cell formatting. 2.1.1.3. How Excel decides your data is a date or time When typing in a date, you have a choice of formats. You can type in a full date (like July 4, 2004), or you can type in an abbreviated date using dashes or slashes (like 7-4-2004 or 7/4/2004), which is generally easier. If you enter some numbers formatted as a date, but the date you entered doesn't exist (like the 30th day in February or the 13th month), Excel interprets it as text, meaning it aligns the bogus date on the left. Figure 2-5 shows you the options you have for typing in dates. Figure 2-5. Whichever way you type in the date in a cell, it always appears the same on the Formula bar. 
Note: The way the Formula bar displays dates depends on the regional settings on your computer (see the "Up to Speed: Regional Dating" box on Section 2.2.1). To fine-tune the way the date appears in your worksheet, you can use the formatting features covered on Section 4.1.1.8.
Because you can represent dates a few different ways, working with them is tricky, and you're likely to encounter some unexpected behavior from Excel. Here are some tips for using dates, trouble-free: Instead of using a number for the month, you can use a three-letter month abbreviation, but you must put the month in the middle. In other words, you can use 7/4/2004 and 4/Jul/2004 interchangeably. When you use a two-digit year as part of a date, Excel tries to guess whether the first two digits of the year should be 20 or 19. If the two-digit year is from 00 to 29, Excel assumes it belongs to the 21st century and displays it that way. If the year is from 30 to 99, Excel plants it in the 1900s. In other words, Excel translates 7/4/29 into 7/4/2029, while 7/4/30 becomes 7/4/1930.
Tip: If you're a mere mortal and you forget where the cutoff point is, just enter the year as a four-digit number, which prevents any confusion.
If you don't type in any year at all, Excel automatically assumes you mean the current year. For example, if you enter 7/4, Excel inserts the date 7/4/2005 (assuming it's currently 2005 on your computer's internal clock). When you enter a date this way, the year component doesn't show up in the cell, but it's still stored in the worksheet (and visible on the Formula bar). Excel understands and displays dates differently depending on the regional settings on your computer. Windows has a setting that determines how your computer interprets dates (see the box on Section 2.2.1). On the U.S. system, Month-Day-Year is the standard progression. But on the UK system, Day-Month-Year is the deal. For example, in the U.S., either 11-7-04 or 11/7/04 is shorthand for November 7, 2004. In the UK, the same notations refer to July 11, 2004. Thus, if your computer has U.S. regional settings turned on and you type in 11/7/04, Excel understands it as November 7, 2004, and the Formula bar displays 11/7/04.
Note: The way Excel recognizes and displays dates varies according to the regional settings on your computer, but the way Excel stores dates does not. This feature comes in handy when you save a worksheet on one computer and then open it on another computer with different regional settings. Because Excel stores every date the same way, the date information remains accurate on the new computer, and Excel can display it according to the new regional settings.
Typing in times is more straightforward than typing in dates. You simply use numbers, separated by a colon (:). You need to include an hour and minute component at minimum (as in 7:30), but you can also add seconds, milliseconds, and more (as in 7:30:10:10). You can use values from 1 to 24 for the hour part, though if your system is set to use a 12-hour clock, Excel converts the time accordingly (in other words, 19:30 becomes 7:30 PM.) If you want to use the 12-hour clock when you type in a time, just follow your time with a space and then the letters P or PM (or A or AM). Finally, you can create cells that have both date and time information. To do so, just type the date portion first, followed by a space, and then the time portion. For example, Excel happily accepts this combo: 7/4/2004 1:30 PM. Behind the scenes, Excel stores dates as numbers. It considers the date January 1, 1900 to be day 1. January 2, 1900 is day 2, and so on, up through the year 9999. This system is quite nifty, because if you use Excel to subtract one date from another, you actually end up calculating the difference in days, which is exactly what you want. On the other hand, it means you can't enter a date in Excel that's earlier than January 1, 1900 (if you do, Excel treats your date like text). Similarly, Excel stores times as fractional numbers from 0 to 1. The number 0 represents 12:00 AM (the start of the day) and 0.999 represents 11:59:59 PM (the end of the day). As with dates, this system lets you subtract one time value from another. |
