Search Not Just Numbers

Loading...

Tuesday, 11 February 2014

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.

Excel Expert Course

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".

3 comments:

  1. Hi Glen,

    I always like to try out your tips on a blank sheet to test for myself and see the outcomes. Your comment about Excel "Replace" not being intelligent with regards to the $A$1 reference, appears to now work. I tried the following simple formula in three adjoining cells both vertically and horizontally and then scattered with blank cells in-between (C2 = 3 and d2 = 10):

    =5*$c$2
    =4*$c$2
    =2*$c$2

    Then, I used the "Replace All" function to replace with $d$2 with success! I am very interested to try this out on other current spreadsheets I have with more complex formula. Thanks for this tip.

    Carl
    Surrey, BC, Canada

    ReplyDelete
    Replies
    1. Carl - that should work fine. What I meant was that Replace A1 will not replace $A$1. Replace $A$1 will.

      Sorry if that wasn't clear.

      Delete
  2. Erasing memories of people seems replica cartier like a heartbreaker of a job. People come in wanting to forget spouses, significant others, job loss and so on. This is the perfect job for people addicted to reality rolex datejust replica TV. You get to watch the most dramatic moments of people lives http://www.lakesideshopping.com/store1.html just before they disappear. They might not remember http://www.artabloom.com/err.html but you will. And it sure beats working at Rekall.

    ReplyDelete