31

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

29

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.

8
  • 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.
    – Rubén
    Nov 4, 2021 at 17:46
  • Anyone know how to search only within same tab/sheet?
    – cellepo
    Jan 23 at 20:23
  • @cellepo In the Find and Replace dialog, click the Search dropdown and select This Sheet.
    – Rubén
    Jan 23 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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