Excel 2013 For Mac Trace Precedents On Another Sheet

 

Our free VBA Add-in installs directly into the VBA Editor, giving you access to 150 ready-to-use VBA code examples for Excel. Simply click your desired code example and it will immediately insert into the VBA code editor. Dear community, I just used the trace dependents feature. If the cell is used on a different sheet, there is simply an icon indicating that it is referencing to a different sheet.

Tracing Dependent and Precedent Cells

Excel 2013 includes a set of formula auditing tools in the Formula Auditing group of the FORMULAS ribbon. These tools allow users to see the relationships between formulas and cells.

Within the formula auditing section are the following tools:

Trace Precedents and Trace Dependents

The Trace Precedents and Trace Dependents options use arrows to show how an individual cell links to other cells, or is linked to from other cells.

Things are relatively straightforward when all the related cells are on the same worksheet. Excel will draw arrows from the active cell to the related cell or cells. However, even on the same sheet, there are a couple of capabilities that might not be immediately obvious.

The first time you click the ‘Trace’ button the cells that link directly to/from the active cell will be ‘arrowed’. Here we have selected cell H9 and clicked the Trace Precedents button:

We can see that H9 includes a reference to the range of cells B4:B8

If we click on the button again, cells that link directly to the first set of precedent cells will gain arrows and so on through to the cells that link directly and indirectly to the active cell.

The arrows themselves can be double-clicked – this will toggle the selection between the cells at either end of the arrow. The arrows can be removed using the Remove Arrows button in the Formula Auditing group.

If a cell links to another sheet then, instead of pointing at an actual cell, the Trace Precedents and Trace Dependents arrows will be dotted and point to a small picture of a worksheet. To see the details of the cells that relate to the selected cell, double-click the arrow itself (not the worksheet icon). This will display the Go To dialog with the related cells listed. Double-clicking on one of the cells listed in the Go To list will select that cell:

Display formulae

Switching from the display of cell values to cell formulae can help reveal inconsistent use of formulae or formulae that have been overwritten. You can switch to the display of formulae using FORMULAS ribbon tab > Formula Auditing group > Show Formulas:

Watch Window

The FORMULAS ribbon tab >Formula Auditing group, also includes the Watch Window option.

This lets you add cells from anywhere in the current workbook and displays their current values. The Watch Window is within Excel the application, so if you have multiple workbooks open you can switch workbooks and add cells from other workbooks to the Watch Window. You can’t switch to a different workbook from within the Add Watch window.

The window displays the ‘live’ value of all the cells, from whichever open workbook, that have been added to the window. As well as displaying cell values, the Watch Window also allows you to navigate to any of the watched cells just by double-clicking them:

Formula evaluator

Sometimes a formula might not give the result that you expect. If the formula is complex, maybe containing several functions, then it can be difficult working out just where the formula is going wrong. The FORMULAS ribbon tab > Formula Auditing section, includes the Evaluate Formula option. This evaluates each element of a formula individually:

Error Checker

Excel has a set of built-in rules to identify cells that might need attention. Some of the rules are obvious such as ‘Cells containing formulas that result in an error’ while others depend on slightly more subtle circumstances. The Error Checking settings and status of the individual rules can be accessed through FILE > Options > Formulas > Error Checking and Error checking rules sections:

The information icons next to each rule provide details of what the rule checks.

By default, cells that ‘contravene’ the rules are marked with a green indicator. Clicking on such a cell displays a warning icon which, when clicked, explains what the problem is and provides a series of possible resolutions.

If you are signed up to Skillsology check out our Excel course for more information on this: Section 5, Unit 7, Module 1 – Tracing Calculations.

ExcelAuditing

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.

Precedents

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.
Shift

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.
Shift

Last Audited Cell

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

Shift

Troubleshooting Pro Precedents / Dependents

Pro Precedents / Dependents does not trace a formula properly

  • Does the audited formula contain unqualifiedstructured 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.
  • Does a precedent or dependent range contain merged cells? Merged cells should be avoided, in general, and Macabacus may be unable to process them. Use Center Across Selection as an alternative, if possible.
  • Macabacus may be unable to parse the audited formula because your workbook contains hundreds or thousands of unused range names that you don't even know about. Use Macabacus' Name Scrubber to 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. Add-ins, including Macabacus, use some hidden names for legitimate purposes (e.g., linking), so be careful when deleting them.
  • 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.

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 Pro Precedents dialog does not display the full audited formula

Excel 2013 For Mac Trace Precedents On Another Sheet Download

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.

The up / down arrow keys do not navigate 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 and Anaplan add-ins, specifically, although other add-ins might produce the same result. Disable the 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. 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.

Alt

Show All Dependents

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.

Alt

Clear Arrows

Excel 2013 For Mac Trace Precedents On Another Sheet Download

Clear trace arrows from the active worksheet.

Alt

AutoTrace

AutoTrace limit

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.

Excel 2013 For Mac Trace Precedents On Another Sheet Pdf

AutoTrace Precedents

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.

Alt
[

AutoTrace Dependents

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.

Alt
]

This documentation refers to Macabacus version 9.1.6. 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.