**AutoSum** helps to sum a range automatically. If you select a range that you want to sum with a blank cell under it or on the right side of the range and then select **AutoSum** then it will return the summation of the selected range in the blank cell. But sometimes you may face a situation where **AutoSum is not working and returns 0** in Excel. The main objective of this article is to explain the reasons behind this problem and show how you can solve them.

## Download Practice Workbook

You can download the practice workbook from here.

## 3 Reasons with Solutions When AutoSum Is Not Working in Excel and Returns 0

To explain this article, I have taken the following dataset. It contains the **Subject **name and **Marks**. Here, I used **AutoSum** to get the **Total**. But in the following picture, you can see that the **AutoSum** **is returning 0**. I will explain 3 different reasons with solutions why** Autosum is not working and returns 0** in Excel.

### Reason-01: Numbers Stored as Text in Excel

Sometimes, **numbers** are **stored as text.** This is the first reason why Excel **AutoSum is not working and returns 0**. In the following image, you can see that there is an **apostrophe (â€˜) **before the number and for this reason, the **numbers** are **stored as text** here. And as the numbers are stored as text **AutoSum** returns **0 **here. Now, I will show you **5 **different solutions to this problem.

#### Solution-01: Use Error Warning to Convert to Number

Here, the dataset shows an **error warning** because the **numbers** are **stored as text** here. In this first solution, I will use the **error warning** to convert the **texts to numbers**. Letâ€™s see the steps.

**Steps:**

- First, select the cells where the numbers are stored as text. Here, I selected the cell range
**C5:C9**.

- Then, click on the
**Error Warning**. - Next, select
**Convert to Number**.

- After that, you will see that the
**numbers are stored as numbers**. Also, the**AutoSum**is working properly and returns the correct result.

**Read More:**** How to Use AutoSum In Excel (4 Easy Ways)**

#### Solution-02: Apply Mathematical Operations

There are different types of** mathematical operations**. Here, I will apply the **Multiplication** operation to convert the **numbers that are stored as text to numbers**. Letâ€™s see how you can do it.

**Steps:**

- In the beginning, make a column where you will
**convert the numbers**.

- Afterward, select the first cell of that column. Here, I selected cell
**D5**. - Next, in cell
**D5**write the following formula.

`=C5*1`

- Then, press
**Enter**to convert it to a number.

- Further, drag the
**Fill Handle**down to copy the formula to the other cells.

- After that, you will see that you have copied the formula to the other cells and got your desired output.

- Finally, you will see that the
**AutoSum**is working and returns the correct result for this range.

#### Solution-03: Employ Paste Special Option

The **Paste Special** option in Excel allows you to select how you want to display the content you copied. In this solution, I will use the **Paste Special **option to **convert the numbers**. Let me show you the steps.

**Steps:**

- Firstly, select a
**blank cell**from the worksheet. Here. I selected cell**B12**. - Secondly,
**copy**the cell by pressing**Ctrl + C**on your keyboard.

- Thirdly, select the cells where you want to
**convert the numbers**. - After that,
**Right-Click**on the selected cells. - Then, select
**Paste Special**.

- Next, the
**Paste Special**dialog box will appear. - Select
**Values**from**Paste**. - Afterward, select
**Add**. - Then, select
**OK**.

- Finally, you will see that the numbers are converted and the
**AutoSum**is working properly.

#### Solution-04: Apply VALUE Function

**The VALUE function** is one of the text functions of Excel. It returns the value of a text. If the text matches a specific format then the function returns the value in that format. Here, I will use the **VALUE** function to **convert the numbers** that are stored as text to numbers. Letâ€™s see the steps.

**Steps:**

- First, select the cell where
**AutoSum**is not working and returns**0**. Here, I selected cell**C10**. - Then, in cell
**C10**write the**VALUE**function in the**AutoSum**like the following formula.

`=SUM(VALUE(C5:C9))`

- In the end, press
**Enter**and you will see that the formula is working and returning the correct result.

#### Solution-05: Use Text to Columns Wizard

Here, I will use the **Text to Columns Wizard** to solve when Excel **AutoSum is not working and** **returns 0**. I will convert the text to numbers by using the **Text to Columns Wizard**. Letâ€™s see how you can do it.

**Steps:**

- Firstly, select the cells where the
**numbers are stored as text**. - Secondly, go to the
**Data**tab. - Thirdly, select
**Text to Columns**.

- After that, the
**Convert Text to Columns Wizard â€“ Step 1 of 3**will appear. - Make sure
**Delimited**is selected. - Then, select
**Next**.

- Next, the
**Convert Text to Columns Wizard â€“ Step 2 of 3**will appear. **Check Tab**from**Delimiters**.- Afterward, select
**Next**.

- Further, the
**Convert Text to Columns Wizard â€“ Step 3 of 3**will appear. - Select
**General**. - Lastly, select
**Finish**.

- Finally, you will see that you have converted the numbers and the
**AutoSum**is working properly and returning the correct result.

**Read More:** **How to Autosum Column in Excel (4 Easy Methods)**

### Reason-02: Non-Numeric Characters in Number

The second reason when Excel **AutoSum is not working and returns 0** is entering **non-numeric** **characters** in **numbers**. If you enter a **non-numeric character** in a number on purpose or by mistaken then the number is **converted to text**. In the following image, you can see that I have entered the **Number Sign (#) **in the numbers. As a result, the **AutoSum** is not working here and returns **0**. Now, I will show you how you can solve this problem.

#### Solution: Employ Find and Replace Feature

The **Find and Replace** feature will help you to find something in an Excel sheet and replace it with whatever you want. Here, I will use this feature to remove the **non-numeric character **from the numbers. Letâ€™s see the steps.

**Steps:**

- In the beginning, select the cells where you have entered the
**non-numeric character**with numbers.

- Then, press
**Ctrl + H**from your keyboard, and the**Find and Replace**dialog box will appear. - Next, write the
**non-numeric character**you want to remove in the**Find what**section. Here, I wrote the**Number Sign (#)**. - And then, leave the
**Replace with**section blank. - Afterward, select
**Replace All**.

- Next, another
**dialog box**will appear showing how many replacements you have done. - Select
**OK**.

- Here, you will see that the
**non-numeric characters are removed**and the**AutoSum**is working properly.

### Reason-03: Circular References in AutoSum

**Circular References** in Excel occur when a formula refers to its own cell. **Circular References** is another reason why Excel **AutoSum is not working and returns 0**. In the following picture, you can see that the numbers have no errors in them. But yet the **AutoSum** is returning** 0**. **Circular References **can be the reason here.

Letâ€™s see how you can check for** Circular references**.

- To begin with, go to the
**Formulas**tab. - Then, select the drop-down option for
**Error Checking**. - Next, select
**Circular References**. - Consequently, you will be able to see the cells that are causing the
**Circular References**. Here, cell**C10**is causing the error.

#### Solution: Remove Circular References

To solve the **Circular References** error you will have to remove the cells that are causing the error from the formula and then you will be good to go. Letâ€™s see the steps.

**Steps:**

- Firstly, select the cell where the
**AutoSum is not working and returns 0**. - Secondly, remove the cell that is causing the error. Here, I removed cell
**C10**and got the following formula.

`=SUM(C5:C9)`

- After that, press
**Enter**and you will see that the**AutoSum**is working and returns the proper result.

## Practice Section

Here, I have provided a practice sheet for you to practice why Excel **AutoSum is not working and returns 0**.

## Conclusion

In this article, I tried to cover why Excel **AutoSum is not working and returns 0 **with some solutions. Here, I explained **3** reasons with solutions. I hope this article was clear to you. For more articles like this stay connected with **ExcelDemy**. Feel free to comment if you face any problems.