We frequently round Excel numbers to make them easier to understand. However, when working with sensitive data, it can be inaccurate. Simple fractional changes can have a significant impact on the entire project. So, in this tutorial, we will show you how to stop Excel from rounding up decimals.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Simple Methods to Stop Excel from Rounding Up Decimals
We’ve provided a data set in the image below that shows rounded numbers to two decimal places, though our actual numbers go up to five decimal places. We’ll use Excel basics like adjusting column width, changing the number format, and customizing the Format Cell box to stop Excel from rounding up the decimals.
1. Adjust Column Width to Stop Excel from Rounding Up Decimals
Excel may not display the numbers in full decimals due to a lack of column width. It could appear as a rounded number or as a (###) sign. To get the number in whole decimals, you need to adjust the column width.
⇒ Steps: Adjust Column Width
- Click, hold, and drag in the column margin until the number appears in full decimal format.
- Or, simply double-click the column margin to make changes.
2. Alter Number Format to Stop Excel from Rounding Up Decimals
For large numbers, if the number is in General Format, Excel will not display the number in full decimal. It will appear as if we are showing the scientific number, as shown in the image below. The number format should be changed from General to the Number.
⇒ Steps: Change Cell Format
- From the Number Group, select the Number format instead of General.
- As a result, you’ll notice that the numbers are displayed in their entirety.
- Round Time to Nearest 5 Minutes in Excel (4 Quick Methods)
- How to Round Time in Excel (With 3 Examples)
- Excel VBA: Round to Nearest 5 (Macro and UDF)
- Round to Nearest 5 or 9 in Excel (8 Easy Methods)
- Rounding Time to Nearest Quarter Hour in Excel (6 Easy Methods)
3. Utilize Format Cells Dialog Box to Stop Excel from Rounding Up Decimals
You can select how many digits to display the numbers from the Format Cells box. So, you can type the number of decimal places you want to see from there. Furthermore, it will apply to all types of numbers, such as accounting and currency.
⇒ Steps: Utilize Format Cells Box
- Select the cells you want to show the numbers in full decimals.
- Press Ctrl + 1 to open the Format Cell box.
- From the Number tab, choose any of the number categories (Number, Accounting, or Currency).
- In the Decimal Places box, type the number of decimal places (5).
- In the Sample box, you can see a preview of the change.
- Then, click OK.
- Therefore, the number will show up to the 5 decimal places.
- For currency, choose the Currency from the Number tab.
- Type the decimal place number in the Decimal places box.
- You can choose a preferred currency sign ($=USD) from the Symbol box.
- Finally, press Enter.
- As a result, the number will be displayed with 5 decimal places and the USD ($) symbol.
4. Stop Excel from Rounding Up Decimals for More Than 15 Digits
Excel does not allow digits after 15 digits to be displayed by default. After 15 digits, it displays zero (0) for the last digits. Follow the steps outlined below to display the full number with more than 15 digits.
⇒ Steps: Add an Apostrophe
- After adding the apostrophe (‘) sign before the number, type the number.
- Press Enter.
- To eliminate the error, click on the left-hand icon for the error.
- Click on the Ignore Error option from the list.
- Finally, the number will appear in its entirety.
Read More: How to Stop Rounding in Excel (5 Handy Ways)
I hope this article has given you a tutorial about how to stop Excel from rounding up decimals. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.
Please contact us if you have any questions. Also, feel free to leave comments in the section below.
We, the Exceldemy Team, are always responsive to your queries.
Stay with us and keep learning.
- Calculate Accuracy and Precision in Excel
- How to Round Off to Nearest 50 Cents in Excel (4 Quick Methods)
- Round Down to Nearest 10 in Excel (3 Effective Ways)
- How to Round Time to Nearest 15 Minutes in Excel (6 Quick Methods)
- Round Time to Nearest Minute in Excel (5 Suitable Ways)
- How to Set Rounding Precision in Excel (with Easy Steps)
- Round Percentages in Excel (4 Simple Methods)