Show all the names & their References [Spreadsheet Audit]

Posted on September 20th, 2010 in Excel Howtos - 11 comments

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.

Paste All Named Ranges - Excel

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.

How to show all the named ranges in excel?

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.

11 Responses to “Show all the names & their References [Spreadsheet Audit]”

  1. Khushnood Viccaji says:

    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

  2. JP says:

    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.

  3. bill says:

    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

  4. A.K.Salaymeh says:

    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"""

  5. Gregory says:

    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.

  6. Chandoo says:

    @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.. ?

  7. Karen Cullen says:

    Hi All if you use control f3 , you get the Name Manager which is also great as well. Cheers

  8. alan says:

    @Karen Cullen
    Thanks! just what I needed Control F3.

  9. Maxime Manuel says:

    You are the best man!

  10. Maria says:

    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.

Leave a Reply