Categories
Find all references to defined names in Excel
Named ranges (or defined names) are a very useful feature in Excel, but experienced users will agree that it is easy to lose oversight of all instances of reference. In this article we’ll show you how to find all references to your defined names in Excel.
Excel allows you to define a cell name, so that, when you refer to it in a formula or somewhere else in your spreadsheet, you get a clear name like “Interest_Rate” in stead of some obscure code like “C19.” It helps clarify what type of value is being referred to and it allows users to update “Interest_Rate” globally (across the model) if it changes. Only one question remains… Where is my named range used?
Where is my named range used?
This question is difficult to answer in Excel itself. You can go to the name manager (Formulas > Name Manager) to view your named ranges, and there you can see which cells are saved as named ranges.
This is helpful, because you can then track the dependents of that cell with the hotkey Ctrl + ]
. But it is also inefficient, because the named range might be used in hundreds of cells across many different worksheets.
PerfectXL Explore finds all references
The importance of finding all instances of reference is something we thought about when developing PerfectXL Explore. In just a few clicks you can see for both locally saved as well as globally defined names exactly which cells reference the name, even when the name is used in long, complex formulas. Check it out!
Are you interested in this functionality? Let us show you how easy it is and sign up for a demo!