Use formulas and functions

Use Trace to fix formula errors

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Checking formulas for accuracy and finding the source of an error is hard when the formula uses precedent or dependent cells. To help, use Trace Dependents and Trace Precedents to graphically show the relationships between these cells and formulas with tracer arrows.

  • Dependent cells contain formulas that refer to other cells.
    For example, if cell D10 contains the formula =B5, cell D10 is a dependent of cell B5.

  • Precedent cells are referred to by a formula in another cell.
    For example, if cell D10 contains the formula =B5, cell B5 is a precedent to cell D10.

Trace cells that reference a cell (dependents)
  1. Select the cell for which the dependent cells need to be identified.

  2. On the Formulas tab, select Trace Dependents to display a tracer arrow to each cell that is dependent on the active cell.
    Trace Precedents

    • Blue arrows - show cells with no errors.

    • Red arrows - show cells that cause errors.

    • Black arrows - if the selected cell is referenced by a cell on another worksheet or workbook, a black arrow points from the selected cell to a worksheet icon Worksheet . The other workbook must be open before Excel can trace these dependencies.

  3. Select Trace Dependents again to identify the next level of cells that depend on the active cell.

    Note: At some point, a beep will be heard, otherwise the arrows will stop appearing.

  4. To remove tracer arrows one level at a time, starting with the dependent cell furthest to the active cell, select the arrow next to Remove Arrows on the Formulas tab, and then select Remove Dependent Arrows.

  5. To remove another level of tracer arrows, select Remove Dependent Arrows again.

    Note: You can use Ctrl + Shift + ] to highlight all the dependents of a cell.

Trace cells that provide data to a formula (precendents)
  1. Select the cell for which the precedent cells need to be identified.

  2. Select Formulas > Trace Precedents to display a tracer arrow to each cell that directly provides data to the active cell.
    Trace Precedents

    • Blue arrows - show cells with no errors.

    • Red arrows - show cells that cause errors.

    • Black arrows - if the selected cell is referenced by a cell on another worksheet or workbook, a black arrow points from the selected cell to a worksheet icon Worksheet . The other workbook must be open before Excel can trace these dependencies.

  3. Select Trace Precedents again to identify the next level of cells that provide data to the active cell.

    Note: At some point, a beep will be heard, otherwise the arrows will stop appearing.

  4. To remove tracer arrows one level at a time, starting with the precedent cell furthest to the active cell, select the arrow next to Remove Arrows on the Formulas tab, and then select Remove Precedent Arrows.

  5. To remove another level of tracer arrows, select Remove Precedent Arrows again.

Want more?

Discover more courses like this at LinkedIn Learning

Display the relationships between formulas and cells

So how do we check all the dependencies of a cell?

Clicking cell B3, going to the Formula tab on the ribbon, in the formula auditing group is an option called trace dependents.

Show arrows that indicate which cells are affected by the value of the currently selected cell.

Trace dependent, we see that immediately.

But don't stop there, keep clicking trace dependents, repeatedly.

If you have a speaker on, at some point you'll hear a beep, otherwise the arrows will stop appearing.

So, a change in cell B3 is going to have potential impact on all these other cells that have arrows in them.

Now that doesn't necessarily mean that every one will change, it depends upon the natures of the formulas.

But all of them have formulas that, in one way or another, relate back to cell B3.

And of course, many of these cells have formulas that get data from other cells as well.

As you look at this, something else unusual has occurred.

Here's a dashed line here.

It appears to be coming out of cell G5.

And it's pointing to an icon out here.

Think of that icon as being another work sheet.

As you double-click the dashed line, as I'm doing right now, it brings up a dialogue box and says that in this file, in another sheet called update values in cell A13, is a formula that gets data from cell G5 in this worksheet.

And G5 has a formula in it that gets data from cell B3.

We can double-click this or click it and click OK, either way.

This will take us to the other sheet.

Watch the sheet tabs at the bottom of the screen change.

We're on a different sheet right now, called update values.

And here's the cell, A13, lower left corner.

I'm double-clicking it.

That's a formula that's getting data from cell G5 in that worksheet that we just came from.

So, we'll press escape, move leftward back to the auditing tools sheet.

A change in cell B3 will impact not only the cells referred to on this worksheet, but also the cell on the other work sheet.

Now, without that feature, you would never know.

You could look at this worksheet for days, years, whatever.

You will never know just by looking at this worksheet that there's a formula elsewhere that's getting data from this sheet.

So that's certainly a handy tool.

If we want to be able to check the dependencies of other cells, first order of business probably is remove these arrow, or we'll have a huge number of arrows on the screen.

Now, almost as important sometimes, maybe more important is the idea that you've got a total and you'd like to know where the total is getting its data from.

So, the opposite of dependence is referred to as precedence.

I need to use this in the quarterly report.

I need to use this number, but I need to know where it's coming from.

I've got a formula here that gets data from either cell L11 or K27, but where do those cells get their data from, and so on and so on.

So working in the opposite direction, trace precedents.

I'm clicking it once, only two cells are referred to, but I'll keep clicking it, just as we did with trace dependents, repeatedly.

And here, working in the reverse direction, we see any of the cells that have blue dots in them, if altered, could impact our total on the bottom.

And one of our cells here, and slightly different in this situation, in cell F13.

This is getting data from another worksheet.

Now, here we do see this and we do see the formula refer to the other worksheet, so a little bit different than our example with trace dependents, but once again, we do see, we got a situation where a cell here is getting data from other cells.

And one of the other cells that's impacting the total here is coming from another sheet.

And so here too, when we see this dashed line, we'll double-click it.

And, we could click this, click OK, and go see the other formula, here.

There it is, right there.

Now, this is simply a raw number, but it's referred to by the formula on the other worksheet.

Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at linkedin.com/learning.

Enjoy one month of free access to LinkedIn Learning

Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.

Benefits
  • Get unlimited access to over 4,000 video courses.

  • Receive personal recommendations based on your LinkedIn profile.

  • Stream courses from your computer or mobile device.

  • Take courses for every level – beginner to advanced.

  • Practice while you learn with quizzes, exercise files, and coding windows.

  • Choose a plan for yourself or your entire team.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×