Excel Tip: Editing multiple formulae

I've got a simple tip this week that you have probably used elsewhere, but most Excel users I meet do not know that it can be used for this purpose.

This is a nice little trick for that situation when you need to make the same change to many formulae in different cells.

This can be a very time-consuming task, particularly if the spreadsheet has not been well-structured in the first place. If it has, at least all variables, etc. should have been referenced to cells rather than hard-coded in the formula. See my earlier post for some principles on good spreadsheet practice.

Sometimes though, you're stuck with making a change like this. The good news is that a simple tool that is available throughout Microsoft Office can be used.

This tool is the Replace facility.

Many of you will have used this to replace text in a Word document, or maybe you've used it in an Excel document, but did you know that it works exactly the same within formulae?

Yes, say you have a range of formulae that refer to cell A1 for a particular variable and you need them to now refer to cell B3. You can use Replace to make the change.

Simply highlight the range with the formulae that need changing, click the binoculars on the Home ribbon and choose Replace and fill the boxes in as below:

Click Replace All and Excel will replace the text A1 with B3 throughout all of the formulae.

Note of caution:

This is a simple text replace, so is not intelligent. For example:

$A$1 will not be changed
A113 will become B313

Having said that, with a little thought about the consequences beforehand, you can usually do the Replace in such a way that will solve your problem.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".