Copying Excel Formulas

Posted on 2012-09-02
Last Modified: 2012-09-03
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.)?

Question by:Cactus1994
    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    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.

    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
    LVL 33

    Expert Comment

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

    Knew I was missing something.:)
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Deleted cross post
    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)
    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 =

    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.
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    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.

    Author Comment


    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    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 …
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now