Why Excel Is Changing My Numbers to Decimals? (with Solutions)

Get FREE Advanced Excel Exercises with Solutions!

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.

why is excel changing my numbers to decimals

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.

When Auto-Insert Decimal Point Option Is Active

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

Entering Marks of Student

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

Obtained Mark as Decimal

But we expected to get the number 62 in the cell.

  • After that, insert other students’ scores in the sheet too.

Excel Changing Numbers to Decimals When Auto-Insert Decimal Point Option Is Active

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.

If Currency Format Is Used

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

Changing Number Format

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

Number Displaying with Decimals

  • Likewise, the same happens for others.

Excel Changing Numbers to Decimals If Currency Format Is Used

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.

Utilizing Excel Options Dialog Box

  • Then, choose Options from the menu.

Choosing Options from 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.

Utilizing Excel Options Wizard

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.

Editing the Cell

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

Utilizing Excel Options Dialog Box to Stop Changing Numbers to Decimals

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.

Applying Changes in Format Cells Dialog Box

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

Opening Format Cells dialog box

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

Working on the Format Cells Dialog Box

We can utilize any of the two ways we showed above. Thus, the numbers will look like the one below.

Applying Changes in Format Cells Dialog Box to Stop Changing Numbers to Decimals


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.

How to Stop Excel from Rounding Large Numbers

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.

Changing Number Format

Currently, the contents in Column D look like the one below.

Excel Cell Showing ##### Error

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.

Adjusting Column Width

Now, all the numbers are showing in full format instead of rounding themselves.

Stopping Excel from Rounding Large Numbers

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.


Related Articles

Shahriar Abrar Rafid
Shahriar Abrar Rafid

Welcome to my profile! I'm thrilled to have you here. As a dedicated Naval Architecture and Marine Engineering graduate from the prestigious Bangladesh University of Engineering & Technology, I am deeply immersed in the realm of research and analysis. My current focus revolves around Microsoft Excel, where I engage in extensive work and conduct insightful research. Through this platform, I share articles that shed light on the vast possibilities of Excel. I'm also an avid reader and passionate traveler, constantly seeking knowledge and implementing it effectively in my work. Join me on this exciting journey as we explore Excel and optimize our productivity together.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo