Adding formula to multiple cells

kshora

Board Regular
Joined
Aug 2, 2006
Messages
211
Hi

I have a large work sheet with existing data. How can I add a formula to all the data (cells with data) at one time. Entering the formula one cell at a time is not possible e.g there are 10 cells with data and I want to add a same formula say multiply all 10 cells by $A$100, so that every time I change A100 cell value, all the corresponding cell value changes

Help is highly appreciated

Thanks and regards

Shora
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Shora

You can probably manage it with a Find/Replace:

1.Select your cells
2. Press Ctrl+H
3. Type = into the Find What box
4. Type =$A$100* in the Replace With box
5. Hit Replace All

Hope this helps!

Richard
 

kshora

Board Regular
Joined
Aug 2, 2006
Messages
211
Hi

But I already have data in these cells, copy and paste will not workk as the data is different in each cell.

Find/Replace does not seem to work as there is no "=" in the cells to replace.

Say I have 28.8 in one cell, 35.2 in another cell, 40.12 in another and so on and I want to multiply them by $A$100 and want to add *$A$100 to all the cells, how do I do that in all the cells at one time

Thanks and regards

Shora
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Shora

There's no easy way if you just have values - but you can type say 100 into another cell, copy the cell, select your range and go Edit>PasteSpecial and click on the multiply box and then click OK.

Richard
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Goo afternoon kshora

Press Alt + F11 and go to Insert > Module, and copy this code into the window that appears :

Code:
Sub test()
For Each UsrCell In Selection
UsrCell.Formula = "=" & UsrCell.Value & "*A100"
Next UsrCell
End Sub

Click the red X in the corner to close. Select all the cells you want to apply the formula to and go to Tools > Macro > Macros and select test from the box and click on Run. You can now delete the macro if you wish.

HTH

DominicB
 

kshora

Board Regular
Joined
Aug 2, 2006
Messages
211

ADVERTISEMENT

Richard
Thanks for the reply, I had a feeling that this won't be easy, the problem is that the value to multiply also changes and I have to paste special every time, I have around 2500 cells to work with, please let me know if you come up with a better solution.

Thanks and Regards

Shora
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
There's no native way within Excel, but Dominic's shown you one way with VBA.

Another, non-VBA route might be to use an identically sized range that references back to the original so eg on another part of the sheet/on another sheet you have:

=A2*$A$1

and copy across and down as far as your original data requires. That way you can see your original values and also the effect of multiplying by whatever factor you place in A1.

Hope this helps!

Richard
 

kshora

Board Regular
Joined
Aug 2, 2006
Messages
211
DominicB

Thnaks a million, the macro works like magic.

Thanks once again

Shora
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Hi kshora

Alternatively, you could download John Walkenbachs's PUP add-in : it is shareware so if you're going to keep it you should pay him for it but he has included a tool on here called "Math Without Formulas". You can get a fully working evaluation version from here :

http://j-walk.com/ss/pup/pup6/index.htm

HTH

DominicB
 

Forum statistics

Threads
1,141,679
Messages
5,707,786
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top