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 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.
Alt+Eshortcut 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
F2shortcut again to enter Edit mode.
Ctrl+Leftto move the dialog. Key
Ctrl+Endto position the dialog at the top left and bottom right corners of the screen, respectively. Key
Shift+Leftto resize the dialog.
The Pro Dependents dialog navigates an audited cell's dependencies similar to how Pro Precedents navigates precedents. Other features include:
F2. When you are done editing the formula, key
Enterto apply the new formula, or
Escto cancel editing. If the new formula no longer references the audited cell, the dependent node is removed from the tree.
Navigate back to the last cell audited using Pro Precedents or Pro Dependents.
Center Across Selectionas an alternative, if possible.
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.
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+End shortcuts immediately after executing Pro Precedents/Dependents to bring the hidden dialog back into view.
If the formula you are auditing contains line breaks (achieved by keying
Alt+Enter), you must enable the
Wrap Formula Text setting in the Pro Precedents dialog to view the entire formula.
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 and Anaplan add-ins, specifically, although other add-ins might produce the same result. Disable the conflicting COM add-in to resolve the problem.
Excel natively traces precedents only for the active cell in a selection of multiple cells. In other words, Excel only shows you trace arrows for one cell at a time. 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.
Excel natively traces dependents for only the active cell in a selection of multiple cells. In other words, Excel only shows you trace arrows for one cell at a time. 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 trace arrows from the active worksheet.
AutoTrace will not show trace arrows for selections containing more than 20 cells. Without this limit, navigation might become very slow or Excel could freeze as Macabacus computes precedents / dependents for larger selections.
Show precedent trace arrows for the active cell automatically as you navigate among cells by enabling AutoTrace Precedents. AutoTrace Precedents can be toggled on and off using the shortcut below, or from the
Macabacus > Trace menu.
Show dependent trace arrows for the active cell automatically as you navigate among cells by enabling AutoTrace Dependents. AutoTrace Dependents can be toggled on and off using the shortcut below, or from the
Macabacus > Trace menu.
This documentation refers to Macabacus version 9.1.9. Some features and descriptions of these features may not
apply to older versions of Macabacus. Update your Macabacus software to take advantage of the latest features.