Excel cell formatting

Question

I want to insert numbers in an Excel cell (ex. 01-00), but the formatting wants it to default to a date (ex. Jan-1). How do I delete this formating so I can enter simple numbers?

Answer 1

There are two possible ways:

1) format your cells as text before input

2) use an apostrophe to precede the entry, e.g. enter '01-00 - the apostrophe tells excel that the entry is to be treated as text - it isn't displayed

Answer 2

I don't believe there's an option to prevent Excel from auto-formatting dates, but here are a few workarounds you can try before entering data:

Changing the Normal Style (only for Excel 2007 and above)

Do this before entering any values or data into your spreadsheet.

1. Under the Home Tab, go to the Styles section and right-click the Normal style.
2. Click Modify and then Format.
3. Select Text under the Number tab, or use the custom number format mentioned below. Click Ok.

This way, you don't have to reformat cells/worksheets that you might add.

---

Custom Number Format ("00-00")

To set it up:

1. Select the cells you'd like to format.
2. Press Ctrl + 1.
3. Go to Number > Custom.
4. Enter 00-00 into the Type field.

After setting up the number format, you just have to type the numbers without the dash (i.e. 0100, 0213) and Excel will automatically format it to show 01-00 or 02-13.

Note that Excel will treat the values as they are entered.