41

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.

1 Answer 1

37

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.

11
  • 3
    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. Oct 21, 2021 at 22:05
  • I'd also suggest looking for version with dollar signs: $A5 and $A$5
    – MegaMatt
    Nov 4, 2021 at 17:16
  • @MegaMatt Thanks. I added a related tip. Nov 4, 2021 at 17:46
  • 4
    Any idea how to find formula linking to A6? The formula is A5:A8 so regular expression '$?A:$?5' will not find it. Jan 28, 2022 at 16:13
  • 1
    Caveat: may not work if you are using things like VLOOKUP, MATCH, INDEX...
    – Roberto
    Feb 10, 2023 at 6:10

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.