Print list of Defined Names

%3CLINGO-SUB%20id%3D%22lingo-sub-3188879%22%20slang%3D%22en-US%22%3EPrint%20list%20of%20Defined%20Names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3188879%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20excel%20file%20in%20which%20I%20use%20over%2080%20defined%20named%20for%20different%20variables.%20I%20was%20careful%20to%20use%20a%20structured%20naming%20convention%20for%20the%20variable.%26nbsp%3B%20I%20want%20someone%20else%20to%20be%20able%20to%20maintain%20it%20and%20evolve%20it%20over%20time.%26nbsp%3B%20To%20help%20that%20person%2C%20I%20would%20like%20to%20give%20a%20little%20explanation%20of%20the%20variable%20name%20(something%20akin%20to%20a%20data%20dictionary).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20not%20see%20a%20capability%20for%20the%20Excel%20name%20manager%20to%20export%20a%20list%20of%20the%20name%20to%20a%20file%20or%20an%20added%20sheet%20in%20the%20file%20of%20interest.%26nbsp%3B%20surely%2C%20someone%20has%20been%20down%20this%20path.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3188879%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3189050%22%20slang%3D%22en-US%22%3ERe%3A%20Print%20list%20of%20Defined%20Names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3189050%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1312601%22%20target%3D%22_blank%22%3E%40AstroGuy%3C%2FA%3E%26nbsp%3BThe%20very%20recently%20introduced%20%3CSTRONG%3EAdvanced%20Formula%20Environment%3C%2FSTRONG%3E%20could%20produce%20such%20a%20list.%20Search%20for%20it%20in%20the%20MS%20add-in%20list%20(Insert%20ribbon%2C%20Get%20Add-inns)%20and%20see%20it's%20available%20for%20your%20Excel%20version.%20If%20it%20is%2C%20then%20you%20can%20display%20a%20view%20like%20in%20the%20picture%20below.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-02-20%20at%2007.03.59.png%22%20style%3D%22width%3A%20295px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349736i27EFFE395A6E520F%2Fimage-dimensions%2F295x346%3Fv%3Dv2%22%20width%3D%22295%22%20height%3D%22346%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-02-20%20at%2007.03.59.png%22%20alt%3D%22Screenshot%202022-02-20%20at%2007.03.59.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ECut%20out%20the%20entire%20list%20and%20paste%20it%20in%20your%20data%20dictionary.%20It%20will%20also%20include%20named%20formulae.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EShould%20this%20not%20work%20for%20you%2C%20Power%20Query%20can%20also%20list%20all%20named%20ranges%20(not%20named%20formulae)%20used%20within%20a%20workbook%2C%20though%20it%20does%20not%20include%20the%20ranges%20to%20which%20they%20refer.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3190013%22%20slang%3D%22en-US%22%3ERe%3A%20Print%20list%20of%20Defined%20Names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3190013%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1312601%22%20target%3D%22_blank%22%3E%40AstroGuy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20hundred%20of%20names%20perhaps%20this%20tool%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fjkp-ads.com%2Fexcel-name-manager.asp%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EFree%20Name%20Manager%20Excel%20add-in%20(jkp-ads.com)%3C%2FA%3E%26nbsp%3Bby%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3Bwill%20be%20useful.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have an excel file in which I use over 80 defined named for different variables. I was careful to use a structured naming convention for the variable.  I want someone else to be able to maintain it and evolve it over time.  To help that person, I would like to give a little explanation of the variable name (something akin to a data dictionary).

 

I do not see a capability for the Excel name manager to export a list of the name to a file or an added sheet in the file of interest.  surely, someone has been down this path.

 

 

3 Replies

@AstroGuy The very recently introduced Advanced Formula Environment could produce such a list. Search for it in the MS add-in list (Insert ribbon, Get Add-inns) and see it's available for your Excel version. If it is, then you can display a view like in the picture below.

Screenshot 2022-02-20 at 07.03.59.png

Cut out the entire list and paste it in your data dictionary. It will also include named formulae.

 

Should this not work for you, Power Query can also list all named ranges (not named formulae) used within a workbook, though it does not include the ranges to which they refer.

@AstroGuy 

With hundred of names perhaps this tool Free Name Manager Excel add-in (jkp-ads.com) by @Jan Karel Pieterse will be useful.

I highly recommend my free tool as @Sergei Baklan has pointed you to already
That being said, you can get a quick list of range names using the Formulas tab, "Use in Formula" drop-down, "Paste Names" button and then click Paste List. Note that that will only paste names directly pointing to cells, names containing formulas will not be pasted.