Meet the Family: Find a Formula's Relatives with Ctrl [ and Ctrl ]

Before you delete a cell, find out who it's related to.

Every good haunted house has a ghost: a creak upstairs that nobody can trace. You hear it, you know something is up there, but you can't tell where it's coming from.

Spreadsheets can feel the same way. You change one number, and a total three screens away suddenly jumps. You delete a "leftover" cell, and a formula somewhere else turns into #REF!. That's a spreadsheet ghost: a relationship that was there all along, but invisible.

Excel calls these relationships precedents and dependents:

• Precedents are the cells a formula gets its values from. Think of them as the formula's parents.
• Dependents are the cells that use a cell's value. Think of them as its children.

This matters most when you inherit a workbook from someone else, update a budget or report you haven't touched in months, or clean up a sheet before sharing it. Two small keyboard shortcuts show you the whole family in a second, so you can make changes with confidence instead of crossing your fingers.

1. Jump to a Formula's Parents with Ctrl [

When a formula shows a surprising result, the first question is always "where is this number coming from?" Ctrl [ (Ctrl plus the left square bracket) answers it instantly.

1. Click a cell that contains a formula.
2. Press Ctrl [. Excel selects every cell the formula refers to directly.
3. If several cells are selected, press Enter to move through them one at a time. The formula bar shows each cell's contents as you go.
4. If the formula refers to another worksheet, Ctrl [ takes you right to that sheet.

Outcome: You can check where any number comes from in seconds, which makes it much faster to find and fix a wrong total before it reaches your boss or your client.

2. Find a Cell's Children with Ctrl ]

Before you delete, move, or change a cell, it's wise to ask the opposite question: "who depends on this?" That's what Ctrl ] (Ctrl plus the right square bracket) is for.

1. Click the cell you're thinking about changing or deleting.
2. Press Ctrl ]. Excel selects every formula on the sheet that uses this cell directly.
3. If Excel says No cells were found, nothing on this sheet depends on it.
4. Keep in mind that Ctrl ] only looks at the current worksheet. If you suspect other sheets use the cell, use the Trace Dependents arrows described in tip 5.

Outcome: You avoid the classic mistake of deleting a cell that looked unused but was quietly feeding an important calculation.

3. Follow the Whole Family Tree

Formulas often build on other formulas. A grand total might add up subtotals, which add up line items. Add Shift to either shortcut to follow the relationship all the way down (or up) the tree.

1. Click a cell.
2. Press Ctrl Shift { to select all precedents, including the precedents of the precedents.
3. Or press Ctrl Shift } to select all dependents, direct and indirect.
4. Press Enter to step through the selected cells.

Outcome: You see every cell that affects (or is affected by) a key number, which is invaluable when you're auditing a report or explaining how a figure was calculated.

4. Find Your Way Back Home with F5

After jumping around a large workbook, it's easy to lose your place. Excel remembers where you started.

1. After using Ctrl [ or Ctrl ], press F5 (or Ctrl G, the Go To shortcut from our August newsletter).
2. The Reference box already contains the cell you came from.
3. Press Enter to jump back.

Outcome: You can explore a formula's relationships freely, knowing you're always one keystroke away from where you started.

5. See the Relationships Drawn on the Sheet

Sometimes a picture helps. Excel can draw arrows showing exactly how cells are related, including relationships that cross into other worksheets.

1. Click a cell.
2. On the Formulas tab, in the Formula Auditing group, click Trace Precedents or Trace Dependents.
3. Blue arrows connect the related cells. Click the button again to trace the next level.
4. A dashed arrow with a small worksheet icon means the relationship leads to another sheet. Double-click the dashed line to see a list of those cells and jump to one.
5. When you're done, click Remove Arrows.

Outcome: You get a visual map of how your workbook fits together, which is especially helpful when explaining a spreadsheet to a coworker or training someone new.

A Quick Note About Keyboards

These shortcuts use the square bracket keys found on U.S. English keyboards. On some international keyboard layouts, the brackets are in a different place and the shortcuts may not work. If that's the case for you, the Trace Precedents and Trace Dependents buttons in tip 5 do the same job.

Keep the Family Together

Formulas are relationships. When you can see them, you can change your spreadsheets confidently, catch errors early, and hand workbooks off without worrying about what might break. Make Ctrl [ and Ctrl ] a habit, and no ghost in your spreadsheet will go bump in the night.

Inherited a workbook that nobody fully understands? Reach out to us and let us know how we can help. We're happy to help you untangle it.