As a spreadsheet application, Excel is one big calculator that also displays numeric data in tables and graphs. The vast majority of Excel users will need to utilize the application’s SUM function (or formula). As the SUM function adds numbers up, it’s probably Excel’s most widely utilized formula.
The SUM function will never miscalculate numbers. However, SUM function cells don’t always display the expected results.
Sometimes they might display error messages instead of numeric values. In other cases, SUM formula cells might just return a zero value. These are a few ways you can fix an Excel spreadsheet not adding up correctly.
How can I fix Excel SUM functions that don’t add up?
1. Check the syntax of the SUM function
First, check you’ve entered the SUM function in the formula bar with the right syntax.
The syntax for the SUM function is:
- =SUM(number 1, number 2).
Number 1 and number 2 can be a cell range, so a SUM function that adds the first 10 cells of column A will look like this: =SUM(A1:A10). Make sure there are no typos of any description in that function which will cause its cell to display a #Name? error as shown directly below.
2. Delete spaces from the SUM function
The SUM function does not add up any values when there are spaces in its formula. Instead, it will display the formula in its cell.
To fix that, select the formula’s cell; and then click in the far left of the function bar. Press the Backspace key to remove any spaces before the ‘=’ at the beginning of the function. Make sure there aren’t spaces anywhere else in the formula.
3. Widen the formula’s column
If the SUM formula cell displays #######, the value might not fit within the cell. Thus, you might need to widen the cell’s column to ensure the whole number fits.
To do that, move the cursor to the left or right side of the SUM cell’s column reference box so that the cursor changes to a double arrow as in the snapshot directly below. Then hold the left mouse button and drag the column left or right to expand it.
4. Remove text formatting from cells
The Excel SUM function will not add up any values that are in cells with text formatting, which display text numbers on the left of the cell instead of the right side. To ensure all cells within a SUM formula’s cell range have general formatting. To fix this, follow these steps.
- Select all the cells within its range.
- Then right-click and select Format Cells to open the window shown directly below
- Select General in the Category box.
- Click the OK button.
- The cells’ numbers will still be in text format. You’ll need to double-click each cell within the SUM function’s range and press the Enter key to switch its number to the selected general format.
- Repeat the above steps for the formula’s cell if that cell displays the function instead of a value.
5. Select the Add and Values Paste Special options
Alternatively, you can add values in cells with text formatting without needing to convert them to general with this trick.
- Right-click an empty cell and select Copy.
- Select all the text cells with the function’s cell range.
- Click the Paste button.
- Select Paste Special to open the window shown directly below.
- Select the Values and Add options there.
- Click the OK button. Then the function will add the numbers in the text cells.
Those are a few of the best ways you can fix Excel spreadsheet formulas that aren’t adding up correctly. In most cases, the SUM function doesn’t add up because it hasn’t be entered right or because its cell reference includes text cells.
If you know of any other way to fix this Excel-related issue, leave a message in the comment section below so othe users can try it.
RELATED ARTICLES TO CHECK OUT:
- How to fix an Excel spreadsheet that’s not auto calculating
- How to fix Excel’s sharing violation error
- Microsoft Excel is trying to recover your information error