As a bonus, they make worksheets look more consistent and professional. They are a great way to save time in Excel because they perform a huge amount of formatting automatically. The key benefit of number formats is that they change how a number looks without changing any data. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.Number formats control how numbers are displayed in Excel. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. Setting the decimal places to display the whole value of currencies This way, the precision and accuracy of our data is preserved as close to the original value as possible.įigure 11. To stop Excel from rounding currencies, format the decimal places to “3 or more”. The table above shows the effect of rounding currencies into the total commission. Sample calculation showing the difference with rounded numbers In Accounting, for example, in order to arrive at a more accurate result, rounding should be done only as needed, or as late into the calculations as possible.įigure 10. There are instances, however, where rounding currencies becomes serious. Rounding currencies might have a small impact since it will only be dealing with tenths or hundredths of a currency, such as centavos. Adjusting column width to display the entire number Stop Excel From Rounding Currency To stop Excel from rounding a whole number, we can adjust the column width to display all the digits.įigure 9. A large number displayed with different number of significant digits based on column width Increase Decimal button in ExcelĮxample 2: In cell D3, enter the number 123456789, and see how Excel rounds off the number into varying number of significant digits, depending on the column width.įigure 8. Increase the decimal place until the desired number of decimal places is displayed.įigure 7. To stop Excel from rounding whole numbers, click the Increase Decimal button in the Home > Number tab. The value of pi() displayed with varying number of decimal places Column E displays the value of pi with varying number of decimal places.įigure 6. Let’s take for example the value of pi(), which is commonly used in mathematical equations.Įxample 1 : In cell D3, enter the formula =pi(). Entering the large number as text by preceding with an apostrophe Stop Excel From Rounding Whole Numbers Enter the number as a text by entering an apostrophe “ ‘ ” before the number.Format the cell as text before entering the number or.To stop Excel from rounding a large number, especially those exceeding 15 digits, we can:
HOW TO SHOW SIGNIFICANT DIGITS ON AN EXCEL GRAPH AXIS LABEL HOW TO
Changing the cell format from “General” to “Number”īut how to display the original number 346003617942512178 ? The 18-digit number is now displayed in D3.įigure 4. Select the format “ Number ” and set the decimal places to zero “0”. To display the 18-digit number 346003617942512000, select D3 and press Ctrl + 1 to launch the Format Cells dialog box.The displayed value only shows 6 significant digits because default format in Excel is “General”.The 16th to 18th digits are changed from “178” to “000”.Excel rounding a large number and showing 15 significant digits only Cell D2 will display the value “ 3.46004E+17 ” while the formula bar will display the value “ 346003617942512000 ”.įigure 3. The excess digits will be changed to zeros.Įxample : In cell D3, enter the number “ 346003617942512178 ”, which contains 18 digits.įigure 2. Unfortunately, Excel has a limitation and displays only 15 significant digits. There are instances when we need to enter large numbers, such as credit card or reference numbers. In this article we will discuss the following: The default format for every cell in Excel is set to “General” and not “Number”.The number is too large and exceeds 15 digits Excel is limited to display only 15 significant digits.The number of decimal places is set to fewer digits than the actual decimal places.Column width too narrow and doesn’t display the whole number.There are several possible reasons but the most common are these: Isn’t it annoying when Excel just keeps rounding off numbers? Below table shows some common examples:įigure 1. How to Get Excel to Stop Rounding Your Numbers