When we use Excel spreadsheets to make data reports, in order to avoid the operation of a large number of decimals, we often use the method of rounding, but doing so may cause errors in numerical operations, and it is not easy to find the reason.

If the precision of the calculated value is not high, you can click “Format” – “Cell” on the Excel menu bar, set the number of “Decimal places” in the “Format Cell” window (as shown in Figure 1), and decide A few decimal places are reserved, and the excess digits will be rounded off.

However, this creates a problem. Suppose the value of A is 34.52, which is 35 after rounding; B is 67.8, which is 68 after rounding. It stands to reason that the sum of the two numbers after rounding should be 103, but why does Excel calculate 102? It turns out that this is Because when the numbers are added, Excel performs operations according to the numbers before rounding, so the actual and calculated values are different.

How to set Excel rounding operation to no longer error?

The easiest way to solve this problem is to click “Tools” – “Options” on the Excel menu bar, check the “Subject to display accuracy” item in the “Recalculation” tab (as shown in Figure 2), and then Click the “OK” button. At this point, the program will pop up a prompt message telling you that doing so will cause an error in the calculated value, and then click “OK”.

If the data you need to calculate is information such as accounting statements, it is best not to deal with it in this way.

After setting, the calculated value becomes correct, isn’t it very convenient?

How to easily view data records in Microsoft Excel?

I don’t know if you feel this way. When you view a row of records in an Excel data table, due to the large number of fields in the data table, you need to drag the horizontal scroll bar to read the contents of the entire table. Mistakes”, accidentally mistaking the content of the upper line or the lower line as the currently recorded data. This kind of data is handed over to the leader, but it needs to be approved. In fact, through the following lines of VBA code, you can easily view the records. The operation steps are as follows:

Open a worksheet (assuming it is “Sheet1”), click “Macro” in the “Tools” menu, select the “Visual Basic Editor” command, double-click the corresponding worksheet label name in the left window, and in the pop-up code window Enter the following VBA code in:

Private Sub WorksheetSelectionChange(ByVal Target As Range)

If Target.EntireRow.Interior.ColorIndex10 Then

Target.EntireRow.Interior.ColorIndex = 10Else

Target.EntireRow.Interior.ColorIndex = 0End IfEnd Sub

How to set Excel rounding operation to no longer error?

Close the code window and return to the Excel worksheet. If you need to view a row of records, just click any cell in the row, and the row will be automatically filled with green and highlighted (if you need to change to other colors, only You need to change the corresponding value in the code), click any cell in the row again to cancel the filling of the background color. In addition, multiple rows of records can be filled with backgrounds at the same time to facilitate viewing or comparison of data in tables.

Editing Tip: Although clicking the row number before the record can select the entire row of records, the method provided in this article is more convenient and faster in daily office.

Recommended reading:

How to log out of Fouad WhatsApp?

Batch add various symbols to data in Excel table?

Leave a Reply

Your email address will not be published. Required fields are marked *