Tracing errors like #VALUE! or #DIV/0! or #NUM! in Excel

If you have an #VALUE, #DIV/0! or #NUM! error in Excel, it could be as a result of a direct link to the cell, or it could be that somewhere right in the beginning there is a problem. You may need to track back through all the cells to find where the problem cell is. There is a tool for tracing errors like #VALUE!, #DIV/0! or #NUM! direct to the problem cell.

#VALUE! or #DIV/0! or #NUM! sourceĀ in the same sheet

So below, we have a #VALUE error in the IRR calc, but where is the real problem? You can click on the cell and then CLICK FORMULAS.ERROR CHECKING and then TRACE ERROR.

Tracing errors like #VALUE! or #DIV/0! or #NUM!

You will see that a red line appears and it takes you straight to the source of the error. In this case one of the inputs has a divide by zero in it. You can correct this.

Tracing errors like #VALUE! or #DIV/0! or #NUM!

#VALUE! or #DIV/0! or #NUM! source in a different sheet

In the above case, the problem cell is in the same sheet, so Excel takes you straight to it. However, if the problem is in a separate sheet you need to trace a bit back yourself.

As shown below, the trace error ends at a cell that is linked to another sheet (note the dashed line showing a link to another sheet). You need to follow these links to see where the problem is (learn how to navigate using this dashed line in the Fundamentals course). You may need to run the trace error again on the other sheet but it will be quicker than following everything manually.

Tracing errors like #VALUE! or #DIV/0! or #NUM!

Learn how to get formulas to ignore these error message with IFERROR (or the older but still useful ISERROR).

Difference between IFERROR and ISERROR in Excel

How to safely delete sheets so there are no REF errors

SUMIFS returns #value when linked workbooks are closed

How to remove too many cell formats in Excel