• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 423
  • Last Modified:

Copying Excel Formulas

How do I copy and paste a cell with an Excel formula to another part of the spreadsheet, so the exact formulas and results appear in the new cell (and not changing any of the cell references.)?

Tim Jackoboice
Tim Jackoboice
  • 2
  • 2
  • 2
  • +3
1 Solution
zorvek (Kevin Jones)ConsultantCommented:
Use absolute references.

A relative reference means that the row or column reference is relative to the referencing cell and, when the formula is copied to other cells, the relative cell references are shifted such that the distance or offset between the referencing cell and the referenced cell remains the same. An absolute cell reference means that the row or column reference is fixed and does not change when the formula is copied to other cells.

With the A1 reference style a dollar sign is used to create an absolute reference. For example, the cell reference A$1, when copied to other cells, will always reference row 1 but the column may change if copied to another column. The cell reference $A$1 will allays reference cell A1 regardless of where it is copied.

Whether a row or column is an absolute or relative reference makes no difference in how the formula is calculated or the result it produces. They only have different effects when formulas are copied to other cells. For this reason, the decision as to whether cell references are absolute or relative should be made with regard to whether or not the formula will be copied to other cells. While entering each cell reference in a formula, consider how the referenced cell should be treated as the formula is copied to other rows and columns. If the referenced cell's row should be the same then make the row an absolute reference by placing a dollar sign in front of the row number. If the column should be the same then place a dollar sign in front of the column letter.

Note that relative references are not shifted when a formula is moved by either cutting and pasting or by dragging. Relative cell references are only adjusted when cells are copied.

Manually, you would need to copy the formula directly from the formula bar rather than copying the cell.

Then you would paste it into the destination cell, again via the formula bar.

That's how I do it anyway perhaps there's an easier way.

In code you it's a little easier:

' copy the formula from A1 to Z1

Range("Z1").Formula = Range("A1").Formula
Well there you are, there are other ways to do it.

Knew I was missing something.:)
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Saqib Husain, SyedEngineerCommented:
Deleted cross post
Go to the original cell
Click in the formula bar, then press CTRL+A then CTRL+C  (select all, copy)
Go to the destination cell
Click in the formula Bar
Press CTRL+V (paste)
Elton PascuaCommented:
I need to do this sometimes so here's how I do it:

1. Select the cells with formula
2. Press CTRL+H
3. Replace all = with #
4. Copy and paste the cells to the destination sheet
5. Press CTRL + H
6. Replace all # with =
Tim JackoboiceOwnerAuthor Commented:
This is the best solution to my question, and likely due to the fact I left one thing out: I had a block of several formulas I needed copied and pasted, without wanting to change the original cell references. I apologize for the omission of that fact.

The $absolute value suggestion would work, but would be too time-consuming, adding all the $absolute value designations to the formulas. The manual suggestions would work, too, but would also be time-consuming, for the same reason.

I had never heard or thought of replacing the "=" signs with "#", copying and pasting, and then reversing the replacements again. This worked great with just a few steps, and I'm back in business. Thanks, techfanatic, and everyone else who offered suggestions.
Saqib Husain, SyedEngineerCommented:
Although the answer is accepted but for the sake of completeness I would like to add this.

The accepted solution may work for this particular case but there would be cases where this may cause a problem. An example would be cases which contain #s within the formula and after completion of the process the original #s would be changed to =s

This can easily be taken care of by using a different character instead of the #

A more trouble free method which I use successfully and frequently is as follows

- Copy the given range blank area horizontally to the right of the selected range

- Move this new data further to the right of the copied range by the same number of cells.

- For example if you copy the range 5 cells to the right then move it further 5 cells to the right.

- Copy it back the same number of cells (copy it back 5 cells)

This range will contain the same formulas as in the given range.
Tim JackoboiceOwnerAuthor Commented:

Thanks so much for your follow-up answer ... I really appreciate it, even after I had marked  one of the answers for the solution already.

That's what I enjoy so much about EE -- everyone offers so much help, and so thoroughly!

Have a great week, ssaqibh.


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now