apply formula to multiple cells

[James]

I have a large spreadsheet with cells already filled with data. I
want to apply a formula to each cell using the existing data in each
cell. I have found a way by slecting all the cells I want to add my
formula to and using the paste special function and multiply function.
This works out ok but I would like to apply the formula to the value
and have show up as a formula in the formula bar. If I use the
multiply function it shows up as a value.

 

[Frank Kabel]

Hi
what formula do you want to apply?. Normally you just can copy the
formula to the other rows

 

[Bob Umlas]

If the cells to be multiplied are already formulas then after the paste
special, the formula will be intact. For example, if the source is =3+4 and
you copy a 7 and multiply it, the result will be =(3+4)*7

Bob Umlas
Excel MVP

 

[James]

Thank you for your quick answers. I just noticed what you were saying
about the formula. I guess I would have the enter an equal sign before
and that would make the cell a formula. So I would enter =10 in cell
a1, =20 in cell h3 and so on. Then they would be formulas so that when
I use copy 1.07 in a cell and paste special and multiply then the
cells would look like =(20)*1.07 . Thats just what I need. I would
like to save time so is there a way to apply the equal sign to
multiple cells before values I have to turn into formulas so i would
not have to press = before every value. Make any sense?

 

[Frank Kabel]

Hi
no this won't happen. This could only be achieved with VBA

 

[Debra Dalgleish]

You can try the following on a backup copy of your workbook:

Assuming your numbers are in cells A1:C500, on Sheet1 --
Select a blank sheet in the workbook
In cell A1, type: ="=" &
Select Sheet 1, and click on cell A1
The formula will now include the cell reference:
="=" & Sheet1!A1
Press the Enter key to complete the formula

Select cell A1, and point to its fill handle
(the small black square at the bottom right)
When the pointer changes to a black plus sign, drag across to cell C1
Then, drag down to row 500, to copy the formula to all cells

Select the entire range, and copy it
Switch to Sheet1, and select cell A1
Choose Edit>Paste Special
Select Values, click OK
With the cells still selected, choose Edit>Replace
Find What: =
Replace With: =
Click Replace All

In a blank cell, type the number you want as a multiplier
Select the cell and copy it
Select the cells with the equal signs
Choose Edit>Paste Special
Choose Multiply, click OK

 

[James]

Thank you Debra. Your way could would work however I get equal signs
even on cells with labels and it messes up the formulas that are
already in my spreadsheet. I guess I will have to get used to entering
equal signs will I fill up my spreadsheet.