Accuracy as on the screen in Excel: how to set

Quite often, users who make calculations in Excel do not realize that the displayed numerical values ​​in the cells do not always agree with the data that the program uses to make calculations. It’s about fractional values. The fact is that the Excel program stores in memory numerical values ​​containing up to 15 digits after the decimal point. And despite the fact that, say, only 1, 2 or 3 digits will be displayed on the screen (as a result of the cell format settings), Excel will use the full number from memory for calculations. Sometimes this leads to unexpected outcomes and results. To prevent this from happening, you need to adjust the rounding accuracy, namely, set it to the same as on the screen.

How rounding works in Excel

First of all, you need to take into account the fact that it is better not to use this setting unnecessarily. It is worth thinking carefully and deciding for yourself whether it makes sense to set the accuracy as on the screen or not, since quite often when carrying out calculations with a large number of fractional numbers, the so-called cumulative effect occurs, which reduces the accuracy of the calculations performed.

It is worth setting the accuracy as on the screen in the following cases. For example, let’s say we want to add the numbers 6,42 and 6,33, but we only want to display one decimal place, not two.

To do this, select the desired cells, right-click on them, select the “Format Cells ..” item.

Being in the “Number” tab, click on the “Numeric” format in the list on the left, then set the value to “1” for the number of decimal places and click OK to exit the formatting window and save the settings.

After the actions taken, the book will display the values ​​6,4 and 6,3. And if these fractional numbers are added, the program will give the sum 12,8.

It may seem that the program does not work correctly and made a mistake in the calculations, because 6,4 + 6,3 = 12,7. But let’s figure out if this is really the case, and why such a result turned out.

As we mentioned above, Excel takes the original numbers for calculations, i.e. 6,42 and 6,33. In the process of summing them up, the result is 6,75. But due to the fact that before that one decimal place was specified in the formatting settings, the resulting cell is rounded accordingly, and the final result is displayed equal to 6,8.

To avoid such confusion, the optimal solution is to set the rounding accuracy as on the screen.

Note: In order to find out the original value that is used by the program for the calculation, click on the cell containing the numeric value, then pay attention to the formula bar, which will display the full number stored in the program’s memory.

How to adjust the accuracy as on the screen

First, let’s figure out how the rounding accuracy is configured as on the screen in the version Excel 2019.

1. We go to the “File” menu.
2. Click on the item “Settings” in the list on the left at the very bottom.
3. An additional window with program parameters will open, on the left side of which we click on the “Advanced” section.
4. Now, on the right side of the settings, look for a block called “When recalculating this book:” and check the box next to the “Set the specified accuracy” option. The program will warn us that the accuracy will be reduced with this setting. We agree to this by clicking the OK button and then OK again to confirm the changes and exit the options window.

Note: If it becomes necessary to disable this mode, go to the same parameters and simply remove the corresponding checkbox.

Adjusting Rounding Precision in Earlier Versions

Despite the constant updates of the Excel program, many of the basic functions and the algorithm for using them change slightly or remain the same so that users, after switching to a new version, do not experience difficulties getting used to the new interface, etc.

In our case, the algorithm for setting the accuracy as on the screen in earlier versions of the program is almost the same as what we considered above for the 2019 version.

Microsoft Excel 2010

1. Go to the “File” menu.
2. Click on the item with the name “Settings”.
3. In the settings window that opens, click on the “Advanced” item.
4. Put a tick in front of the option “Set accuracy as on the screen” in the settings block “When recalculating this book”. Again, we confirm the adjustments made by clicking on the OK button, taking into account the fact that the accuracy of the calculations will be reduced.

Microsoft Excel 2007 and 2003

Versions of these years, according to some users, are already outdated. Others consider them quite convenient and continue to work in them to this day, despite the emergence of new versions.

Let’s start with the 2007 version.

1. Click on the “Microsoft Office” icon, which is located in the upper left corner of the window. A list should appear in which you need to select a section called “Excel Options”.
2. Another window will open in which you need the “Advanced” item. Next, on the right, select the “When recalculating this book” group of settings and check the box next to the “Set accuracy as on screen” function.

With an earlier version (2013), things are somewhat different.

1. In the top menu bar you need to find the “Service” section. After it is selected, a list will be displayed in which you need to click on the “Options” item.
2. In the window that opens with the parameters, select “Calculation” and then check the box next to the “Accuracy as on screen” option.

Conclusion

Setting the accuracy as on the screen in Excel is quite useful, and in certain situations, an indispensable function that not every user knows about. It will not be difficult to make the appropriate settings in any of the versions of the program, since there is no fundamental difference in the action plan, and the differences are only in modified interfaces, in which, nevertheless, continuity is preserved.