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!
Excellent, love your tips!!
Thanks Marci! 🙂
I do not always see an immediate use for some of your tips, however; I love watching them so I can archive the thought. Many of them come to light later on and it is great to remember that “I can do that”.
I love that you tell us the multiple ways to access things, I’m an old DOS and UNIX man so the keyboard is my very good friend. You taught me CTRL+ and CTRL- and I use it dozens of times a day, cannot thank you enough.
Thank you for the nice comment and feedback. I really appreciate it and I’m happy to hear you are finding the tips useful.
The keyboard is my good friend too. 🙂 There are always a million different ways to do something in Excel, so I feel like we’ll never run out of things to learn.
I know it is off-topic, but I am curious:
In my Excel sheet I have to display a year as
“2021”, “2022”,… (calendar years)
“21/22”, “22/23”,… (curricular years)
I implemented this as a function because I could not specify a working date format from the integer values 2021, 2022,…
Is it possible to create a date format fof this?
Another feature I love about the Go To window is that after using it to navigate to a range, you can bring it up again and it will automatically be populated with the reference to the range you just came from.
So after using it to get to a reference and do what you need to there, simply press Ctrl + G to bring the window up again; and then press Enter, and you will be taken right back to where you were before navigating to the reference.