Stop automatically changing numbers to dates
Preformat the cells you want to enter numbers into as Text. This way Excel will not try to change what you enter into dates.
Select the cells that you’ll enter numbers into.
Press Ctrl + 1 (the 1 in the row of numbers above the QWERTY keys) to open Format Cells.
Select Text, and then click OK.
A space before you enter a number. The space remains in the cell after you press Enter. (See Notes)
An apostrophe (‘) before you enter a number, such as ’11-53
or ‘1/47. The apostrophe isn’t displayed in the cell after you press
A zero and a space before you enter a fraction such as 1/2 or
3/4 so that they don’t change to 2-Jan or 4-Mar, for example. Type 0
1/2 or 0 3/4. The zero doesn’t remain in the cell after you press Enter,
and the cell becomes the Fraction number type.
We recommend using an apostrophe instead of a space for
entering data if you plan on using lookup functions against the data.
Functions like MATCH or VLOOKUP overlook the apostrophe when calculating
If a number is left-aligned in a cell that usually means it isn’t formatted as a number.
If you type a number with an “e” in it, such as 1e9, it
will automatically result in a scientific number: 1.00E+09. If you
don’t want a scientific number, enter an apostrophe before the number:
Depending on the number entered, you may see a small green triangle in the upper left corner of the cell, indicating that a number is stored as text, which to Excel is an error. Either ignore the triangle, or click on it. A box will appear to the left. Click the box, and then select Ignore Error, which will make the triangle go away.
- We recommend using an apostrophe instead of a space for entering data if you plan on using lookup functions against the data. Functions like MATCH or VLOOKUP overlook the apostrophe when calculating the results.