Software

Ask something
Discussion Locked

Copying formulas in excel

0 helpful
8 replies

by rdl

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I'm trying to copy a formula from 1 cell to the rest of the cells in the same column, but it's copying the results, not applying the formula. How can I change this. Thanks

8 replies

by dklippert

If there is a column next to the formula that extends to the bottom of the list, double click on the Fill handle (the little box in the lower right corner of the Active cell)
What results is it copying? If it's repaeting the original results, look at the formula and see if it is using Absolute Addreses ($A$1).
How are you copying and pasting?

by rdl

I am highlighting the cell with the answer in it, taking the fill handle and dragging it down the column so it should copy the formula from that cell and apply it to every cell. It's just copying the value of the cell, not the formula. I'm not sure about the absolute address, how do I change that? thanks

by dklippert

Check Tools>Options>Calculation to make sure it is set for Automatic.

******
An Absolute address does not adjust as the formula is copied from one spot to another.
It uses a $ sign to fix the location of the target cell. $A1 will always be in the A column A$1 will always be in row 1.
$A$1 refers to cell A1 no matter where the formula is moved. You can change Absolute to Relative addressing, by removing the dollar signs.
Either edit the formula, or click in the address in the formula bar and hit the F4 key to toggle all the combinations.

by dklippert

You can check if the calulation is working, by hitting F9 after pulling the Fill handle.

by dsexton

If you wish to have the formula copied to multiple cells do the following: right click on the cell with the correct formula; choose COPY from the drop down box; A moving marquee will begin "circling" the copied cell; left click and drag down to select the destination cells; right click on the highlighted cells; click on FORMULAS and then okay. It will copy the cell in relative or absolute form (see answer 2 for the clarification between the two formats).

by chitosunday

Use the simple copy paste and not paste special values but be sure what you are copying is a formula. If it is a value, you cannot convert it to a formula no matter what.

This discussion is closed to new replies.