Home

Custom Function not showing formulas in auto generated results in cells

%3CLINGO-SUB%20id%3D%22lingo-sub-718791%22%20slang%3D%22en-US%22%3ECustom%20Function%20not%20showing%20formulas%20in%20auto%20generated%20results%20in%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718791%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3CBR%20%2F%3EA%20Family%20Member%20who%20uses%20Excel%20for%20Business%20work%2C%26nbsp%3B%20came%20to%20me%20asking%20me%20why%20excel%20functions%20are%20not%20show%20up%20in%20formula%20bar%20when%20they%20click%20on%20the%20cell.%26nbsp%3B%20all%20that%20appears%20is%20the%20value%20in%20the%20cell%20also%20appears%20in%20the%20function%20bar%20(like%20the%20cell%20was%20manually%20typed%20in%20the%20numbers)%20.%26nbsp%3B%20they%20assure%20that%20the%20numbers%20are%20automatically%20generated%20from%20a%20custom%20function%2C%20however%20there%20isn't%20anything%20other%20than%20the%20values.%20so%20i%20have%20no%20idea%20where%20they%20could%20have%20gone.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eheres%20what%20i%20know%3A%26nbsp%3B%3C%2FP%3E%3CP%3E-%20excel%20workbook%20(xlsx%20format)%2C%20macros%20are%20enabled%3C%2FP%3E%3CP%3E-VB%20add-in%20is%20applied%20to%20the%20workbook%3C%2FP%3E%3CP%3E-%26nbsp%3B%20Showing%2Fforcing%20all%20cells%20to%20show%20function%2C%20only%20has%20a%20few%20that%20display.%26nbsp%3B%20%26nbsp%3Bbut%20the%20ones%20they%20are%20concerned%20about%20just%20show%20a%20number%26nbsp%3B%20%26nbsp%3Blike%2030.8%20(while%20force%20functions%20show%2030.8898898955454343%20and%20so%20on)%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B-%20I%20can%20get%20the%20VB%20modules%20and%20all%20the%20add-in%20and%20put%20it%20up%20(i%20just%20have%20to%20get%20it%20all%20together%20to%20attach)%20.%26nbsp%3B%20however%20the%20workbook%20has%20private%20information%20for%20business%2C%20so%20it%20has%20to%20be%20censored%26nbsp%3B%20if%20any%20needed%20to%20see%20the%20workbook.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20you%20can%20help%20solve%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-718791%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-719229%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20Function%20not%20showing%20formulas%20in%20auto%20generated%20results%20in%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719229%22%20slang%3D%22en-US%22%3EProbably%20the%20data%20is%20either%20pulled%20in%20using%20a%20data%20connection%20(Data%20tab%2C%20Queries%20%26amp%3B%20Connections%20button)%20or%20the%20data%20is%20placed%20into%20the%20workbook%20using%20VBA%20code.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719372%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20Function%20not%20showing%20formulas%20in%20auto%20generated%20results%20in%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719372%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365921%22%20target%3D%22_blank%22%3E%40Nwat1993%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20users%20prefer%20to%20hide%20the%20formulas%20so%20that%20no%20one%20could%20accidentally%20or%20intentionally%20modify%20them.%3C%2FP%3E%3CP%3EHidding%20formulas%20is%20a%202%20steps%20process%3A%3C%2FP%3E%3CP%3EStep%201%3A%3C%2FP%3E%3CP%3ESelect%20the%20cell%20having%20the%20formula%20to%20hide.%3C%2FP%3E%3CP%3EHit%20CTRL%20%2B%201%20to%20open%20the%20Format%20Cell%20Dialog%20box%3C%2FP%3E%3CP%3ECheck%20the%20box%20for%20Hidden.%3C%2FP%3E%3CP%3EStep%202%3C%2FP%3E%3CP%3EOn%20the%20Review%20Tab%20%26gt%3B%26gt%3B%20Protect%20Worksheet%3C%2FP%3E%3CP%3EIf%20it%20is%20protected%20without%20password%20(just%20for%20unintentional%20modifications)%20then%20you%20can%20unprotect%20it%20from%20here%20as%20well.%20If%20it%20password%20protected%20then%20you%20need%20the%20password.%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719409%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20Function%20not%20showing%20formulas%20in%20auto%20generated%20results%20in%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719409%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%20True%2C%20but%20if%20you%20hide%20a%20formula%2C%20the%20formula%20bar%20stays%20blank%2C%20it%20does%20not%20show%20the%20formula%20result%20as%20the%20poster%20of%20the%20question%20mentions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719637%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20Function%20not%20showing%20formulas%20in%20auto%20generated%20results%20in%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719637%22%20slang%3D%22en-US%22%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%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%3Bwrote%3A%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3ETrue%2C%20but%20if%20you%20hide%20a%20formula%2C%20the%20formula%20bar%20stays%20blank%2C%20it%20does%20not%20show%20the%20formula%20result%20as%20the%20poster%20of%20the%20question%20mentions.%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3EYou%20are%20right%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%3B%20because%20you%20will%20see%20the%20result%20in%20the%20cell%20itself%2C%20so%20what's%20the%20need%20of%20seeing%20it%20in%20the%20formula%20bar%20(as%20the%20name%20says%20it's%20for%20formulas)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Nwat1993
Occasional Visitor

Hi, 
A Family Member who uses Excel for Business work,  came to me asking me why excel functions are not show up in formula bar when they click on the cell.  all that appears is the value in the cell also appears in the function bar (like the cell was manually typed in the numbers) .  they assure that the numbers are automatically generated from a custom function, however there isn't anything other than the values. so i have no idea where they could have gone.  

 

heres what i know: 

- excel workbook (xlsx format), macros are enabled

-VB add-in is applied to the workbook

-  Showing/forcing all cells to show function, only has a few that display.   but the ones they are concerned about just show a number   like 30.8 (while force functions show 30.8898898955454343 and so on) 
 - I can get the VB modules and all the add-in and put it up (i just have to get it all together to attach) .  however the workbook has private information for business, so it has to be censored  if any needed to see the workbook. 

 

Hope you can help solve 

Thank You

4 Replies
Probably the data is either pulled in using a data connection (Data tab, Queries & Connections button) or the data is placed into the workbook using VBA code.
Highlighted

@Nwat1993 

Some users prefer to hide the formulas so that no one could accidentally or intentionally modify them.

Hidding formulas is a 2 steps process:

Step 1:

Select the cell having the formula to hide.

Hit CTRL + 1 to open the Format Cell Dialog box

Check the box for Hidden.

Step 2

On the Review Tab >> Protect Worksheet

If it is protected without password (just for unintentional modifications) then you can unprotect it from here as well. If it password protected then you need the password.

Hope that helps

Nabil Mourad

@nabilmourad True, but if you hide a formula, the formula bar stays blank, it does not show the formula result as the poster of the question mentions.


@Jan Karel Pieterse wrote:

@nabilmouradTrue, but if you hide a formula, the formula bar stays blank, it does not show the formula result as the poster of the question mentions.


You are right @Jan Karel Pieterse  because you will see the result in the cell itself, so what's the need of seeing it in the formula bar (as the name says it's for formulas)

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies