Microsoft Excel is an incredibly sophisticated spreadsheet program, and we use it in our day-to-day life. Excel is created just to optimize the productivity of its users. While it functions as expected in most cases, there are some situations where it may actually work against you. In your work life, the following question may come to your mind once, “why is Excel changing my numbers to decimals?”.Here, we will take you through 2 easy and convenient workarounds to this phenomenon. Right before that, you’ll explore 2 specific reasons why Excel is changing your numbers to decimals.
Why Excel Is Changing Numbers to Decimals? (2 Handy Reasons)
For ease of understanding, we’re using a Name List of Students of a particular institution. This dataset contains the Names in Column B.
Now, we’ll try to make you identify with the problems first. In this case, we’ll use this dataset.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.
1. When Auto-Insert Decimal Point Option Is Active
Sometimes, Excel changes the numbers into decimals because its auto-insert decimal point is enabled. Let’s perceive the matter step by step with proper illustrations.
📌 Steps:
Presently, we want to make a marks list of a subject for the student.
- Create a new column named Obtained Marks in Column C.
- In cells under this column, we’ll input the marks of the corresponding students.
- First, write down the marks of John Walter in cell C5. He gets 62 in this subject.
- Then, press ENTER to place the number in that cell.
- As soon as we press ENTER, the number in cell C5 gets transformed into 0.62.
But we expected to get the number 62 in the cell.
- After that, insert other students’ scores in the sheet too.
Surprisingly, the same thing happens to them.
Read More: How to Stop Excel from Auto Formatting Numbers
Similar Readings
2. If Currency Format Is Used
When we work with cash, bills, ledger, or any kind of medium of exchange in Excel, we use the Currency format. Then the problem associated with decimals happens also. Let’s see the dilemma first.
📌 Steps:
- At this moment, we want to record the payment amount of each student for their tiffin purpose in a worksheet.
- For this prospect, we made a column named Tiffin Cost first under Column C.
- Then, select cells in the C5:C14 range.
- After that, go to the Home tab.
- Following this, click on the Number Format drop-down.
- Lastly, select Currency from the available options.
- Now, select cell C5 and insert the amount of John Walter.
- Therefore, press the ENTER key.
- Unexpectedly, there comes a decimal point with two trailing zeros.
- Likewise, the same happens for others.
Read More: How to Stop Excel from Changing Numbers to Scientific Notation
How to Stop Excel Changing Your Numbers to Decimals
In the previous section, we talked about the reasons for this problem. Now, we will show the workarounds to stop changing your input numbers to decimals in Excel. So, let’s explore them one by one.
1. Utilizing Excel Options Dialog Box
To solve the first problem, we’ll get the help of Excel Options. It’s simple & easy, just follow along.
📌 Steps:
- At the very beginning, jump to the File tab.
- Then, choose Options from the menu.
- Immediately, it opens the Excel Options dialog box.
- Here, move to the Advanced tab.
- In the Editing options, uncheck the box of Automatically insert a decimal point.
- Consequently, click OK.
Note: You can press just ALT + F + T to open the Excel Options dialog box.
- Again, it returns us to the worksheet.
- Then, select cell C5 and edit it. In this place, write down 62 again.
- As usual, hit the ENTER key.
Now, we can see that it shows as 62, not changing into decimals.
- Finally, enter all the entries in the sheet.
Now, they are in the correct format.
2. Applying Changes in Format Cells Dialog Box
To solve our 2nd problem, we’ll use the Format Cells dialog box in Excel. Let’s explore the method step by step.
📌 Steps:
- Initially, highlight cells in the C5:C14 range.
- After that, jump to the Home tab.
- Then, click on the Decrease Decimal icon on the Number group twice.
- Alternatively, select the cell range.
- Then, go to the Home tab.
- After that, click on the little arrowhead at the right of the Number group.
- Instantly, the Format Cells dialog box appears.
- Here, the Currency format under the Category section is already selected because we had applied this to the range at first.
- Now, change the Decimal places to 0.
- As always, click OK.
We can utilize any of the two ways we showed above. Thus, the numbers will look like the one below.
How to Stop Excel from Rounding Large Numbers
Frequently, we round numbers in Excel to make them easier to understand. But sometimes it causes unnecessary trouble. Look at the picture below.
Here, the Rev is displayed in exponential form. But it may be difficult to understand for the general people. So, we want Excel to show the full number like in cells D6 and D7. Let’s see the process in detail.
📌 Steps:
- Firstly, select the cells in the D5:D14 range.
- Hereafter, proceed to the Home tab.
- Consequently, click on the Number Format drop-down on the Number group.
- Correspondingly, select the Number format from the options.
Currently, the contents in Column D look like the one below.
It’s because this column isn’t wide enough to accommodate the numbers. So, they’re displaying themselves like that.
- At present, place your cursor on the thin line between columns D and E.
- Then, make double-click on your mouse.
- For a better understanding, see the following screenshot.
Now, all the numbers are showing in full format instead of rounding themselves.
Also, you can follow the How to Stop Excel from Rounding Up Decimals article to explore the same task extensively.
Read More: How to Stop Excel from Rounding 16 Digit Numbers
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
Conclusion
This article provides easy and brief solutions to the problem of changing numbers to decimals in Excel. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.
