Bottom Line: Learn how to use the Go To window in Excel to quickly navigate to any formula reference.
Skill Level: Beginner
Watch the Tutorial
Navigate to Formula References
This is a great tip to know if you spend a lot of time working with formulas, especially if the formulas were created by someone else. That's because it's a quick way to navigate to portions of the formula that otherwise might take time to find if you're unfamiliar with the workbook/data.
For example, in the image below, I want to go to the Table Array reference in order to make changes. It's currently reads ‘Product List'!$A$2:$C$18.
Instead of finding the Product List tab in the worksheet and then finding the cell range A2:C18, we can jump straight to it using the Go To window.
Using the Go To Window
- Start by selecting the reference for the data you're looking for. To select it, you can either highlight it with your mouse, our just click on the argument name (the screen tip) that appears below the formula.
- Open the Go To window. On the Home tab of the ribbon, select the Find & Select drop-down menu, and choose Go To… Or use the keyboard shortcut Ctrl + G (or F5).
- The reference we highlighted in the first step will automatically be populated in the Go To window, so all you have to do is hit OK.
After you hit OK, you will be taken to the data that is referenced so you can make changes. If you just wanted to look at the data without making changes, you can hit Esc to finish.
Something to Keep in Mind
When you jump to the data that you're looking for, your are still in formula edit mode, so the formula shows in the Formula Bar. However, there is a glitch that you need to be aware of. Any absolute references that were in the highlighted portion of the data get changed to relative references. You will have to add the dollar signs ($) back in to make them absolute again.
To make a reference absolute, just highlight it and hit F4.
Good for Tables
This technique is especially useful for Excel Tables because the references don't always indicate which worksheet the data is on. So jumping straight to the data using the Go To window saves time because you don't have to search for the data on each sheet.
When you open the Go To window, you will notice that instead of the table name, the XXXX field shows the sheet anme and cell range that it is taking you to.
Fortunately, the nuance I mentioned before about the absolute references doesn't apply with Excel Tables.
This tip is one of ten that I share in my Excel Pro Tips ebook. If you'd like to see the other nine, you can access the ebook for free here: Excel Pro Tips.
And here's a post with hundreds of other Excel shortcuts which you might want to bookmark: Excel Keyboard Shortcuts List.
Hope this post is helpful to you! If you have any questions, feel free to leave them in the comments below!