excel sums to 0 all the time

excel sum equal to 0 how

excel sum equal to 0 reasons are as follows:

1, the format error

The numerical sum of the results of 0, the fundamental reason is still out of the cell problem, want to realize the numerical sum and show the correct results, we have to enter the correct data in addition to the need to set up corresponding to the format. format.

The sum of the numerical value of the result is 0 is because the format of the value of the text format, in the text format of the value, he is equivalent to Chinese characters or letters and other text data, and text data and his results must be 0, in excel, the difference between the text value of the most intuitive way is to look at the data in the upper left corner of the green triangle if there is a which this data is the text format of the value, the sum of his data is 0. text format values, the results of his sum must be 0.

2, there are invisible characters in the cell

Another situation is that there are invisible characters in the cell, the most common is the space, the following chart, the upper left corner of the data does not have a triangle symbol but we sum him the results are still 0, this is because there is a space behind the data when there is a space. excel will automatically set the format of this cell as a text format, so that we say it and the result is also 0.

Solution

The easiest way is to use the breakdown of the format of the cell into a regular format, in the regular format, excel will automatically recognize the type of input data, if the input cell data are numerical value then it will be recognized as a numerical format of the data. At the same time, the breakdown can also achieve the effect of rapid cleaning of data, when we use the breakdown, the cell will be removed from the invisible characters, will be formatted as a regular format, but it also has a disadvantage is that it must be a column by column to change the data.

If the cell format is regular or text format, in the summation operation, generally do not get the correct results, so if the summation operation is the result of 0, then you can check whether the cell format is a numerical value.

Spreadsheet summation appears to be the value has been 0 why

Explaining the form data contains hidden strings and can not be summed up to calculate.

1, if the numbers in Excel are text numbers, as shown in column B of the following chart.

2, then after making a pivot table, as shown below.

3, even if the numeric field is set to sum, as shown below.

4. The result of the summation is still 0, as shown below.

5, only the data source in the data columns, by disaggregation and other methods, the text figures will be converted into numerical figures, as shown below.

6, and then refresh the pivot table, as shown below.

7, so that the normal summation, as shown below.