"Search & Replace" Shock

[DQR]

I've just come across something that I feel is both stupid and dangerous in Excel, and I'm wondering just how many people know about it.

To illustrate the problem, type a 1 into cell A1, and type the formula =1+A1 into cell A2, so A1 contains 1, and A2 now contains 2. Now conduct a search and replace on the whole sheet to replace 1 by 5. You would expect A2 to contain 6, but it doesn't - it contains 5.

Why? Because the search and replace procedure has not only replaced the contents of A1 by 5, but it has ALSO changed the FORMULA in A2 from "=1+A1" to "=5+A5". A5 doesn't contain anything, so A2 now contains 5.

If you check the "Find entire cells only" box in the options of the Search and Replace procedure you will get the correct answer in A2, because the formula is left unchanged - but that isn't the default setting, at least not in my implementation of Excel. Also I hardly think that the instruction "Find entire cells only" is particularly informative to an inexperienced user.

This emerged because I was replacing every "99" in a data sheet by a missing value code. However, the data sheet also contained formulae to calculate the averages of the rows, one of which was in the 99th row...

So, if you've got formulae in your worksheet and fail to check that box before conducting a search and replace operation, every single formula in your sheet is likely to be corrupted - and in a relatively abstract workbook like a balance sheet or a profit-and-loss account you probably wouldn't realise that until it was far too late.

How many other people have come across this little banana skin? My impression is that it isn't many - and certainly not amongst people who use Excel almost exclusively for keyboarding into worksheets that have been created by other people, but who might just occasionally need to use the search-and-replace procedure to correct a coding error.

Cheers,
DQR

 

[cheerio]

Take a look at Edit, GoTo, Special, Constants if you want to avoid formulas. Quite useful to zap all the data in a sheet but leave the formulas.

Ken

 

[xlbo]

Or just change the LOOKAT part of the find to look at VALUES rather than formulae. Sorry but this little "feature" is incredibly helpful in many instances and I view it neither as dangerous nor stupid. If users don't look at the available options or the help file then I'm sorry but it's entirely their own fault - it's not like it can't be undone with the undo button anyway
It's pretty common sense. If you want to change part of the value in a cell, choose values......if you want to replace part of a formula (ie if you need to change a cell ref that is propagated through a lot of formulae), then choose formulas.....it ain't brain surgery.

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[SkipVought]

DQ,

Been Excelling for over a decade and VBAing nearly that long as well. Have gotten "bitten" AND have bitten back. Find and replace is a great tool, properly used.

George Washington once stated,

"Government is not reason. It is not eloquence. It is a force, like fire: a dangerous servant and a terrible master."

May be some analogy there.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bluedragon2]

Yeah, what Skip said

 

[DQR]

Hmmm - I anticipated an immediate response to this, and I was right. To clarify, it's not the functionality of the search and replace function that concerns me, but the ease with which I feel an error in its use can be made, and the difficulty of correcting that error after the event. [As Geoff said earlier, the undo button is always available, and that's true of course - but not if the search and replace was run halfway through a large keyboarding exercise, and the error only spotted when somebody notices afterwards that the results of the calculations don't look right. This is pretty much the scenario that I encountered in the "99" example described in my original note: at the end of a lengthy keyboarding stint I realised that the calculations didn't make sense any more. This is a depressingly realistic scenario, I'm afraid: when you're entering data you aren't looking at the answers, which usually don't make sense until you've finished the task in hand anyway.]

My point was that I can see situations occurring where a complex worksheet containing formulae is being updated by a keyboarder who knows nothing about any formulae embedded in the worksheet, and almost nothing about the search-and-replace function other than how to use it in its most basic form. In short, I feel there's a failure here to anticipate (a) the way in which Excel is likely to be used and (b) the knowledge-base of those people likely to be using it. Before posting my original note I conducted a quick poll around my office. Not one person there knew about the effect of the search-and-replace function on formulae, even though they routinely use it every day of the week - and several jaws dropped when I showed them the effect of it. Now although I cheerfully acknowledge (well, moderately cheerfully anyway) that it's our own fault for not understanding all the options and/or reading the help file on this subject, I do believe that this is one instance of a situation in which we wouldn't be expected to KNOW that we needed to do these things. The search and replace function is sufficiently intuitive that we all thought we understood its use. We were wrong. The question is: are we really the only ones?

Have a good weekend everybody,
DQR

 

[xlbo]

Fair point DQR - I get used to thinking that everyone knows these things automatically. However, I would suggest that your company should teach basic office (including excel) skills - this is where I learnt about the functionality of F&R. You are probably right in that M$ should make it clearer as to what th eF&R functionality is - maybe replacing tick boxes with larger option buttons etc but I think that there is still an onus on companies and individuals to learn about the tools that they use

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[DQR]

Ahh, if only there were the time and the money! We're a small company, and everybody learns these things on the hoof (and I can't believe we're alone in that regard). That is also of course the reason that forums like this one are so phenomenally useful to us. For my part, I tend to forget that larger organizations have the resources to send their staff on courses. Following on from that thought however, I'd guess that something as specific as the point we've been discussing wouldn't be appreciated during a training course by somebody who was new to Excel anyway: one needs to have a basic grounding in its use before the relevance of some of the more detailed points fall into place. I've found in the past that training courses need to be undertaken after you know the basics but before you've got into bad habits. The problem is recognising when the bad habits have started to creep in - something that we've all recognised in our usage of Excel is that once you've discovered how to do something, you stop trying to find a better way to do it, which is often a mistake.

Bye for now: it's time to go home at the end of a long week, and I think I'll forget about Excel for a couple of days.

Best regards,
David (DQR)

 

[SkipVought]

See my reply in thread68-587736.

It is the bane and blessing of Excel.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com