Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
Join Date
15th January 2007
Posts
5

## Adding The Same Formula To All Cells?

I recieve cost sheets from a company and I want to be able to change there number into a formula and mark it up all in one. How would I do this without going into each cell individually and make the change? Example say the number is \$100 in the cell I want to change that to =\$100/.6 to get a 40% margin. Then I can give this to salesmen with just the retail number but I can see the formula and cost. Probably easy to do if I knew how please help!

Thanks

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Adding The Same Formula To All Cells?

Hi

Welcome to Ozgrid.

Did you try Edit > Replace?

3. Long Term Member
Join Date
13th May 2006
Location
India
Posts
1,030

## Re: Adding The Same Formula To All Cells?

Is threy typo error . is it not 100*.6 . anyhow whatever be the formula try this

suppose A1 is 100 in B1 type
=A1*0.6
now copy B1 down ny copy paste method.

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
26th January 2003
Location
Derbyshire,UK
Posts
19,468

## Re: Adding The Same Formula To All Cells?

The easiset way would be to enter 1.4 in any empty cell then Copy it. Now select all the cells with the values to change & go to Edit > Paste Special & check Multiply. Now delete the entry of 1.4

5. I agreed to these rules
Join Date
15th January 2007
Posts
5

## Re: Adding The Same Formula To All Cells?

I am not familar with the Edit> How does this work? Stay basic for me, I know only the basic stuff of excel.

Venkat, I tried this but it took the number from the original cell and formula both. All costs are different in each cell. If a1 is 100 and B1 200 when I copied A1 into B1 it took the formula and 100 not the formula and applied it to the 200.

Excel Video Tutorials / Excel Dashboards Reports

6. Super Moderator
Join Date
26th January 2003
Location
Derbyshire,UK
Posts
19,468

## Re: Adding The Same Formula To All Cells?

Originally Posted by fryguy
I am not familar with the Edit> How does this work? Stay basic for me, I know only the basic stuff of excel.

Venkat, I tried this but it took the number from the original cell and formula both. All costs are different in each cell. If a1 is 100 and B1 200 when I copied A1 into B1 it took the formula and 100 not the formula and applied it to the 200.
Edit is the Edit Menu at the top of your spreadsheet. You can'treally get more basic than what I suggested!

Edit: I suggest you take the time to fill in your Experience level with in your Ozgrid User Profile
Last edited by royUK; January 15th, 2007 at 21:37.

7. I agreed to these rules
Join Date
15th January 2007
Posts
5

## Re: Adding The Same Formula To All Cells?

OK, I understand what you are saying now. I looked at the replace method but I am unsure of how to write the formula then. Also how do I get it to change the numbers not text and not the blank boxes?

Thank you again!

Excel Video Tutorials / Excel Dashboards Reports

8. Super Moderator
Join Date
26th January 2003
Location
Derbyshire,UK
Posts
19,468

## Re: Adding The Same Formula To All Cells?

Originally Posted by fryguy
OK, I understand what you are saying now. I looked at the replace method but I am unsure of how to write the formula then. Also how do I get it to change the numbers not text and not the blank boxes?

Thank you again!
Have you tried what i suggested? I've already said it is the easiest way, you don't need to use replace!

9. I agreed to these rules
Join Date
15th January 2007
Posts
5

## Re: Adding The Same Formula To All Cells?

OK, I had two methods in front of me and was trying the first one but on to your way. I tried and it didnt work. For one I do want to Divide by and not multiply but when I go to paste special it only gives me two options Unitext or text. Which do I pick and what do I do from there? Still doesnt make sense to me. Also how do I pick the cells with numbers and not the blank cells to paste into?
Last edited by fryguy; January 16th, 2007 at 01:25.

Excel Video Tutorials / Excel Dashboards Reports

10. Super Moderator
Join Date
26th January 2003
Location
Derbyshire,UK
Posts
19,468

## Re: Adding The Same Formula To All Cells?

It is really simple, if you type .60 in any empty cell & Copy.

Next select all the cells with values in that you want to change.

Now from the Edit Menu select paste Special

A dialog is displayed with various options, one of which is Divide.

Click OK & the calculation will be done.

Last delete the .6

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno