One of Excel’s most important features is its ability to distinguish between different types of information. A typical worksheet contains both text and numbers. There isn’t a lot 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 numbers like Microsoft Word, for example can’t provide these features.
Most of the time, when you enter information in Excel, 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, 2007), times (like 4:30 p.m.), and combined date and time information (like Oct 3, 2007, 4:30 p.m.). 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, but they’re useful in worksheets that include Visual Basic macro code or that use complex formulas that evaluate conditions.
One useful way to tell how Excel is interpreting your data is to look at cell alignment, as explained in Figure 2-1.
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), as in column A. On the other hand, it always right-aligns numbers and dates, as in columns B and C. And it centers Boolean values, as in column D.
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 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. Other entries include only date information or both date and time information. You assume that when you type in a number, it will appear in the cell exactly the way you typed it. For example, when 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.
These differences may seem like a spreadsheet free-for-all, but don’t despairyou can easily set the formatting of numbers and dates. 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. Figure 2-2 shows you how to find the underlying stored value of a cell.
Figure 2-2. You can see the underlying value that Excel is storing for a cell by selecting the cell and then glancing at the formula bar. In this sheet, you can see that the value $299.99 is actually stored without the dollar currency symbol, which Excel applied only as part of the display format. Similarly, Excel stores the number 2,000 without the comma; it stores the date 1-Jun-07 as 6/1/2007; the time 12:30 p.m. as 12:30:00 PM, and the time 14:00:00 as 2:00:00 PM.
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, when 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), then you need to split this content into two cellsone that contains the number 44 and one that contains the remaining text.
Technorati Tags: add different data type
Popularity: 13% [?]
If you liked this post, would you please buy me a twelve-ounce lattes for only $2
Recent Comments