WebGuru’s Blog about Digital Life

This tips came from my mailbox as a training company’s newsletter, and I would like to share with you all since I feel it might be useful to you too. Based on the email, it should be working on versions of Excel 97/2000/2002/v. X/2003.

You’ll occasionally find that seemingly simple entries cause problems because Excel tries to interpret numeric values as a date. For instance, say that you have a column of product codes with values like 9-12, 10-22, and 8-2099, or you have data labels that describe the age ranges 1-3, 4-6, 7-9, and 10-12. Excel automatically converts the codes to 12-Sep, 22-Oct, and Aug-99. Likewise, Excel converts the range labels to 3-Jan, 6-Apr, 9-Jul, and 12-Oct.

There are a number of ways to avoid the problem, but the easiest is to take advantage of a simple entry shortcut. Simply precede your data with an apostrophe character (’). Doing so forces Excel to treat the entry as text data. For example, enter ‘8-2099. When you complete the entry, Excel doesn’t display the apostrophe in the cell, although you can still see it in the Formula bar.

Note that Excel aligns the entry along the cell’s left edge. That’s because the entry actually becomes a text value. Because of this, you generally don’t want to use this shortcut simply to left-align numeric values. Any formulas that depend on the numeric values could return errors or incorrect results if you do.