We all have been there. You started to build a simple workbook to keep track of an ongoing project or dashboard or something equally complicated. Even before you realize the workbook has 23 sheets and 41 named ranges all going from one place to another, like flying spaghetti monster, only less awesome.
Now, how do you keep track of all these names?
Thankfully, there is just the feature for this, called as “paste names“.
This is how it works.
To paste all the names and their corresponding references, just press F3 anywhere in a workbook and click the button that says “paste names”. Bingo, you have a list of names and their references. Now, you can audit a spreadsheet or maintain it with ease.
That is all. Go back to eating spaghetti, now.
More tips on spreadsheet maintenance:
PS: Here is a great recipe if you don’t know how to make awesome spaghetti.
12 Responses to “Show all the names & their References [Spreadsheet Audit]”
I've been using Jan-Karel Pieterse's Name Manager add-in for a few years now...
Its a free utility available for download at
http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp
In addition to the list generated by Excel, its report gives a few other details as well.
Khushnood
This is a great technique for monitoring your named cells on a separate sheet. What I do is then edit each formula cell (F2, Enter) to see the current value.
Chandoo,
I, too, recommend Jan-Karel Pieterse’s Name Manager add-in available for free download at
http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp
It works with Excel 2007 and 2010 the 64bit version.
In addition to being able to produce a sheet with all of the named ranges and range definitions, it allows you to manage those ranges that are no longer valid and ranges that have become linked to other spreadsheets. The latter issue is the biggest cause of unintended ans hard to identify spreadsheet size bloat.
-bill
always very nice hints and reminders topics
Thanks
Happy Birthday for your lovely kids, by the way I and GOD like all kids in the world
I wonder how to show formulas in one column as old """LOTUS 123"""
This is a great tip, but it will only show visible Names in the workbook. Any Names hidden (with VBA) will not show on the list. If all Names are hidden then pressing F3 will do nothing.
@Gregory.. I didnt know we can hide names thru vba. Thanks for sharing that with us.
@Bill & Khushnood... good pointer about JKP's name manager. I will install it this weekend.
@AK.. you can use CTRL+` that is back tick to show formulas instead of values. This might be what you are looking for.. ?
[...] http://chandoo.org/wp/2010/09/20/show-all-names-excel/ [...]
Hi All if you use control f3 , you get the Name Manager which is also great as well. Cheers
@Karen Cullen
Thanks! just what I needed Control F3.
You are the best man!
I have a large workbook with a data dictionary to list all named ranges/tables, field type, validation rules, and how each is used. What formula can I use to show the current "refers to" Sheet and cells, especially for dynamic ranges/tables? F3 will list the current ranges, but I can't use it in the Data Dictionary as tables grow.
Hi.
Here are a few of my "findings" on F3... bear with me, for I'll probably repeat things stated in the article:
F3 displays all Workbook-scoped (global) named ranges (NRs) AND any sheet-scoped (local) NRs in that active sheet.
Yes, it can be used to paste the entire list to any large enough, 2-column wide range.
However, if the sheet is protected, local NRs will be displayed, but cannot be pasted.
If there are no global NRs, only the local NRs in the active sheet, if any, will be displayed. If there are none, you will hear the Windows ding.
I do not know of a way to display and/or paste the entire list of local NRs without going sheet by sheet... I am sure someone has written a VBA macro to do so.
Thanks for the all the info and all the help you provide in this site; it is absolutely priceless!
CHEERS