How to change a formula

I have the same formula in multiple cells and I want to change this formula in all these cells. This is a simple task if the formula is placed in a continuous range. I do it thus:

- select the range
- press F2
- write a new formula
- press ENTER

It's done, the new formula is inserted in all selected cells.

Of course, I can do it in the same way if the formula is in a several unconnected areas. The only thing I have to do is to select the areas at once (with the CTRL key). This is possible and I do it often. But there can be a lot of areas or some can be large then this way becomes a little uncomfortable. So what now?

I ask: "Can I replace this formula in a single step?" And this question is also the answer. Yes I can replace it.

For example I have a formula "=B1+C1" in cell D1, "=B2+C2" in D2 etc. I know this is the same formula, only the Excel shows different row numbers. So everything I have to do is to persuade the Excel to show the same formula in all rows.

And that is no problem, because the R1C1 reference style exists in the application. So I switch the reference style to this one.

I can see the same formula in the cells. That's what I want and I can use the next method:

- select the all range where I want to change the formula
- open the Find/Replace dialog (e.g. by pressing CTRL+H)
- Find what: =RC[-2]+RC[-1]
- Replace with: =RC[-3]+RC[-1]
- click on the Replace All button
- switch the reference style to A1

Do you know another use of the R1C1 reference style? Let me know.

Tweet