Influence of the cell format on working of the SUM function
When working with the SUM () function in Excel, you should take into account the peculiarities of the data formats, that can influence on the totals in the calculations.
It is worth paying attention to the behavior of the formats it selves when working with different types of values. Under certain conditions, these types can be automatically changed and in different ways.
Errors of summation at comma instead of dot
Fill the cells with values as shown in the drawing (in the cell B2, a comma instead of a dot):
Select the range A1:B3. Then on the «HOME» tab, click the «Sum» tool (or press ALT + =), to automatically sum the columns separately.
A comma instead of a point for Excel is the default text. The SUM () function skips the text values when summing cells. As a result, we see different amounts.
This error is dangerous, especially when there are a lot of data in the table. Firstly, it is easy to admit, and secondly difficult to notice. Therefore Excel in the default format «General» aligns the text on the left side, and the numbers on the right. If the amounts do not converge, it is worth for all cells to set the format «General» to find where the dot is replaced by a comma. But there is a better way.
How to find the text in numerical values?
To quickly find all the cells where a dot instead of a comma, you should do the following:
- Select the «HOME» tab, select tool – «Find & Select» – «Go To» (or press the keyboard shortcut CTRL + G).
- In the window that appears, click on the «Special» button.
- In the «Selecting the group of cells» window, select the «Constants» option and tick «Text» only. Then click OK.
So the program will automatically select to all cells that contain text values. They do not need to be searched in the large tables and prices to correct the error.
If there are too many points, you can use the «Home» tool – «Find and Highlight» – «Replace» (or key combination CTRL + H). It will allow in Excel to replace comma to dot by automatically.
Attention. If a range was selected before the search for text values with the method described above, then the search will be limited to the same range. Therefore, before finding of the points, it is better to remove all the selections.
