PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

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.

BUTTON LOCATION FOR TROUBLESHOOTING IN EXCEL

TROUBLESHOOTING BUTTON EXCEL
TROUBLESHOOTING SECTION

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.

TRACE PRECEDENTS EXCEL
TRACE PRECEDENTS IN EXCEL

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.

TRACE DEPENDENTS IN EXCEL
TRACE DEPENDENTS IN EXCEL

REMOVE ARROWS:

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

REMOVE ARROWS IN EXCEL
REMOVE ARROWS IN EXCEL

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.

ERROR CHECKING IN EXCEL
ERROR CHECKING IN EXCEL

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.

ERROR CHECKING EXCEL
ERROR CHECKING IN EXCEL

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.
EVALUATE EXCEL
EVALUATE IN EXCEL

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.

WATCH WINDOW EXCEL
WATCH WINDOW

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.

CALCULATE OPTIONS IN EXCEL
CALCULATE OPTIONS DEMONSTRATION

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.

OTHER WAYS TO REACH THIS ARTICLE

  •  TROUBLESHOOTING A FORMULA, FINDING PROBLEM IN FORMULA IN EXCEL, FIND ERROR IN FORMULA STEP WISE STEP

YOU MAY LIKE

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

gyankosh060309@gmail.com

Like this:

Like Loading...

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

%d bloggers like this: