Ms Office Tune Up

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.

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. When 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 may have a value like a social security number or a credit card number that’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, 2007which is what it wants to do).

In all these cases, the solution’s simple. Before you type the cell value, start by typing an apostrophe (’). The apostrophe tells Excel to treat the cell content as text. Figure 2-3 shows you how it works.

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). This worksheet shows the result of typing 1-2-3, both with and without the initial apostrophe. When you store 1-2-3 as text, Excel left-aligns it, as if it were an all-text cell (and puts a tiny green triangle in the corner of the cell to let you know you may have made a mistake). The date, on the other hand, is right-aligned.

When you precede a numeric value with an apostrophe, Excel checks out the cell to see what’s going on. When 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, as shown in Figure 2-4.

Figure 2-4. In this worksheet, the number 42 is stored 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. Excel provides a similar menu if you enter a text date that has a two-digit year, as in ‘1-1-07. In this case, the menu allows you to convert the two-digit date to a four-digit date that has a year starting with 19 or 20.

Popularity: 10% [?]

Pages: 1 2