Excel Formulas – 4 Popular Ways to Debug Formulas

Writing a formula is just part of the job. Like a software coder, you need to fix bugs, improve the code, etc. Sometimes you need to go inside the formula content and not the result. I will show you 4 handy commands you can use to get into the code…

1) Pressing F2. You can then edit the formula content from within the cell. The benefit of this feature is that you don’t need to move your head up and down to the formula bar.

2) Editing the formula at the formula bar. This feature is useful when you have a long formula that needs to be read entirely without hiding the neighbor cells. You can get into the formula content without expanding it into the cell

3) Showing the formula instead of results. This option is not so known but very helpful. Imagine each cell as a formula bar. Use the shortcut (Ctrl + `) to alternate or activate the option this way…

  • Excel 2007 users go to Formulas>Formula auditing>Show formulas
  • Excel 2003 users go to Tools>Options>View>Window options>Formulas

4) Using the watch window to show how variables behave when you changes the inputs. This is a kind of  sensitive approach. You always see the impact of a change in the dependent values in the formula. The best part with this is that it allows you to monitor the cells you want.

  • Excel 2007 users go to Formulas>Formula auditing>Watch window
  • Excel 2003 users go to Tools>Formula auditing>Show Watch window

“Many studies indicate that the testing process can even take three times as long as the actual coding itself. The above features will be a companion for that time.