Where is the formula referring to???

This is an extension of my post – Teleport in Excel

I don’t remember  how many times I want to know where a formula is actually referring to, esp. when I need to spot what’s the formula doing.  If the worksheet is small, it’s manageable.  When the worksheet is huge or many sheets are involved, I wish I know a way to go to the range in a formula quickly in order to understand what the formula does.  Wait… do the techniques discussed in “Teleport in Excel” help?  Of course!  🙂

As a simple example below, I want to check what “Sheet6!A1:B6” contains (not evaluating the formula)


Simply check inside the formula bar


You should see the pop up info box (sorry that I don’t know the official name for such box)

Hover your cursor onto the description, e.g. table array, and click on itImage

Now the “table array” is selected in the formula bar  (note: this is a very useful technique for evaluating formula, esp. a long one)

With the “table array” selected, Press CTRL G


The Go to Dialogue box opens, with the “table array” pre-input in the References:


Now simply click OK to go to the “table array”, i.e. Sheet6!A1:B6 in this case

There you go.  So easy!


Press ESC to return (Note the formula bar is still active, you may revise your formula if needed)

Do you find it useful?  Feel free to leave your comments!


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

2 Responses to Where is the formula referring to???

  1. Arun Gatg says:

    Please send me daily newsletter and post


    • MF says:

      Hi Arun, you may follow my blog with your email address then you will get an email when there is a blogpost published.


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.