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.


Boost your LinkedIn profile

LinkedIn is No. 1 when it comes to business-related social media. Be prepared when an unexpected career opportunity arises by following these tips for keeping your LinkedIn page current.


Millennial women: How to woo a new generation of employees

Ambitious, educated, and tech-savvy, Millennial women make ideal employees. Win them over with flexibility and clear paths to advancement.


Belicia Cespedes: A CPA at 17

Through hard work and determination, Belicia Cespedes earned the credential before she was even eligible to vote.