Adding to the formulas of many cells at once

homie

Board Regular
Joined
Nov 30, 2014
Messages
66
Hi,

I have a long list of rates (hourly wages) in my spreadsheet. These are hardcopy numbers (values), not formulas. I would like to add an escalation to the rates by referencing a cell where I can enter an escalation factor.
For example:
In cell A1 I write an escalation value. For example 102%. So if I multiply the hourly rate of 345 by this factor, the rate is increased by 2%.
A1: 102%

Colomn B:
345
786
554
325
986
...

Instead, I would like the contents of colomn B to read like this:
Colomn B:
=345*$A$1
=786*$A$1
=554*$A$1
=325*$A$1
=986*$A$1

So, I would like a way to add *$A$1 to all cells in Colomn B without manually typing it in each cell. Is there a way to do that without using Macros/VBA?

Yes, I know I can just use Colomn C and write in cell C2=B2*$A$1 and I would have the values I want in that colomn. This does not fit with the format of my spreadsheet which is used in my company, and is not an option. Yes, I could also just temporarily get the values from colomn C as discussed above and copy-paste values into colomn B and then delete colomn C again. Again, that is not what I want to do. I want colomn B do be dynamic and have references to A1 (escalation factor).

Thank you so much for your help in advance :) There are always a lot of smart Excel users on this forum.

P.S. If this has already been answered, please post a link. I could not find someone posting this problem.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Try this,

If you do not know how to add VBA code... Press Alt+F11 to open the editor, find the sheet module you need (i.e. Sheet1(Sheet1) if your sheet is named sheet1, Sheet1(Rates) if the sheet 1 is called rates) Double click the Sheet1() and paste the following then hit F5 to run it...

Code:
Sub AddFormula()

For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
    Range("B" & i).Formula = "=" & Range("B" & i).Value & "*$A$1"
Next i


End Sub
 
Upvote 0
Use concatenate funciton

345345*$A$1=CONCATENATE(C10,"*$A$1")
786786*$A$1=CONCATENATE(C11,"*$A$1")
554554*$A$1=CONCATENATE(C12,"*$A$1")
325325*$A$1=CONCATENATE(C13,"*$A$1")
986986*$A$1=CONCATENATE(C14,"*$A$1")

<colgroup><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Try this,

If you do not know how to add VBA code... Press Alt+F11 to open the editor, find the sheet module you need (i.e. Sheet1(Sheet1) if your sheet is named sheet1, Sheet1(Rates) if the sheet 1 is called rates) Double click the Sheet1() and paste the following then hit F5 to run it...

Code:
Sub AddFormula()

For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
    Range("B" & i).Formula = "=" & Range("B" & i).Value & "*$A$1"
Next i


End Sub

Thanks, but I was wondering whether there is a way to do this without having to rely on VBA/Macros? This is a problem that comes up quite frequently, so I find it odd that Excel would not have a way to add to several cell's formulas in one, easy action.
 
Upvote 0
Use concatenate funciton

345345*$A$1=CONCATENATE(C10,"*$A$1")
786786*$A$1=CONCATENATE(C11,"*$A$1")
554554*$A$1=CONCATENATE(C12,"*$A$1")
325325*$A$1=CONCATENATE(C13,"*$A$1")
986986*$A$1=CONCATENATE(C14,"*$A$1")

<tbody>
</tbody>

<tbody>
</tbody>
Nope, that does not give what I asked for in the OP. I don't want the cells do display the text "345*$A$1". I want them to perform the calculation.
 
Upvote 0
Thanks, but I was wondering whether there is a way to do this without having to rely on VBA/Macros? This is a problem that comes up quite frequently, so I find it odd that Excel would not have a way to add to several cell's formulas in one, easy action.

The only way to do this outside of VBA is to use a secondary column to perform the calculations...

Excel will only allow EITHER a formula or straight data within a cell... not both. So if you add a formula to an already populated cell, it will over write with the formula and not pick up the data currently there. If you do not wish to use VBA your only option, as far as I am aware, is to do the one thing you stated originally that you did not want to do.

Sorry, maybe someone else has more info...
 
Upvote 0
If i understand that you want to multiply the $A$1 value to All cell B:B
Then copy $A$1 and select column B:B and paste special and select Multiply in operation section.
 
Last edited:
Upvote 0
... I find it odd that Excel would not have a way to add to several cell's formulas in one, easy action.
Well, it is sometimes possible to do that, however, you stated that you do not have formulas in the cells:
I have a long list of rates (hourly wages) in my spreadsheet. These are hardcopy numbers (values), not formulas.

As I see it you have 2 options.

1. This is not dynamic but requires less 'helper' cells.
- Put 1.02 (not 102%) in a vacant cell and Copy that cell.
- Select your column B data and use PasteSpecial -> Multiply


2. Some work to set up initially, but then dynamic.
I'm assuming first value is in B2.
- In a vacant column put this formula in row 2 and copy down: ="=$A$1*"&B2
- Select that column of formulas and Copy then PasteSpecial -> Values
- With the column still selected, Find/Replace to replace = with =
- With the column still selected Cut and Paste into column B
- You now have your formulas in column B and can change the A1 value and have them update dynamically from now on.
 
Upvote 0
Try this

In the adjescent column, say column C
Put =$A$1 and fill down to fill the whole column next to B
Copy Column C
Highlight column B
Right Click - Paste Special - Formulas - Multiply - OK.
 
Upvote 0
Try this

In the adjescent column, say column C
Put =$A$1 and fill down to fill the whole column next to B
Copy Column C
Highlight column B
Right Click - Paste Special - Formulas - Multiply - OK.
Good one Jonmo, I don't think I have seen PasteSpecial used with that combination before. :)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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
Back
Top