In this article you will learn everything about formula auditing. It is especially about Trace Precedents and Trace Dependences which can help you to understand what is going on in the worksheet.
In the Formulas tab you can see a section called Formula Auditing.
Trace Precedents - which cells using this formula?
Commands contained therein provide an analysis of the formulas from your workbook. For example, in a situation when you are in a cell that contains a specific formula, and you want to check the cell to which it refers. Then you can use the Trace Precedents command. This will display the arrows from all the cells that are used in the formula to cells analyzed.
Excel tells us, from which the data depends on our formula by selecting the color cell source. You can see an example below. Below cell C2 is directly dependent on B2 and C12.
Using the same function again, Excel puts another arrow until the all the cells with formulas that have an influence on our cell, will be referred to their predecessors.
Tracking dependencies - which formulas use this cell?
The dependency tracking shows that cells depend indicated, the opposite of tracking predecessors. To do it use the Trace Dependents button.
The following example shows how the cells change, if you change the value in cell B2.
You can remove arrows clicking Remove Arrows button.
This button will show fomulas in cells instead of results of formulas. In ths example it will looks like that:
Formula Auditing greyed out
Sometimes it may happen that formula auditing button will be greyed out. There might be a few causes for that.
First check if your there is worksheet protection enabled.
File - Options - Advanced - Display options for this workbook - For objects show - change it from Nothing (hide objects) to All.
Further reading: SmartArt Array Formulas Display Cells Containing Formulas