change multiple formulas at once

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

It is possible to change formulas in multple cells at once. For example, if I had 100 cells with their own different formulas and I wanted to mulitply them all by one particular cell or value. How could I do that so that I don't have to edit each cell separately
 
one way

highlight all the cells you want to change

Use edit, replace.
in the find what box type =
in the replace with box type =A1*

this will change all the formulas to be multiplied by A1

hth

Mik
 
Hi,

Personally I would use a small bit of VBA, but it can be done manually.

First put the value you wish to apply in an empty cell in the worksheet
concerned.
Say we are going to increase all the cells with NUMERIC formulas 10-fold.

So put 10 in the said empty cell and copy it (you'll then have the familiar
marching ants).
Next press F5 and the GoTo dialogue box appears.
Press "Special", now select in the new menu "Formulas" and remove the ticks
in the boxes
against "Text", "Logicals" and "Errors" (we are only going to cells with
numbers-formulas).
Press OK.
Appropriate cells are now selected, whilst copied cell still has its
marching ants.
Next select Edit>Paste Special.
Choose (in this case) "Multiply" in the Operations section and press OK.
Said formulas have "*10" added to them and displayed values adjust
accordingly.

Hope this helps, but save your workbook before you do whatever in case of a
foul-up.

Regards

Paul










squizzman said:
It is possible to change formulas in multple cells at once. For example,
Click to expand...
if I had 100 cells with their own different formulas and I wanted to
mulitply them all by one particular cell or value. How could I do that so
that I don't have to edit each cell separately
 
squizz

Enter your multiplier number in a cell.

Copy this cell.

Select your range of 100 cells to change and Edit>Paste
Special>Multiply>OK>Esc.

Gord Dibben Excel MVP
 
Back
Top