Depending on what a person is doing in Microsoft Excel, there may come a time when they have to work with decimal places. If this is indeed the case, then one must learn how to change the number of decimal places in an Excel spreadsheet.
We should point out that the ability to determine the number of decimal places shown on a spreadsheet is possible on all versions of Excel from the year 2007 to date. For this article, we are using the Office 365 version of Excel, so the steps might be a bit different compared to the version you’re currently using. Now, there is more than one way we can change the number of decimal places in Microsoft Excel, and we will discuss each option.
How to change Decimal places in Excel
You can use any one of these methods to change the number of decimal places in Microsoft Excel:
- Use Increase Decimal and Decrease Decimal buttons
- Add a built-in Number Format
- Make your preferred Decimal Places number the default
1] Use Increase Decimal and Decrease Decimal buttons
If you’ve already entered numbers in your worksheet, then we suggest using the increase or decrease button to determine where decimal places will show up. These are buttons on the Ribbon, which means, they are not hard to find.
- Open the Microsoft Excel application.
- Go to the preferred worksheet.
- Choose the cells you want to format.
- From the Home tab, you must look for the Increase Decimal, and Decrease Decimal buttons.
- Click Increase to show more digits or Decrease to show fewer digits.
Right away you’ll realize that your new decimal place settings are in effect and ready to go.
2] Add a built-in Number Format
Those who are using the desktop version of Excel, have the option to create custom decimal rules by using the Number Format option.
- From within your spreadsheet, go to the Home tab.
- Look to the Number group.
- You are now required to select the arrow that is next to the list of number formats.
- After that, click More Number Formats.
- From the Category list, you can choose either Currency, Accounting, Percentage, or Scientific. It all depends on the type of data situated in your worksheet.
Now, from the Decimal places box, please enter the number of decimal places you want to show up.
3] Make your preferred Decimal Places number the default
The final thing to do here if you’re not all that interested in the above solutions is to set your preferred decimal places as the default options.
Before we begin, it should be noted that this feature is not available in Excel Online, so do not attempt it there.
- Open Excel and select Options.
- If you have a spreadsheet up and running, then click on File > Options.
- From the Advanced category, go to Editing Options.
- After that, click the checkbox next to Automatically insert a decimal point.
- You should see the Places box.
- Enter a positive number to the right of the decimal point, or a negative number to the left.
- Hit the OK button.
From within your worksheet, click on a cell and type the required number.
READ: How to use the MOD function in Microsoft Excel
How many decimal places does Excel use?
Here’s the thing, Excel can showcase around 30 decimal places, however, its precision for a specified number is restricted to 15 significant figures. Furthermore, calculations could have less accuracy due to five major issues, and they are, round-off, truncation, binary storage, accumulation of the deviations of the operands in calculations, and finally, cancellation at subtractions resp.
Why is Excel not showing decimals?
Some users are having problems with their version of Excel failing to show decimal places, so why is this happening? Well, it appears as if it has something to do with the number format of the cells where the numbers are available. The best thing to do here is to change the cell format in order for it to show more numbers than what it shows at the moment.
How do I fix rounding problems in Excel?
First, you must click File, and from there, select Options. After you’ve done that, please select Excel Options, then navigate to Advanced. From there, you are required to look for When Calculating This Workbook and choose Set Precision as Displayed. Finally, hit the OK button, that’s it, you’re done.