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.

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.

You can also drill down on precedents using intuitive, tree-based navigation. If a precedent cell or evaluated function/group has precedents, it will be marked with a symbol. Press the right arrow key to expand the tree 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.

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.

Pro Dependents

The Pro Dependents dialog navigates an audited cell's dependencies similar to how Pro Precedents navigates precedents. Other features include:

• Edit formulas "live" — 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 list as a dependency any range name that refers to the audited cell.

Last Audited Cell

Navigate back to the last cell audited using Pro Precedents or Pro Dependents.

Troubleshooting

Pro Precedents/Dependents will not trace a formula

1. Is the audited formula a 3D formula referring to the same range on multiple worksheets (e.g., =SUM(Sheet1:Sheet3!C5))? Macabacus cannot process 3D formulas at this time.
2. Does the audited formula contain unqualified structured table references (e.g., =SUM([Sales]))? Macabacus can only process fully qualified structured table references (e.g., =SUM(DeptSales[Sales]) in formulas at this time. Fully qualified structured table references must also reference tables within the same workbook.
3. Most often, Macabacus is unable to parse the audited formula because your workbook contains hundreds or thousands of unused range names that you don't use or even know about. Go to the Macabacus > File > Optimize menu and open Name Scrubber. Remove any unused names by clicking the Clean Names button and performing a Deep Clean, or remove names manually from the list. If you are satisfied that hidden names are not in use, delete them, too. Note that some add-ins, including Macabacus, use hidden names for legitimate purposes (e.g., linking), so be careful when deleting them.
4. If you are still experiencing difficulty, email support@macabacus.com with the information requested here. You should also include a copy/paste of the audited formula or, if at all possible, attached the affected workbook.

The Pro Dependents does not find dependents in certain formulas

If Pro Dependents does not find dependents in cells where you believe it should, use Excel's native Trace Dependents tool to see if the expected cells appear there. If not, then the "dependent" cell's formula may contain a "volatile" function. Excel cannot find dependencies in some volatile functions like OFFSET(), and since Macabacus relies on Excel to tell it what the dependencies are, Pro Dependents cannot find these dependencies, either.

The Pro Precedents/Dependents dialog is hidden

Macabacus remembers the last position of the Pro Precedents/Dependents dialogs. Sometimes, these dialogs can appear outside of your screen's viewable area. This can happen if you are working in Excel on a large docking station screen, undock your laptop and begin working on the laptop's smaller screen. Use the Ctrl+Home or Ctrl+End shortcuts immediately after executing Pro Precedents/Dependents to bring the hidden dialog back into view.

The Up/Down arrow keys do not navigate the precedents/dependents

If keying the Up/Down arrow keys does not navigate the precedents and dependents in the Pro Precedents and Pro Dependents dialogs, respectively, and instead causes the cursor to move up/down one row on the worksheet, another add-in may be causing this condition. We have only observed this conflict with the Workshare add-in, specifically, although other add-ins might produce the same result. Disable the Workshare or other conflicting COM add-in to resolve the problem.

Show All Arrows

Show All Precedents

Excel natively traces precedents only for the active cell in a selection of multiple cells. Macabacus, on the other hand, shows precedent trace arrows for all selected cells at once. If you repeat this keystroke before changing the cell selection, Macabacus clears the trace arrows.

Show All Dependents

Excel natively traces dependents for only the active cell in a selection of multiple cells. Macabacus, on the other hand, shows dependent trace arrows for all selected cells at once. This is a useful check to perform before deleting cells that helps avoid unintentional #REF! errors upon deletion. If you repeat this keystroke before changing the cell selection, Macabacus clears the trace arrows.

Clear Arrows

Clear trace arrows from the active worksheet.

AutoTrace

AutoTrace Precedents

You can automatically show precedent trace arrows for the active cell as you navigate among cells by enabling AutoTrace Precedents. AutoTrace Precedents can be toggled on and off using the shortcut below, or with a checkbox on the Macabacus > Trace menu.

AutoTrace Dependents

You can automatically show dependent trace arrows for the active cell as you navigate among cells by enabling AutoTrace Dependents. AutoTrace Dependents can be toggled on and off using the shortcut below, or with a checkbox on the Macabacus > Trace menu.

This documentation refers to Macabacus version 8.8.15, and may differ from the PDF documentation
on your computer that installed with your version of Macabacus.