Excel includes a number of very useful tools you can use to ensure your formulasincorporate the proper values.One of those tools give you the ability to determine which cells are used inwhich worksheet formulas.Cells can play two roles in a formula, as a precedent or as a dependent.A cell that is precedent supplies its value to the formula in another cell.A dependent is a cell that contains a formula and has other cellsproviding values to it.Displaying a cell's precedents indicates which other cells provide the formula's inputs.Now you can see the two cells here, B3 and B4 in this simple example, but sayyou had a more complex formula,you can still use this technique to determine where the values are coming from.

So, to display precedents, you go to the Formulas tab and then in the FormulaAuditing group, you click Trace Precedents.You'll see that we have cells B3 and B4 as advertised that are highlighted with a dot.There's a line with an arrow and it goes to the cell that contains the formulathat uses those two values.So that is how you trace dependents.When you're done doing the tracing, you can go back up to the Formula Auditing group, click Remove Arrows and the arrows disappear.So let's see how it works for dependents.

On this worksheet, we have Bonds, Notes and Loan Interest and we have a Total.This is a company's monthly debt service for the year 2009.If you want to see which cells use the value from cell B3 as part of its formula,you can click cell B3, again, on the Formulas tab and in the Formula Auditing group, click Trace Dependents.You'll see that once again you have the blue circle. The line draws an arrow tothe cell that uses the value as part of it's formula.To remove the arrow, you go up to the ribbon and click Remove Arrows.

Now tracing precedents and dependents also works for values that are on other worksheets.So, for example, here in cell B5, I have a loan interest and that is on theInterest worksheet, which is the one we looked at previously.If you want to see which cell contains that value, you would select it,click Trace Precedents.Tracer arrows can also show you when values have been drawn from other worksheets.So, for example, on this worksheet, we have cell B5, which draws its value fromthe Interest worksheet.

So, if you click cell B5 and then go up to Trace Precedents to see where itdraws its value from, Excel shows you this indicator, which tells you that thevalue is drawn from another worksheet.It can also be from another workbook entirely, but in this case it's just fromanother worksheet in the same workbook.So, to display that value, you click the line, the Go To dialog box appears andyou can select the location to which you want to go, click OK, and it takes youto the Interest worksheet, cell B6, which is where the value came from.Identifying a cell's precedents and dependents will help you discover andcorrect many formula inaccuracies.

Resume Transcript Auto-Scroll

Author

Released

8/25/2009

Numbers and financial data drives today's business world and Excel 2007: Financial Analysis can help decode this information. The proper understanding of these numbers, and the formulas behind them, can be the gateway to corporate and personal success. Microsoft MVP (Most Valuable Professional) Curt Frye teaches basic fluency in corporate finance, enabling users to see the meaning behind essential financial calculations. Curt explains how to review formulas to ensure they have the proper inputs, and shows how to interpret formula output. He also covers how to calculate leverage ratios and amortization and depreciation schedules, as well as forecast future growth. Exercise files accompany this course.

Topics include:

Building a financial worksheet with Pivot Tables

Reviewing financial statements through common-sized balance sheets

Calculating percentage change over time in financial statements

Determining profitability ratios and return on investments

Studying liquidity and activity ratios through an average collection period