How to Stop Excel From Rounding?

When you are using Excel, it will often “helpfully” round numbers, often to two decimal points. But when you are working on the kind of spreadsheet where all the decimal points matter, how can you stop Excel from rounding your figures?

Why Excel Keeps Rounding?

The number Excel stores for any given cell may not be the same as the number it displays. While it will store your original number with up to 30 decimal points, it will round the displayed number, either based on your spreadsheet settings, which tell it how many decimal points to show, or to make the number readable based on the width of the cell.

How Many Decimal Places Can Excel Display?

While Excel can display up to 30 decimal points, it is worth noting that Excel uses the IEEE 754-double-precision floating-point format, which means that the number stored and used in calculations is only accurate to a maximum of 15 significant digits, after which the number is rounded or truncated.

Therefore, it can calculate off the number 0.123456789123456 or the number 123456.123456789 but will truncate any additional decimal points when calculating.

How to Stop Excel From Rounding

To stop rounding in Excel, you will need to change your spreadsheet settings. Start by selecting the entire sheet, or just the cells that you want to change. You can then go to the “Home” tab. In the menu across the top, go to the section on “Numbers” and you will see buttons to either increase the number of decimal places (arrow pointing left) or decrease the number of decimal places (arrow pointing right). Just click the button until you get the number of decimal places you want.

You can also choose the number of decimal places to show, depending on the type of number that is being displayed, for example, currency, percentage, or scientific. Again, select the relevant cells, and in the same “Numbers” menu, choose the arrow in the lower left. This will open a window that shows the number categories on the left, and the details of how they are being displayed on the right. At the top, you will see an option to select the number of decimal places to display.

If Excel keeps rounding your numbers after this, it is probably because the cell is too narrow to show the full number. You simply need to change the width of the cells using the two-directional arrow that appears when you hover in the top axis.

Alternatives to Excel

If you need to calculate, with a great level of accuracy, more than 15 significant digits, you will need alternative tools for more complex data sets, such as Tableau and Qlik.