Finding a needle in a haystack

When auditing formulas in Excel, tracing the cells referenced in a formula can be like finding a needle in a haystack. Save yourself the hassle and use these keyboard shortcuts instead!

The first shortcut is CTRL+[, which finds and selects cells that are directly referenced in a formula. To use this shortcut, select the cell with the formula you want to find cell references for and hit CTRL+[. Excel will automatically select the cells directly referenced in the formula.

The next shortcut is CTRL+SHIFT+[, which finds and selects cells that are both indirectly and directly referenced in a formula. Similar to the CTRL+[ shortcut, you select the cell with the formula you want to find cell references for and hit CTRL+SHIFT+[. Now Excel will automatically select all cells directly and indirectly referenced in the formula.

Note that an indirect cell reference is a value used to calculate a value used in a formula. In the example above, each book sale is an indirect reference because they’re all added up to calculate the “Total Book Sales” value, and the “Total Book Sales” is used to calculate “Book Revenue.” The “Total Book Sales” and “Cost of Goods Sold” are direct references because they are used to calculate “Book Revenue.”

Phew—no more hunting for that needle!

Related Video

Back to blog

Keep Reading

View all
  • TOCOL or Not TOCOL

    TOCOL or Not TOCOL

    TOCOL or not TOCOL…there really is no question.  TOCOL is the easiest way to combine data from multiple lists into one! Check out the gif above - we’ve got several...

    TOCOL or Not TOCOL

    TOCOL or not TOCOL…there really is no question.  TOCOL is the easiest way to combine data from multiple lists into one! Check out the gif above - we’ve got several...

  • That's How That Works?!

    That's How That Works?!

    Ah, the elusive pivot table.  Everyone fears it, nobody understands how it works, and those who unlock its mysteries are the Excel wizards we all hope to be.  Have you...

    That's How That Works?!

    Ah, the elusive pivot table.  Everyone fears it, nobody understands how it works, and those who unlock its mysteries are the Excel wizards we all hope to be.  Have you...

  • There’s Nothing You Gantt Do

    There’s Nothing You Gantt Do

    Ahh, the Gantt Chart.  Is there anything so satisfying?  If you’ve never encountered one, a Gantt Chart is a horizontal bar chart that visually represents a project plan over time....

    There’s Nothing You Gantt Do

    Ahh, the Gantt Chart.  Is there anything so satisfying?  If you’ve never encountered one, a Gantt Chart is a horizontal bar chart that visually represents a project plan over time....

1 3
View all

Meet Emma

Emma is the founder of Excel Dictionary, your ultimate source for impactful, digestible Excel tips and tricks.  After graduating from The Ohio State University in 2019 with a degree in Actuarial Science, Emma began her professional career as an actuary, just in time for COVID to hit.  New to the industry, new to the company, adjusting to a new WFH environment, and new to Excel, Emma quickly realized how overwhelming those Excel projects could be and didn’t know where to turn for help – so she taught herself.

Emma created Excel Dictionary to help others avoid Excel overwhelm and to be the coworker that you can turn to.  She was recently awarded the Microsoft MVP award and has built a community of over 5 million people across the globe! Join her across all social channels to learn quick, actionable skills that will make you more efficient, comfortable, and confident in your daily work!

Follow Emma