Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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 why Excel is changing my numbers to zero.


Download Practice Workbook

You can download the practice workbook from here:


4 Solutions to Excel Changing Numbers to Zero

Here, I will explain 4 possible solutions of 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 contains more than 15 digits.

Dataset for Why Is Excel Changing My Numbers to Zero

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

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


1. Use of Quotation Mark

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

As 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 Quotation (‘) mark before the number. Then, Excel will consider your number as text format and all those digits will be exactly what have you written.

Solutions for Why Is Excel Changing My Numbers to Zero

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

  • Firstly, click on the Warning mark.
  • Secondly, from the Menu Bar >> choose Ignore Error.

At this time, you can see the following result.

  • Similarly, use the Quotation mark before all the numbers.

Lastly, you will get the following numbers.

Result of Using Quotation mark for Why Is Excel Changing My Numbers to Zero

Read More: How to Stop Excel from Changing Numbers (3 Easy Methods)


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 which contain more than 15 digits.

  • Firstly, select the cells where you want to insert numbers which contain more than 15 digits. Here, I have selected the D5:D11 range.
  • Secondly, Right-click on the Mouse. Or, you can use Excel keyboard shortcuts SHIFT+F10 to open the Context Menu Bar.
  • Thirdly, 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.

Employing Format Cells as Solution for Why Is Excel Changing My Numbers to Zero

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 Quotation mark before your number. Which makes sure that the number is in Text format.

Lastly, you will get the following numbers.

Read More: How to Stop Excel from Rounding 16 Digit Numbers (2 Easy Ways)


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 which 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.

Applying Text Feature as Solution for Why Is Excel Changing My Numbers to Zero

  • Now, write down your number.

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

Lastly, you will get the following numbers.

Read More: Why Excel Is Changing My Numbers to Decimals (with Solutions)


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.

Use of Legacy Wizard for not Changing My Numbers to Zero

  • 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 which 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.

Lastly, 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.

  • Lastly, press OK.

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

Read More: How to Stop Excel from Auto Formatting Numbers (3 Easy Ways)


💬 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.

Practice Section for Why Is Excel Changing My Numbers to Zero


Conclusion

I hope you found this article helpful. Here, I have explained 4 suitable solutions for why Excel is changing my numbers to zero. Also, you can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo