Microsoft Office Tutorials and References
In Depth Information
Adding Different Types of Data
Types of Data
In this example, 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 despair—you can
easily set the formatting of numbers and dates. (In fact, that’s the subject of Chapter
16.) 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 14-30 shows you how
to find the underlying stored value of a cell.
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-10
as 6/1/2010; 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 cells—one that contains the number 44 and one that
contains the remaining text.
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 or vice versa? The first step to solving this problem is
grasping the logic behind Excel’s automatic decision-making process.