Display the relationships between formulas and cells
Checking formulas for accuracy or finding the source of an error may be difficult if formula uses precedent or dependent cells:
Precedent cells — cells that are referred to by a formula in another cell. For example, if cell D10 contains the formula =B5, then cell B5 is a precedent to cell D10.
Dependent cells — these 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.
To assist you in checking your formulas, you can use the Trace Precedents and Trace Dependents commands to graphically display and trace the relationships between these cells and formulas with tracer arrows, as shown in this figure.
Follow these steps to display formula relationships among cells:
Click File > Options > Advanced.
Note: If you are using Excel 2007; click the Microsoft Office Button
In the Display options for this workbook section, select the workbook and then check that All is chosen in For objects, show.
To specify reference cells in another workbook, that workbook must be open. Microsoft Office Excel cannot go to a cell in a workbook that is not open.
Do one of the following.
Follow these steps:
Select the cell that contains the formula for which you want to find precedent cells.
To display a tracer arrow to each cell that directly provides data to the active cell, on the Formulas tab, in the Formula Auditing group, click Trace Precedents
Blue arrows show cells with no errors. Red arrows show cells that cause errors. 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
To identify the next level of cells that provide data to the active cell, click Trace Precedents
To remove tracer arrows one level at a time, begin with the precedent cell furthest away from the active cell. Then, on the Formulas tab, in the Formula Auditing group, click the arrow next to Remove Arrows, and then click Remove Precedent Arrows
Follow these steps:
Select the cell for which you want to identify the dependent cells.
To display a tracer arrow to each cell that is dependent on the active cell, on the Formulas tab, in the Formula Auditing group, click Trace Dependents
Blue arrows show cells with no errors. Red arrows show cells that cause errors. 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
To identify the next level of cells that depend on the active cell, click Trace Dependents
To remove tracer arrows one level at a time, starting with the dependent cell farthest away from the active cell, on the Formulas tab, in the Formula Auditing group, click the arrow next to Remove Arrows, and then click Remove Dependent Arrows
Follow these steps:
In an empty cell, enter = (the equal sign).
Click the Select All button.
Select the cell, and on the Formulas tab, in the Formula Auditing group, click Trace Precedents
To remove all tracer arrows on the worksheet, on the Formulas tab, in the Formula Auditing group, click Remove Arrows
Issue: Microsoft Excel beeps when I click the Trace Dependents or Trace Precedents command.
If Excel beeps when you click Trace Dependents
References to text boxes, embedded charts, or pictures on worksheets.
References to named constants.
Formulas located in another workbook that refer to the active cell if the other workbook is closed.
To see the color-coded precedents for the arguments in a formula, select a cell and press F2.
To select the cell at the other end of an arrow, double-click the arrow. If the cell is in another worksheet or workbook, double-click the black arrow to display the Go To dialog box, and then double-click the reference you want in the Go to list.
All tracer arrows disappear if you change the formula to which the arrows point, insert or delete columns or rows, or delete or move cells. To restore the tracer arrows after making any of these changes, you must use auditing commands on the worksheet again. To keep track of the original tracer arrows, print the worksheet with the tracer arrows displayed before you make the changes.
Источник
How to Trace Dependents in Excel
Find out which cell values are connected
If you work with formulas a lot in Excel, you know that the value of a single cell can be used in a formula in many different cells. In fact, cells on a different sheet may reference that value also. This means that those cells are dependent on the other cell.
Trace Dependents in Excel
If you change the value of that single cell, it will change the value of any other cell that happens to reference that cell in a formula. Let’s take an example to see what I mean. Here we have a very simple sheet where we have three numbers and then take the sum and the average of those numbers.
So let’s say you wanted to know the dependent cells of cell C3, which has a value of 10. Which cells will have their values changed if we change the value of 10 to something else? Obviously, it will change the sum and the average.
In Excel, you can visually see this by tracing dependents. You can do this by going to the Formulas tab, then clicking on the cell you want to trace and then clicking on the Trace Dependents button.
When you do this, you will instantly see blue arrows drawn from that cell to the dependent cells like shown below:
You can remove the arrows by simply clicking on the cell and clicking the Remove Arrows button. But let’s say you have another formula on Sheet2 that is using the value from C1. Can you trace dependents on another sheet? Sure you can! Here’s what it would look like:
As you can see, there is a dotted black line that points to what looks like an icon for a sheet. This means that there is a dependent cell on another sheet. If you double-click on the dotted black line, it’ll bring up a Go To dialog where you can jump to that specific cell in that sheet.
So that’s pretty much it for dependents. It’s kind of hard not to talk about precedents when talking about dependents because they are so similar. Just as we wanted to see which cells are affected by the value of C3 in the example above, we may also want to see which cells affect the value of G3 or H3.
As you can see, cells C3, D3, and E3 affect the value of the sum cell. Those three cells are highlighted in a blue box with an arrow pointing to the sum cell. It’s pretty straight-forward, but if you have some really complicated formulas that use complicated functions too, then there may be a lot of arrows going all over the place. If you have any questions, feel free to post a comment and I’ll try to help. Enjoy!
Founder of Help Desk Geek and managing editor. He began blogging in 2007 and quit his job in 2010 to blog full-time. He has over 15 years of industry experience in IT and holds several technical certifications. Read Aseem’s Full Bio
Источник
Precedents & Dependents
Navigate
You have probably used Excel’s native Trace Precedents/Dependents tools and discovered the limitations of their utility. Macabacus’ Pro Precedents and Pro Dependents—the most advanced auditing tools of their kind—make tracing precedents/dependents very simple and are absolutely essential for any power user.
Pro Precedents
Pro Precedents allows you to effortlessly navigate an audited formula’s inputs. When you activate Pro Precedents, a dialog opens displaying the addresses and values of all cells used in the calculation of the audited cell. Selecting a precedent cell range in the dialog using the up/down arrow keys or the mouse navigates to the precedent range, whether it is outside of the visible range on the same worksheet, on another worksheet, or even in another workbook.
- Drill down — You can also drill down on precedents using intuitive, tree-based navigation. If a tree node has precedents, it will be marked with a symbol. Press the right arrow key to expand the tree node and trace precedents one level deeper. Use the left arrow key to move back up one level in the precedents tree. You can open the Pro Precedents dialog, navigate multiple levels of precedent cells, and close the dialog without ever using your mouse.
- Edit formulas — Use the F2 or Alt+E shortcut to modify the audited formula in Point, Enter, or Edit mode, as applicable (these mode names correspond to the text in the bottom left corner of the Excel window, which normally reads «Ready» when not in one of these three input modes). Macabacus takes you directly to Point mode when possible, allowing you to immediately use the keyboard arrows to navigate and modify the precedent range corresponding to the selected node. To make other changes to the formula, key the native F2 shortcut again to enter Edit mode.
- Move & resize — Pro Precedents has several keyboard shortcuts for repositioning and resizing the dialog. Key Ctrl+Up , Ctrl+Down , Ctrl+Right , and Ctrl+Left to move the dialog. Key Ctrl+Home and Ctrl+End to position the dialog at the top left and bottom right corners of the screen, respectively. Key Shift+Up , Shift+Down , Shift+Right , and Shift+Left to resize the dialog.
- Options — With the Evaluate Functions & Groups option enabled, Pro Precedents evaluates Excel functions (e.g., SUM) and expressions grouped by parentheses within formulas individually, letting you analyze complex formulas piece-by-piece. In other words, you can see what a portion of your formula is contributing to the overall result. If Macabacus is able to evaluate certain Excel functions as cell references, selecting the function in the Pro Precedents dialog will navigate to that cell range. If you are wondering what cell that HLOOKUP, VLOOKUP, OFFSET, CHOOSE, INDIRECT, or INDEX(MATCH) function is actually pulling from, Pro Precedents can show you. The Evaluate Functions & Groups option is disabled by default to avoid confusion for those who do not understand it, but enabling this option is recommended for most users. This feature can be toggled using the Ctrl+E shortcut.
Pro Dependents
The Pro Dependents dialog navigates an audited cell’s dependencies similar to how Pro Precedents navigates precedents. Other features include:
- Edit formulas — Edit a dependent cell’s formula directly in the formula box by either clicking into the formula box or keying F2 . When you are done editing the formula, key Enter to apply the new formula, or Esc to cancel editing. If the new formula no longer references the audited cell, the dependent node is removed from the tree.
- Check for chart dependencies — Pro Dependents lists as a dependency any chart whose series reference the audited cell. Note that data label references to the audited cell cannot be shown as dependencies.
- Check for name dependencies — Pro Dependents lists as a dependency any range name that refers to the audited cell.
Источник