fbpx
Search
Close this search box.

Show all the names & their References [Spreadsheet Audit]

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

12 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.

  11. Alphonse says:

    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

Leave a Reply