ExcelInsider Team
By default, Excel rounds up numbers to two decimal points even though your original stored data is much larger. While it provides a cleaner look, displaying the exact numbers can be essential for detailed scientific data, monetary transactions, or analytical calculations. With some minor changes, you can change this format and display decimals in their full form.
➤ Select the cells with decimals and click on the Home tab.
➤ From the top ribbon, locate the Number group and click the downward arrow (launch icon) to open the Format Cells options. Or, you can press CTRL + 1 .
➤ As the Format Cells box appears, select Number. In the Decimal Places box, type how many decimal places to display in your dataset.
➤ Click Ok and Excel will display all the digits of your original numeric value without rounding up.
Other ways of preventing decimal roundup in Excel include fixing cell width, setting custom format, and using the TEXT function. In this article, we’ll cover each method step by step.
Remove the Set Precision As Displayed Setting to Avoid Decimal Roundup
Our dataset contains 4 different columns of stock prices, volume, and related metrics. In Column D (Traded Volume), all our decimal numbers extend up to at least 8 decimal places.
As you can see, Excel has rounded up the decimals to two places even though they are formatted as Numbers. You can see the original value of the highlighted cell in the Formula box.
Before you load the decimal values in your worksheet, removing the Set Precision As Displayed setting can prevent data roundups. However, this won’t make any difference if you’ve already inserted your numeric values in the workbook.
Also, this method doesn’t always work. Yet, it ensures that Excel stores your inserted data correctly. Here’s how to use it:
➤ Click on the File tab, choose More from the side column, and select Options.
➤ Go to Advanced and scroll down to locate the Set Precision As Displayed box in the When Calculating This Workbook group.
➤ Uncheck the box and click Ok. After that, you can load the decimals in your desired cells.
Fix Column Width to Display the Full Numeric Values
In some cases, not fixing the column width according to your data’s size can be the reason why Excel doesn’t display full numeric values.
Sometimes Excel display hashtags(####) if the column width is too narrow for the data string. To fix this problem, follow the steps given below:
➤ Select the column with the decimal numbers and put your cursor on the right side of the column header.
➤ As the double-pointed + shaped arrow appears, double-click on it to readjust the size of the cells.
➤ When the cell extends, Excel displays the full numeric values as shown in the following image:
Using the Increase Decimal Feature to Adjust Decimals
Although this is the easiest method of adjusting the decimal places manually, it can be tiresome for large values. Here’s how to utilize the Increase Decimal feature effectively:
➤ Select the cells with the rounded decimal numbers and go to the Home tab.
➤ From the top ribbon, navigate to the Numbers group and click on the Increase Decimal icon.
➤ Keep clicking on the icon until you get all the digits to display after the decimal point.
Format Cells and Specify Decimal Places
With the Format Cells option, you can convert your numeric values into numbers, currencies, scientific values, or accounting data. There’s also a custom formatting option.
No matter which format you choose, always specify the decimal places to prevent Excel from rounding up the decimals. Let’s get to the steps:
➤ Select all the cells with decimal numbers and click on the Launcher button in the Number group of the Home tab. You can also use the keyboard shortcut CTRL + 1 .
For Excel-Specified Formatting
➤ In the Category section of the Format Cells box, choose Number, Scientific, Currency, or Accounting based on your sheet data. As we’re using decimal numbers, we selected the Number category.
➤ Go to the Decimal Places box and type a number or use the upward/downward arrow to specify how many digits you want to display after the decimal point. Click Ok.
For Custom Formatting
➤ Instead of selecting from the existing categories, you can click on Custom and enter 0.000000## in the Type box.
➤ This way, Excel will display 8 digits after the decimal point. The zeros represent the first 6 digits and the hashtags (#) represent the remaining 2 digits.
➤ Change the number of zeros and hashtags according to your dataset. For example, if you want to display 4 digits after the decimal point, insert 0.00## in the Decimal Places box.
➤ Finally, press Ok.
In both cases, Excel displays the original numeric values up to your specified decimal places.
Apply the TEXT Function to Specify Decimal Places
With the TEXT function, we can create a formula that converts the decimal numbers into text and specifies the decimal places. Keep in mind that converting to text won’t allow you to perform accurate numeric calculations in Excel with this converted data. Here’s the process:
➤ Create a helper column and enter the following formula in the first cell adjacent to the first decimal number:
=TEXT (D2, “0.00000000”)
➤ Here, D2 is the cell reference containing the decimal number we’re converting. The number of zeros (0) after the decimal point represents how many digits we want to display after the decimal point. Change the values according to your dataset.
➤ Press Enter and use the tiny + sign (fill handle) on the bottom corner of your cell to drag and apply the formula to the remaining cells.
Display Large Decimal Numbers with Apostrophe
In some versions of Excel, you can’t accurately input more than 15 digits in a single cell. Excel rounds up the numbers or simply displays zeros (0) for any value you enter after the 15-digit mark. Using an apostrophe (‘) in the following way will fix this problem:
➤ For this method, we inserted a 15-digit decimal number in cell D2.
➤ As you can see, Excel doesn’t display the original value in the cell, but you can find it in the formula box when you click on the cell.
➤ To display the full numeric value in the cell, enter an apostrophe (‘) before the first number and press Enter.
➤ Now, Excel displays the full decimal number without rounding up while also hiding the apostrophe.
Note:
Using an apostrophe also converts numerical data into a text string, so you’re unable to use it accurately for future calculations and formula applications.
Frequently Asked Questions
How do I get rid of decimals in Excel without rounding?
Use a formula with the TRUNC function to remove decimals without rounding. In a helper column cell, type the following formula:
=TRUNC(D2, 0)
Replace D2 with the appropriate cell reference containing the decimal number. Instead of 0, you can use a whole number to specify how many decimal places you want to display.
How to remove scientific notation from large numbers in Excel?
Sometimes Excel rounds up large numbers and displays them as exponential E+ (example: 2.06578E+14). To avoid applying such scientific notations, select the cells and press CTRL + 1 to open the Format Cells window. Click on Number and type 0 in the Decimal Places box. Press Ok.
How do I stop Excel from turning decimals into dates?
To stop Excel from converting decimals to dates, you need to format your cells as numbers. Select the cells with decimals and click on the Home tab. Go to the Number group in the top ribbon and select Number in the cell format box, replacing the General or Date format.
Wrapping Up
To stop rounding up decimals in Excel, choose the correct method depending on how Excel rounds up data. In any case, formatting cells as numbers and specifying decimal places always work for any type of dataset.
However, it can be much simpler like changing the column width or adjusting decimals. Try each method keeping in mind the drawbacks we’ve mentioned.
