Why Is Excel Changing My Numbers to Zero? (4 Solutions)

Sometimes you may need to insert numbers more than 15 digits in Excel. But, there is a problem as Excel changes the number after 15 digits. Basically, Excel includes zero after 15 digits in a number. Thus, in this article, I will explain the solutions to why Excel is changing my numbers to zero.

Why Is Excel Changing Numbers to Zero: 4 Possible Solutions

Here, I will explain 4 possible solutions to why Excel is changing my numbers to zero. For your better understanding, I’m going to use the following dataset. Where I have inputted numbers which contain more than 15 digits.

Here, if you notice then you can see that the inputted number will be in General Number format which will consider the numbers in Scientific form.

Therefore, to avoid the Scientific form I have used the Number format for all those numbers.

1. Use of Apostrophe Mark

Here, as you can see when you have inputted numbers more than 15 digits, then all those digits after 15 digits become zero.

For example, you want to write 2000415000459866 but Excel will consider this as 2000415000459860. Here, the last digit becomes zero.

Again let’s you want to write 20004150004598678 in Excel. But as well as, Excel will consider this as 20004150004598600. Here, the last two digits become zero.

Now, I will give the solution for not changing my numbers to zero.

Solutions:

You can use the apostrophe(‘) mark before the number. Then, Excel will consider your number in text format and all those digits will be exactly what have you written.

Here, you can see a Warning mark beside your number. To remove the Warning mark you can follow the given steps.

First, click on the Warning mark.
Next, from the Menu Bar >> choose Ignore Error.

At this time, you can see the following result.

Similarly, use the apostrophe mark before all the numbers.

Lastly, you will get the following numbers.

2. Employing Context Menu Bar

You know that Excel doesn’t count the exact written numbers which contain more than 15 digits. Actually, Excel converts the digits into zero after the 15th term of any number. So, check the solutions given below for why Excel is changing my numbers to zero.

Solutions:

Here, you can use the Context Menu Bar to get the exact inserted numbers that contain more than 15 digits.

First, select the cells where you want to insert numbers that contain more than 15 digits. Here, I have selected the D5:D11 range.
Next, Right-click on the Mouse. Or, you can use Excel keyboard shortcuts SHIFT+F10 to open the Context Menu Bar.
Then, from the Context Menu Bar >> choose the Format Cells option.

Also, you can press the CTRL+1 keys to open the Format Cells dialog box directly or you can use the Custom Ribbon to go to the Format Cells command. In the case of using Custom Ribbon, select the data range >> from the Home tab >> go to the Format feature >> choose the Format Cells command.

In the end, a dialog box named Format Cells will appear.

Now, from that dialog box, you have to make sure that you are on the Number command.
Then, select the Text option.
Finally, you have to press OK to get the changes.

Now, write down your number.

Here, if you notice then you can see that there is a apostrophe mark before your number. Which makes sure that the number is in Text format.

Lastly, you will get the following numbers.

3. Applying Text Feature

Excel accepts a number with only 15 digits. Thus, after the 15th term of a number Excel considers all other digits as zero. So, you may find changing your numbers to zero. Now, let’s talk about the solutions.

Solutions:

Here, you can apply the Text feature to keep constant your input numbers.

Firstly, select the data range where you want to insert numbers that contain more than 15 digits. Here, I have selected the D5:D11 range.
Secondly, from the Home tab >> go to the Number command.
Finally, choose the Text feature.

Now, write down your number.

Here, if you notice then you can see that there is an apostrophe mark before your number. Which makes sure that the number is in Text format.

Lastly, you will get the following numbers.

4. Use of Legacy Wizard

There is another tricky way to stop changing my Excel numbers to Zero which is using the Legacy Wizard feature. For this, follow the steps below:

First, click on the File tab on the top ribbon.

Then, select the Options menu from the list.

Thus, a new window named Excel Options will appear.

Here, go to the Data option in the list.
Then, mark all the lists under the title named “Show legacy data import wizards”.
Finally, press OK.

Now, select the cell where you want to insert numbers that contain more than 15 digits. Here, I have selected the D5 cell.
After that, from the Data tab >> go to the Get Data menu.
Then, from the Legacy Wizards feature >> select From Text (Legacy).

At this time, a new dialog box named Import Text File will appear.

Now, choose your text file. Here, you must select any .txt extension file.
Then, click on the Import button.

Here, write down all the required numbers in a text file. And you should import that particular file.

Subsequently, a new window named Text Import Wizard – Step 1 of 3 will appear.

Here, make sure that the Delimited option is checked.
Then, choose 437: OEM United States in the File origin box.
After that, click on Next.

Then, a new window named Text Import Wizard – Step 2 of 3 will appear.

Now, make sure that you mark the Tab option from Delimiters.
Then, click on the Next option.

Finally, a new window named Text Import Wizard – Step 3 of 3 will appear.

Here, choose Text from the Column data format.
Then, click on the Finish button.

At this time, a dialog box named Import Data will appear. Here, you will see that the Existing worksheet is marked including the cell reference.

Thus, press OK.

Finally, you will see all the numbers in the text file.

💬 Things to Remember

Here, in all those methods you have converted all your numbers into Text format.
Furthermore, in the case of method 4, you write the numbers in a text file and then you will import that into an Excel file.

Practice Section

Now, you can practice the explained method by yourself.

Download Practice Workbook

You can download the practice workbook from here:

Changing Numbers to Zero.xlsx

Conclusion

I hope you found this article helpful. Here, I have explained 4 suitable solutions for why Excel is changing my numbers to zero. Please, drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Excel Auto Formatting | Excel Cell Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Why Is Excel Changing My Numbers to Zero? (4 Solutions)

Why Is Excel Changing Numbers to Zero: 4 Possible Solutions

1. Use of Apostrophe Mark

2. Employing Context Menu Bar

3. Applying Text Feature

4. Use of Legacy Wizard

💬 Things to Remember

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply