why is my 'find and replace' function automatically set to look in formulas?

[Ray707]

Hi all,

I'm wondering if you can help. I use the 'find and replace' function a lot in excel in my new job but when I click on 'ctrl F' to open this function up, the part that says 'look in' is set to 'formulas'. This drove me crazy yesterday as I was validating data in a file and trying to search for unique identifiers but it kept saying the values were not in there when I knew it 100% was. Eventually I realised that it was due to the fact that the 'look in' was set to 'formulas' instead of 'values'. I have never experienced this problem before with excel- how can I change it to values and maintain that moving forward? It keeps reverting back.

I found a picture of what I am referring to on google. The 'look in' part needs changing to always say 'values':



Thanks

[rollis13]

No idea how you got to there by what happens if you select 'Delete Find What format' from the dropdown of 'Format...' ? does it definitely clear the current settings ? or is it grayed out ?
Which version of MS Office are you using ?

[Fluff]

how can I change it to values and maintain that moving forward?

Unfortunately, you can't. At least not permanently. If you change the setting xl will remember it, but the next time you open xl, it will be back to the default settings

[Ray707]

No idea how you got to there by what happens if you select 'Delete Find What format' from the dropdown of 'Format...' ? does it definitely clear the current settings ? or is it grayed out ?
Which version of MS Office are you using ?

the box is greyed out. My version of excel is office 365 16.0.12527.21962.

[Ray707]

Unfortunately, you can't. At least not permanently. If you change the setting xl will remember it, but the next time you open xl, it will be back to the default settings

Really? This is so odd. I've never had this problem before in previous jobs- why has it occurred all of a sudden on this laptop?

[Fluff]

It has always been like that.

[p45cal]

Really? This is so odd. I've never had this problem before in previous jobs- why has it occurred all of a sudden on this laptop?

I'm not going to be authoritative on this, but I think it's always been like this; when you have it set to look in formulas, if a cell doesn't have a formula it will still search what's in that cell, because what's in the cell is the cell's formula even if it isn't an actual formula.

[Ray707]

Looks like I can't do much about it.

Thanks anyway guys!

[p45cal]

Is your file using macros? If so you can make sure that setting is set to what you want, either on opening the workbook or activating the relevant sheet. Come back if you want to know how.

[Ray707]

The problem is the setting seems to be permanently set to formulas so I would have to implement this macro in every new file I create moving forward which is a hassle. I wish there was an option to simply set to values permanently.

I think the easier option will be to try and remember to set to values when I first open an excel file but I use excel so much this will be impossible to remember every time