While working with number values in Excel, sometimes, it may leave you with a different format from what you expected. For Instance, when you input 2-22, Excel might have returned a date like Feb-22. Or, numbers like 123456789012 as 1.234567E+11 scientific notation.
Excel converts these numbers into other formats when it misperceives the value. Meaning, the app is preprogrammed to identify the numbers with delimiters as dates and automatically changes them. However, in some cases, Excel might do this to display the numbers according to the cell size.
Here, you can learn 4 different cases when Excel auto-changes the number and solutions to fix them.
Case 1: Numbers Changing Automatically
If you’ve used the RAND or RANDBETWEEN function in Excel, the number will automatically change each time you recalculate the sheet. To stop this from happening, you can press Ctrl + C to copy the value you generated using the function. Then, paste them as Value Only in the same cell.
Case 2: Numbers Rounding Up
During number calculations, Excel automatically rounds up numbers in the output. This usually happens when the column width is too small to fit the number. Or, when the decimal format is set to show only small numbers.
If you do not want the numbers to round up automatically, follow these fixes.
Increase Decimal Place
Excel shows decimal places only up to two numbers for all formats. To show all numbers in cells, you can increase the decimal value.
For this, select all cells with decimal numbers and head to Home Tab. On the Number section, click Increase Decimal. Click this button until all the decimal numbers appear on your cell.
You may encounter an ##### error when you raise the decimal number. Such an error arises if the numbers do not fit in the column size. Expanding the column width should fix this.
Format Decimal Place of Numbers
In the above method, we learned how to increase decimal places for the number values in Sheets. But, you can also set the decimal place for each number format like accounting, percentage, number, and many more.
- Enter Ctrl + 1 to bring up the Format menu.
- On Format cells window, click on Number Tab.
- Head to Number Category. Then, on Decimal places, raise the number using the increase button. Or, you can manually enter the number in the box.
- Repeat Step 3 for other categories too. When done, click OK.
Case 3: Numbers Changing to Scientific Notation
Sometimes, when you enter the numbers and letters together in sheets, Excel will instantly convert them into the Scientific Format. It happens when there is the letter “E” in the value. For Example, when you enter 12E50, you’d get 1.20E+51 instead. To avoid this, you can pre-format cells as text or convert them to numbers.
Pre-Format Cells as Text
Excel allows you to apply Number formats to blank cells too. So, here, we will format the required empty cells as Text and input value.
For this, select empty cells and navigate to Home Tab. On the Number section, set the format to Text.
When you manually enter numbers, it will not convert to Scientific Notation. Note that this method will not work if you paste the data in pre-set Text format.
Convert Data to Number Format
When you enter long numbers (exceeding 15 digits), it struggles to fit in the column. During such cases, Excel will display the value in Scientific format. To fix the problem, select the data and can convert them to Number Format.
Case 4: Numbers Changing to Date
Whenever you import numbers or manually input them in sheets, Excel automatically converts numbers into dates. This is because Excel is automated to make date entry simpler. You get this while entering numbers with “–” or “/” delimiter.
Use Space
The most simple way to stop numbers from changing into dates is by using Space. Use this method if you’re yet to input numbers.
Here, firstly, enter a space in an active cell. Then, type in numbers and hit Enter. When you do this the space will still be in the cell and keeps the number format intact.
Use an Apostrophe
Similar to the space, you can also enter an apostrophe (‘) symbol to retain the number format. Here, we will add (‘) before numbers like ‘2-22. You can also use the flash-fill for other cells.
This method is especially handy when you need to use MATCH and VLOOKUP functions as these functions ignore the apostrophe during calculation.
Use Both Zero and Space
Excel will also return fraction numbers like 2/22 as 22-Feb dates. So, to retain such fraction numbers, you can enter 0 and space before typing numbers. When you do this, you’ll get the fraction format as output in the cell. Also, the leading zero is gone from the cell.
