Microsoft Office Tutorials and References

In Depth Information

**Adding Different Types of Data**

Adding Different

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.

Figure 14-30:

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.