If Excel keeps changing a number to date when you are trying to input data into a worksheet, you will need to know how to manually rectify this to have your information displayed on your worksheet.
Have you checked what format your cells are currently set to? Configuring this to ‘Text’ will help stop Excel from automatically changing your number format.
Microsoft Excel is also known to round up numbers, which can be resolved in a few simple steps.
With this easy to follow guide, we will help you make sense of why Excel keeps changing the number to date, as well as touch base on Excel’s rounding and automatic formula calculations.
Why does Excel keep changing my numbers to dates?
Why does Excel keep changing my number format?
Excel will keep changing numbers to dates because it is automatically programmed to make inputting dates as easy as possible to help improve the users experience Microsoft. Because this is an automatic feature, Excel can sometimes distinguish the data incorrectly, changing it to a date, or a number format of a date.
You cannot turn off a setting in Excel to stop the program from changing your number format, however, there are manual methods you can do that will prevent Excel from automatically changing your number data into a date.
How do you stop Excel from changing numbers into dates?
Photo by Mika Baumeister on Unsplash
You can stop Excel from changing your numbers into dates by formatting the collection of cells you are going to be using before you input your data. This is completed by changing the format of the worksheet to ‘Text’ instead of being set on ‘General’. This will force Excel to view the numbers you have inputted in the cell as letters.
(Method 1). How to change excel cells to text:
Step 1. Open your Excel workbook
Step 2. Highlight the cells you are going to be using and ‘right-click’
Step 3. Click on ‘Format Cells’
Step 4. Under the ‘Number’ category, change the category to ‘Text’
Excel will now keep your numbers in the same format as you entered them in.
If you want to format your cells as you proceed on your worksheet, you can the following methods to ensure your data does not change its format once you have inputted it into a cell;
Method 2. Excel apostrophe before the text
Before entering your number into the Excel cell, you can insert an apostrophe first, followed by your data.
This will look like: ‘12345.
VLOOKUP and other formulas will still function with the apostrophe included in the cell.
Tip, have you noticed if you have inputted a mobile number in a worksheet, the ‘0’ will disappear in the cell? Inserting the apostrophe before you type the cellphone number will stop Excel from getting rid of the zero.
Excel space in the cell:
Before you input your numbers, you can make a space with your spacebar. This is the same as if you were to use an apostrophe.
These two methods are what you can utilise when you are using an Excel Workbook. We advise you to execute method 1 if you have a large amount of data that needs to be formatted, otherwise, method 2 is good to use.
How to stop excel changing fractions to dates
If you are using an Excel worksheet to work with fractions, Excel will keep changing them into date format. To stop this from happening, you can use methods 1 and 2.
There is an additional method you can utilise when using fractions in Excel;
Method 3. Insert a zero & space
Similar to that of the apostrophe and space method, inputting a ‘0’ followed by a ‘space’ before you type your fraction will keep the data the same.
This will look like; 0 1/2, and stop Excel from changing your fraction numbers to dates.
Why are my numbers in Excel rounding?
Why are my numbers rounding in Excel?
Excel will automatically round your number if you have enabled the rounding function in Excel. Once this has been turned on, if your number has one, or a few decimal places, it will be rounded to the closest whole number.
If the Excel rounding feature has not been enabled, you will be experiencing this issue for the following reasons;
Reason 1. Excel column width is too small
If the width of your column on your worksheet is set too narrow for the length of your number, part of it will be cut off.
The whole number will still appear in the top tab section, however, part of it will be hidden in the cell.
Reason 2. Different Excel formats enabled
Your Excel workbook can be set to a format that will only show a certain amount of digits in a cell. If this amount is smaller than the length of your number, it will only be able to display what fits into the format.
Another format is if your cells are set to ‘General’. This is the automatic setting for each cell when you launch a new Excel workbook. This setting will stick to what the width of the individual cell is and show only what fits.
How to stop Excel from rounding large numbers
You can stop Excel from rounding your large numbers by executing the following solutions;
Solution 1. Increase Excel cell size
Increasing the cell size will allow Excel to display the full number without hiding any digits.
The simplest way to do this is by double-clicking on the right sideline of the column you are currently using.
Solution 2. Use the Excel decimal button
Excel workbooks contain ‘Increase Decimal’ and ‘Decrease Decimal’ buttons that you can use to change how many decimal places are shown in a cell.
If you click on the increase decimal button, the cell will bring back all the numbers that may have been rounded.
Solution 3. Change Excel cell format
Excel sheets are automatically set to ‘General’. Updating this to ‘Numbers’ will allow the cell to accept the number of digits.
You can change this setting by going; Format Cells > Number Tab > Category > Number
You will need to update the ‘Decimal Places’ setting to how many decimals you want to be shown in your cell.
Quick Tip; you can access this setting quickly by clicking on the little arrow next to ‘Number’ that is positioned under the ‘Increase Decimal’ button.
(Fun Fact; Excel assumes the year 1900 is a leap year. This comes from the date system Lotus 123 used that was copied over to Excel. Lotus was the spreadsheet program that was already in the market when Excel came about in 1985. The program was not successful, hence why Excel was then used for Mac users, followed by Windows in 1987).
Why is Excel not automatically calculating formulas?
Why is Excel not automatically calculating formulas?
Excel is not automatically calculating formulas because the ‘Calculation Options’ mode is set to manual and not automatic. If this is set to manual, you will need to go into each cell and complete the calculation. If it is set to automatic, Excel will do complete the formula for you.
You can make sure the calculation option is set to automatic, by going;
Formulas Tab > Calculation Options > Automatic
To make sure your worksheet is configured correctly for formulas to be auto-filled, you can check the following;
- The cell format is on number
- There is no space between the equal sign and your formula in the cell
You can also check that there is a formula in your cell by clicking on ‘Show Formulas’.
Conclusion
With this extensive guide, we hope you now know why Excel keeps changing the number to date, and that you were able to prevent this from happening again on your workbook.
If you are still experiencing issues with Excel, there may be something wrong with your Microsoft program. You can make use of Microsoft’s Excel Help & Learning page. If you can’t find what you’re looking for, you can contact them directly by sending through a description of your issue via their Office Support online form.
(Quiz Time; What program gave Excel the data for 1900 to be a leap year? Fill out our online contact form with your answer).
