Solved

# Copying Excel Formulas

Posted on 2012-09-02
419 Views
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.)?

Thanks!
0
Question by:Cactus1994

LVL 81

Expert Comment

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.

Kevin
0

LVL 33

Expert Comment

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
0

LVL 33

Expert Comment

Well there you are, there are other ways to do it.

Knew I was missing something.:)
0

LVL 43

Expert Comment

Deleted cross post
0

LVL 70

Expert Comment

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)
0

LVL 8

Accepted Solution

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 =
0

Author Closing Comment

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.
0

LVL 43

Expert Comment

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.
0

Author Comment

ssaqibh:

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

Have a great week, ssaqibh.

Tim.
0

## Featured Post

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …