A complicated inheritance

Q: I inherited an Excel workbook containing lengthy formulas, and I am having trouble determining exactly how these formulas work. Is there a magic trick for figuring out how complicated formulas work?

A: Excel provides a couple of tools that might help. The first tool is Evaluate Formula. To use this tool, highlight the complicated formula, then from the Formulas tab’s Formula Auditing group, select Evaluate Formula. This action will display the formula in the Evaluate Formula dialog box (pictured below).

With each click of the Evaluate button, Excel solves the next step of the formula to help you follow and understand the logic.

A second tool that might help reveal a formula’s logic is Trace Precedents. To use this tool, highlight the complicated formula, then from the Formulas tab’s Formula Auditing group, select Trace Precedents. This action inserts arrows depicting the flow of data from its source to the formula.

Click the Trace Precedents tool a second time to insert additional arrows depicting the flow of data to the second level of data (preceding the initial first level of source data). If desired, continue clicking Trace Precedents until all data flowing to the complicated formula is mapped to the original values entered in the workbook. This arrow mapping might help you better follow the flow of data through the workbook. (Note: You must place your cursor on a single cell for Trace Precedents to work. It won’t work if you select multiple cells. This means you must move your cursor to each cell where you would like to trace the data flow.)

For example, examining the flow of data in the screenshot pictured below reveals that the total formulas on rows 59 and 61 do not line up with the same columns containing the data (i.e., the January totals are contained in the February column, and so on). This might explain why the reader was having trouble making sense of these formulas.