How to set precision as on screen in Excel. How to set and adjust precision in Excel

When performing certain calculations in Excel that are directly related to fractional values, users may encounter situations where a completely unexpected number appears in the cell with the output of the result. This is due to the technical features of this program. By default, Excel takes fractional values ​​for calculations with 15 digits after the decimal point, while the cell will display up to 3 digits. In order not to constantly receive unexpected calculation results, it is necessary to pre-set the rounding accuracy equal to that displayed on the screen in front of the user.

How the rounding system works in Excel

Before you start setting up the rounding of fractional values, it is recommended to learn more about how this system works, which will be affected by changing its parameters.

It is not recommended to change the settings in situations where calculations involving fractions are performed very often. This can backfire on what you want.

One of the situations where it is recommended to make additional adjustments to the accuracy calculation is to add several numbers using only one decimal place. To understand what happens most often without additional configuration, you need to consider a practical example. The user needs to add two numbers – 4.64 and 3.21, while taking only one digit after the decimal as the basis. Procedure:

1. Initially, you need to select the cells with the numbers entered in them with the mouse or keyboard.
2. Press RMB, select the “Format Cells” function from the context menu.

3. After that, a window with settings will appear, in which you need to go to the “Number” tab.
4. From the list you need to select the “Numeric” format.
5. In the free field “Number of decimal places” set the required value.
6. It remains to save the settings by pressing the “OK” button.

However, the result will not be 7.8, but 7.9. Because of this, the user is likely to think that a mistake has been made. This fractional value was obtained due to the fact that by default Excel summed the full numbers, with all decimal places. But according to an additional condition, the user specified a number with only one digit after the decimal point for display on the screen. Because of this, the resulting value of 7.85 was rounded up, whereby 7.9 came out.

Important! To find out what value the program will take as a basis during the calculations, you need to click on the cell with the number of LMB, pay attention to the line where the formula from the cell is deciphered. It is in it that the value that will be taken as the basis for the calculations without additional settings will be displayed.

Setting the rounding precision

The way to configure the rounding of fractional values ​​​​for Excel (2019) – the procedure:

1. Go to the main menu “File”.

2. Go to the “Parameters” tab. You can find it at the very bottom of the page on the left side.
3. Select advanced settings.
4. To the right of the window that appears, find the block “When recalculating this book”, find the function “Set the specified accuracy” in it. Here you need to check the box.
5. After these steps, a small warning window should appear on the screen. It will indicate that by performing this action, the accuracy of the calculations in the tables may decrease. To save the settings, you must agree to the changes by clicking “OK”. Press “OK” again to exit settings.

When you need to turn off the exact rounding function or change it, you need to go to the same settings, uncheck the box or enter a different number of characters after the decimal point, which will be taken into account during the calculations.

How to adjust accuracy in earlier versions

Excel is updated regularly. It adds new features, but most of the main tools work and are configured in a similar way. When setting the rounding precision of values ​​in earlier versions of the program, there are minor differences from the modern version. For Excel 2010:

1. Go to the “File” tab on the main toolbar.
2. Go to options.
3. A new window will appear in which you need to find and click “Advanced”.
4. It remains to find the item “When recalculating this book”, put a cross next to the line “Set accuracy as on the screen.” Confirm changes, save settings.

Procedure for Excel 2007:

1. On the top panel with open spreadsheet tools, find the “Microsoft Office” icon, click on it.
2. A list should appear on the screen, from which you need to select the “Excel Options” item.
3. After opening a new window, go to the “Advanced” tab.
4. On the right, go to the group of options “When recalculating this book”. Find the line “Set accuracy as on the screen”, set a cross in front of it. Save changes with the “OK” button.

Procedure for Excel 2003:

1. Find the “Service” tab on the top main toolbar, go into it.
2. From the list that appears, go to the “Settings” section.
3. After that, a window with settings should appear, from which you need to select “Computations”.
4. It remains to check the box next to the “Accuracy as on screen” parameter.

Conclusion

If you learn how to set the rounding accuracy in Excel, this setting will help you perform the necessary calculations when, according to the conditions, only those numerical values ​​uXNUMXbuXNUMXbthat take into account one digit after the decimal point can be taken into account. However, we must not forget to turn it off for standard situations, when calculations should be as accurate as possible, taking into account all the numbers.