I remember there's a way that you can use find and replace to do following:

=Sum(A1,B1)... to become =Sum(A1, B1:B1)...

Not replacing "B1" with "B1:B1", but replace a single cell reference to become a range cell reference.

If anyone can please help and advise, thanks a lot in advance.

  • 1
    Do you mean the Find and Replace menu option? CRTL-F also gets you there, then click on the replace tab. – gns100 Feb 12 at 23:19
  • No, there's a way that you can find and replace "B" with "B (add special character)", it will give you the result from "B1" to "B1:B1"... I've just forgot what special character I need to add to achieve that. thanks. – Patrick P. Feb 13 at 16:12

I must say no, then, there will be no way to use Find and Replace for your need. The problem is that you need to make a copy of, say, "B3" and place it in the Replace string (because it presently only exists once and it has to exist twice in your desired result) and the functionality does not have a way to do that.

For example, you cannot search for "B?" and find "B3" then have that inserted into your output (Replace) string. It will find that alright, and replace it with whatever you typed, but it will not insert the found string into the Replace string you typed.

All the options of what to replace the "B3" with, even whacky doubled double OFFSET() stuff, still requires that Excel insert the "B3" into the output and it will not do that.

There are plenty of formula methods, and of course, the gold standard is to use VBA. In fact, this kind of "same work in the tons of cells quantity" work is one of the biggest uses of VBA.

So it really depends upon whether you just have to have Find and Replace do it (unhappy ending) or if how it's done isn't important, just that it be done (happy ending).

  • Believe it or not, there is a way. When I was doing the budget thing, I used it a lot, to insert a new worksheet cell reference to an existing sum function by using this way, it saved me a lot of time... But it's been a while now, and I've forgot how did I do it., and can't trace to any note and no luck finding it on google either. And I'm really appreciate your time and effort trying to help me on this, thanks! – Patrick P. Feb 14 at 19:46
  • @PatrickP. maybe you can help us by explaining how you used to do it, then - because I don't think there's a way, either. VBA would do the trick nicely. You could also construct a formula to replicate the functionality you're looking for using INDIRECT, FORMULATEXT and MID... but Find & Replace is not going to work for this. – Alex M Feb 14 at 19:54

The only thing I can figure makes sense of the "not B1:B1 but rather a range reference" thing is that, while B1:B1 IS a range reference, it would be a single cell range, even as it is copied down since it is completely relative referencing and both parts of it would change.

With that in mind as the only way I can make sense of the question, simply use Find and Replace to replace B1 with:


which will make it a range reference that begins at B1, always, and goes down to incluse the cell in the B column for whatever row you are on so making the range always be B1 to B-whatever, whatever row you are on. So the formula in row 19 would see the range $B$1:B19.

Another thought occurs seeing the second comment, above. Perhaps column B has a SPILL function in B1 and the poster wants to refer to the whole spill range instead of explicitly stating a range. A person could call that a range reference though it'd be slightly inexact and in a way that implied something else, but it's all new so... In that case, Find and Replace B1 with:


which will specify the entire spill range even if it changes due to changes in its source.

  • Thanks for your answer, but that's not what I meant.. I want to use Find and Replace to do, for example there are formulas =Sum(A1,B1); =Sum(C2,B3)... Find "B", Replace with something, the formulas will become =Sum(A1,B1:B1); =Sum(C2,B3:B3)... – Patrick P. Feb 14 at 15:46
  • @PatrickP. you should edit that information into your question because your question as it stands is difficult to understand. – Alex M Feb 15 at 1:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.