Besides Excel having many valuable and smart features, it can be frustrating sometimes. For instance, if you enter a date on your worksheet in this format (mm/dd/yy), excel tends to format the date into the correct order. Also, it can convert amounts in dollars/standard figures into the date format. So, how do you stop Microsoft Excel from changing numbers to dates automatically? This tutorial will guide you on how to turn off Excel date formatting.
Change the Format to Text
The best way to stop excel date formatting is by using texts to let excel know that they are not numbers. Dates are written as numbers; therefore, if you use texts, excel will leave it in a text format. Change the format to text by:
1. Select the cell or cells that you want to change to text
2. Navigate to the “Home Tab”
3. Proceed to the number group in the home tab
4. Click on the launcher icon in the dialog box that appears
5. Go to the “Format Cells” dialog box and click “Text.”
6. Click “Ok” to close the dialog box.
This process will change your format to text. Now you can type your numbers without any interference. Note that this method will not work if you have already typed your data. Moreover, it works best if you work in a wide range of cells.
Add a leading Apostrophe
Adding apostrophes or space is the best if you are typing only a couple of cells. Excel will consider the cell as text. You will not see the space or apostrophe sign, but you will notice your numbers aligned to the left, showing that it is a text. Usually, text values in excel align to the left, whereas the numbers align on the right.
Here is how to stop formatting using space and an apostrophe.
1. Add a “Space” or “Apostrophe” right before entering a number
2. Type in your number of data
3. Click “Enter”
If you use an apostrophe, the apostrophe will disappear after you click enter, and on the other hand, the space will still be there after you click enter.
One significant advantage of using the space bar and the apostrophe for text formatting is you can still incorporate formulas like VLOOKUP and MATCH into your worksheet. Also, using this method, you can still convert existing data into text format. You may come across a green triangle on top of your cells. This indicates that you have successfully converted your work into text. If you don’t want it to appear:
4. Click it
5. Choose the “Ignore Error Option,” and it will automatically disappear.
Adding an apostrophe before data is a very useful hack in Excel. Apart from turning off Excel date formatting, you can also use it to prevent any other form of data from automatic formatting. Furthermore, if you don’t want leading zeroes to disappear then you can use a leading apostrophe so that Excel treats the Zero as Text.
Use Kutools to change to convert it to a standard date format
Kutools is an Excel Add-in by Extend office that comes with a pack of Excel functions to make work easier. It comes with a feature that can convert dates to the standard date format that Excel and most programs use. This method is efficient for both existing and new data. Follow these simple steps.
1. Download and Install Kutools adding from Extendoffice official website.
2. Select the cells with date
3. Click on the Kutools Tab>>Content>>convert to date
4. Click okay to apply the changes
Add a leading space to turn off Excel date formatting
When you add a space before writing the date, Excel will treat this data in text form. This is one of the efficient ways to turn off Excel Date formatting as it treats data as text. Here are the steps to follow:
1. Add space by pressing the space bar
2. Type in the date and press enter
3. Repeat the same procedure on cells that you wish to include
Use 0+ to turn off Excel date formatting
You can use a zero to avoid the automatic conversion of numbers to text. Here’s how to do it:
1. Type “0”
2. Click on the “Space Bar” to add space
3. Type in your number of data
4. Press enter
The inputted number will be converted into text. For instance, 0 4/5 will be ⅘. Note that this method will convert your numbers into a fraction.
That’s it. As you can see, it is possible to stop excel from automatically changing numbers to dates, making it possible to type your desired values. You can use format tools, apostrophe, space bar, or add a zero before your number. It’s that easy.