# HOW TO TROUBLESHOOT A FORMULA IN EXCEL?

## INTRODUCTION

Sometimes when using the complex formula we might do some mistakes which will bring out wrong results.

For such cases, there are few functions present in EXCEL which will help in troubleshooting these cases.

## STEPS TO TROUBLESHOOT IN EXCEL

### TRACE PRECEDENTS:

It helps to trace the cells which are dependent on the current selected cell value. This options shows all the cells which are involved in the derivation of result in the current cell.

Example- Suppose we added 8 to a value in a cell. Now keep the cursor in result and click TRACE PRECEDENTS.

We added 8 to 456. So the result shows that the value has derived by acting upon the CELL SHOWN.

### TRACE DEPENDENTS:

It shows all the cells which contain the values, whose values are dependent on the selected cell value.

Suppose we select a cell and use it in two different formulas. Now select the first cell and click TRACE DEPENDENTS. The picture will make it more clear.

### REMOVE ARROWS:

It removes the arrows which appeared in the trace dependents and trace precedents as per the picture.

### SHOW FORMULAS:

This option shows the formulas in the cell itself. Select the cell and click SHOW FORMULA OR PRESS CTRL+`. It’ll show all the formulas in the sheet.

Click it again to hide the formula and show values.

### ERROR CHECKING:

This option gives the facility of checking any error. Select the error cell and click ERROR CHECKING. The dialog will show help on the error which can be rectified.

We created a problem in a cell by putting a text in a cell whose value was used in the result. Selected the cell and clicked ERROR CHECKING.

The screen shows its observation.

### EVALUATE FORMULA:

This is one of the very useful tool provided in Excel. It shows the steps of the evaluation. Many times especially in complex formulas, it might happen that the result is not as per our expectations. In that case this option is helpful.

Lets figure out the evaluation steps of the following result.

#### STEPS TO EVALUATE FORMULA:

• SELECT the result cell.
• Click EVALUATE FORMULA.
• The dialog box will open and show the formula.
• Click EVALUATE and the formula will be replaced by values one by one. Click evaluate again and the whole process of evaluation will be shown. The process of the case discussed is shown in pictures.

### WATCH WINDOW:

THE WATCH WINDOW:

Its a watch we keep on few cells. We can add a watch on the values.

It is meant for long spread data which is not visible at all the times. We keep watch on those cells whose value is to be checked again and again and we don’t want to scroll the window which will be wasting our time.

The picture shows the scenario.

The AH12 cell is not in the window shown. The value of AH12 is depending upon the value of I12. So the watch will always be visible even if the window portion of AH12 is not visible.

### CALCULATION OPTIONS:

It gives the option of doing the calculation automatic or manual.

In manual calculation, the selected formula will be calculated only after the button CALCULATE NOW will be pressed. CALCULATE SHEET will calculate all the formulas.

In AUTOMATIC mode all the calculation will happen instantly as by default is done in EXCEL.

#### STEPS FOR CALCULATION OPTION:

• SELECT the result cell.
• Click EVALUATE FORMULA.
• The dialog box will open and show the formula.
• Click EVALUATE and the formula will be replaced by values one by one. Click evaluate again and the whole process of evaluation will be shown. The process of the case discussed is shown in pictures.