42

Is there a way to see which cells reference a given cell in their formulas, in Google Sheets?

For example, if cell C1 contains =SUM(A1:A5), and cell D1 contains =SUM(A5:A8), I'd like to somehow be able to determine that cell A5 is used in two places: C1 and D1, without prior knowledge that A5 is referenced by C1 and D1. It would be similar to how clicking on a formula highlights which cells it references, but the reverse.

Improve this question

1 Answer 1

Reset to default
40

Short answer

Use find & replace

Explanation

  1. Press Ctrl + H to open the Advanced Find and Replace dialog
  2. Write A5 on the Find field
  3. Turn on the Also check within formulas option. This will turn on the View All formulas spreadsheet option.
  4. Click the Find button

Tips

Use \$?A\$?5 and check Search using regular expressions to find all the different reference forms (A5, A$5, $A5, $A$5) with a single search.

Improve this answer
11
  • 4
    Adding to this, use Cmd+Shift+H on a Mac to open the dialog. Alternatively, go to the Edit menu and click on Find and Replace.
    – Luca Bezerra
    Commented Oct 21, 2021 at 22:05
  • I'd also suggest looking for version with dollar signs: $A5 and $A$5
    – MegaMatt
    Commented Nov 4, 2021 at 17:16
  • 4
    Any idea how to find formula linking to A6? The formula is A5:A8 so regular expression '$?A:$?5' will not find it.
    – Eugene Kaurov
    Commented Jan 28, 2022 at 16:13
  • 1
    Caveat: may not work if you are using things like VLOOKUP, MATCH, INDEX...
    – Roberto
    Commented Feb 10, 2023 at 6:10
  • 2
    this does not find references via ranges (except when it is one of the start:end cells), let alone named ranges
    – törzsmókus
    Commented May 4, 2023 at 8:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.