Dividing one number with another is a common task in Excel. Almost every person has the experience of doing the task. However, sometimes this easy task doesn’t go smoothly and causes huge trouble. In this article, we will demonstrate to you several processes of how to fix division formula not working in Excel. If you are also facing any difficulties to do the division operation, follow the context.
What to Do If Division Formula Is Not Working in Excel: 6 Possible Solutions
For demonstrating the solutions in this article, we will consider a dataset of 10 students of an institution and the examination marks of a text. The name of the students is in column B, their obtained marks are in column C, the total marks of the examination are in column D, and the percentage will show in column E. Thus, we can claim that our dataset will be in the range of cells B4:E14. Through the calculation of percentage, we will show you how you will fix the problems associated with the division formula.
1. Set Cell Format as Number
Sometimes an unexpected cell format in Excel will hamper our capability to do mathematical operations. As a result, the tools cannot recognize the cell value and do the desired calculation. If you look at our dataset the percentage value of cell E8 is showing a #VALUE! error.
To find the issue let’s select the cells C8, D8, and E8 individually to check their cell format in the Number group. You will see that the cell format of D8 and E8 is shown as the Number.
However, the cell format of C8 is shown as a Text.
As a result, the division operator cannot able to recognize the cell value and do its job. Now, we will demonstrate to you how you will fix the division formula not working issue.
📌 Steps:
- First of all, select cell C8.
- Now, in the Number group, select the drop-down arrow of the Number Format box.
- Then, change the cell format Text to Number.
- You will see the problem will be solved and cell E8 will get the value of the percentage.
Finally, we can say that our method worked perfectly and we are able to solve the division formula not working issue.
2. Changing Calculation Options
Sometimes the change of calculation mode also creates trouble for us at the time doing division. We will use the same dataset in this case. For calculating the value of percentage we will divide the value of C5 by the value of cell D5. You can write down the following formula into cell E5.
=C5/D5
Now, we can use the Fill Handle icon to copy the formula up to cell E14. However, you will see that the same formula will copy in every row. It doesn’t change with the change of each row.
To find the reason, we check the Calculation Options of our Excel spreadsheet and we found that our calculation mode is set in Manual.
In this procedure, we will show how you can fix this issue.
📌 Steps:
- First, in the Toolbar, select Formula > Calculation Options.
- After that, change the calculation option Manual to Automatic.
- You will see that the division formulas will change automatically for each row.
In the end, we can say that our process worked effectively and we are able to fix the division formula issue.
3. Using Paste Special Command
Paste Special option is another way that can help us in dividing a set of data with a single value. In this approach, we will divide each value of column C by the value of column D. The steps of this approach are given below:
📌 Steps:
- At first, select the entire range of cells C5:C14.
- Then, press ‘Ctrl+C’ on your keyboard to copy the data and paste them in the range of cells E5:E14.
- Now, select cell D5:D14 and right-click with your mouse, and select the Copy option.
- Next, select the entire range of cell E5:E14.
- In the Home tab, select the drop-down arrow of the Paste option from the Clipboard group.
- After that, select the Paste Special option.
- A dialog box entitled Paste Special will appear.
- Choose the Operation option None to Divide and keep the Paste option in All.
- Click OK.
- Within a second all the values of column E will be divided by the value of column D.
Thus, we can say that our working approach worked perfectly and we are able to do division operations.
Read More: How to Divide One Column by Another in Excel
4. Resolving #DIV/0! Error
This error is a quite common type of error that happens at the time of division operation. If any cell with a value divides with an empty cell, thus this type of error occurs. Most of the time, it happens when we mistakenly delete any cell value. So, we can say that this is mainly a human error. In our dataset, you will find cell E9 is showing us a #DIV/0! error. To resolve the issue, we select the cell and check the formula of cell E9. The formula of cell E9 is C9/D9 and we can see that cell D9 doesn’t contain any value.
The solution to resolving this issue is given below:
📌 Steps:
- Select cell D9.
- After that, write down the value in cell D9 manually and press Enter. If the cell contains the same value as the previous cell, you can also drag the Fill Handle icon to copy the data.
- You will see the error will vanish and the cell will show the desired value.
So, we can say that our process worked perfectly and we are able to fix the issue division formula not working in Excel.
5. Solving #REF! Error
The #REF! error is another common type of error, found often in our daily Excel work. This is also a human error. This error occurs when we input the wrong cell into our formula. In our dataset, you can see that all the range cells E5:E14 show us a #REF! error. As we check the formulas of those cells, we find the formula of cell E5 is showing the following formula:
=C5/#REF!
For all the range of cells E5:E14, the cells show us the same type of formula.
It means that we mistakenly delete the denominator cells of our formula. Here, the steps to fix this problem are explained:
📌 Steps:
- At the beginning of this method, select the entire column D.
- Now, in the Home tab, click Insert from the Cells group. Besides it, there are several ways to insert a new column in Excel.
- Write down the value 100 in cell D5 and copy it up to cell D14.
- Then, in cell E5, edit the existing formula with the following formula given below:
=C5/D5
- Press the Enter key.
- After that, double-click on the Fill Handle icon to copy the formula up to cell E15.
- Finally, the error will go out and we get our desired value.
In the end, we can say that our process worked successfully and we are able to fix the issue division formula not working in Excel.
Read More: How to Divide a Column by a Number in Excel
6. Changing Incorrect Sheet or Workbook Reference
Sometimes inputting an incorrect sheet or workbook in the cell reference cause trouble in the division operation. To avoid this type of error we have to input our data carefully. To demonstrate this type of error and its solution, we consider two different sheets. In the sheet, titled Marks, we keep the obtained marks of the students.
In the other sheet, we evaluate the percentage. If we take a look at our dataset, we see that cell C8 is showing us a value of 0. However, in the Marks sheet, cell C8 contains a value of 74. Selecting cell C8 illustrates to us that the cell has the following formula into it.
=Marks!D8/100
Whereas cell D8 in the sheet Marks doesn’t contain any value. Now, we will describe the solution of how to fix this issue:
📌 Steps:
- Select cell C8 and press Delete to delete all the formulas of that cell.
- Now, write down the following formula into the cell.
=Marks!C8/100
- Press the Enter key on your keyboard.
- Thus, you will get the correct value of the percentage in that cell.
Lastly, we can say that our formula worked successfully and we are able to fix the issue division formula not working in Excel.
Read More: How to Divide in Excel for Entire Column
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to fix the division formula not working in Excel. If you have any further queries or recommendations, please share them with us in the comments section below. Keep learning new methods and keep growing!
Related Articles
- How to Divide for Entire Row in Excel
- Divide a Number by a Percentage in Excel
- How to Add and Then Divide in Excel
- Formula for Addition Subtraction Multiplication and Division in Excel
- How to Divide Without Using a Function in Excel
- Division Formula with Absolute Reference in Excel
- How to Divide a Group of Cells by a Number in Excel
- How to Divide with Decimals in Excel
- Division Formula in Excel for Multiple Cells
