The autoformat and autocorrect features can be very helpful when working with numbers and formulas in Excel, but sometimes they mess up your data by automatically converting numbers to dates. This often happens when you enter fractions or decimals. Here are some ways to stop Excel from automatically formatting as dates.
Prevent Excel from automatically formatting dates using cell formatting
If Excel automatically corrects to a date, it's likely that the range has been converted to Custom. If so, you'll need to re-format a cell or range with the specific format you want so Excel doesn't automatically format dates in blank cells.
You need to highlight the data area you want to format according to the data type you want, then in the format selection section you choose the format type you want to use.
Enter text to prevent Excel date auto formatting
One of the easiest ways to get Excel to display the exact text you want instead of autocorrecting to a date or number is to use the ' (apostrophe) character at the beginning. This indicates that you are entering a string of characters, forcing Excel to display it as text, but still automatically register it in the format that Excel autocorrects.
Excel does not display the caret when viewing data, but it does display it in the formula bar when you select a cell.
Prevent Excel from saving fractions as dates
If you want to automatically apply number formatting to cells containing fractions (such as 1/2), you can also use a leading zero and a space. Or we can change the format to Fraction.
- How to stop Excel from changing fractions to date format