Excel: Which Cells Flow into This Cell?

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I have a large formula, and I would like to visually see how the cell is calculated.

Strategy: One way to handle this is to select the cell and then press F2 to edit the cell. All the references in the formula will light up with different colors. If the precedent cell is in the visible portion of the window, the cell will be surrounded by a box of the same color as the formula.

Alternate Strategy: If you need a more permanent view of the calculations than pressing F2 provides, you can use the Formula Auditing menu to draw blue arrows from all the precedent cells. To do so, you select cell D32 and then select Formulas, Trace Precedents. Excel will draw blue arrows from all the cells that are referenced in the D32 formula. As shown near the bottom left of this figure, the arrow from the other worksheet icon indicates that at least one reference is on another worksheet. Double-click the arrow to see a list of those off-sheet precedents.

If you click Trace Precedents enough times, Excel will trace the precedents of all the arrowed cells. After a few iterations of the command, you will see that nearly all the cells factor in to the calculation.

  1. Trace Precedents.