Google Answers Logo
View Question
Q: Copying cells in Excel ( Answered 2 out of 5 stars,   3 Comments )
Subject: Copying cells in Excel
Category: Computers > Software
Asked by: jpalme-ga
List Price: $10.00
Posted: 04 Aug 2002 02:28 PDT
Expires: 03 Sep 2002 02:28 PDT
Question ID: 50394
When I copy cells in Excel containing formulas, Excel adjusts
the cell references. For example: =AVERAGE(K7:K63) is
adjusted to =AVERAGE(K17:K73) if I copy it two an area ten
cells lower.

How can I get Excel to copy the original cell content,
and not adjust the cell references. I know that I can do
this for a single cell by copying the cell content, but
I want to do it for all cells in a selected area.
Subject: Re: Copying cells in Excel
Answered By: historybuff-ga on 06 Aug 2002 10:56 PDT
Rated:2 out of 5 stars
Hello jpalme,

You're in luck!  This is not difficult to accomplish.  I've placed
exclamation points below to mark the exact phrase that tells you how,
though it will probably be most helpful to read the entire answer
shown here, taken from the Excell help feature:

The difference between relative and absolute references

When you create a formula, references to cells or ranges are usually
based upon their position relative to the cell that contains the
formula. In the following example, cell B6 contains the formula =A5;
Microsoft Excel finds the value one cell above and one cell to the
left of B6. This is known as a relative referencing.

5  100
6  200  =A5

When you copy a formula that uses relative references, the references
in the pasted formula update and refer to different cells relative to
the position of the formula. In the following example, the formula in
cell B6 has been copied to cell B7. The formula in cell B7 has changed
to =A6, which refers to the cell that is one cell above and to the
left of cell B7.

5  100
6  200  =A5
7       =A6

!!!!!If you don't want references to change when you copy a formula to
a different cell, use an absolute reference.!!!! For example, if your
formula multiples cell A5 with cell C1 (=A5*C1) and you copy the
formula to another cell, both references will change. You can create
an absolute reference to cell C1 by placing a dollar sign ($) before
the parts of the reference that do not change. To create an absolute
reference to cell C1, for example, add dollar signs to the formula as



In other words, when you create your formulas, make sure each and
every one is created as an ABSOLUTE formula.  You should be able to
cut and paste the blocks anywhere you like without them shifting on

If I somehow did not understand your question correctly, please let me
know and I'll readdress if after your clarification.



search strategy:

copy absolute formula (in the MS Excell help feature, no search engine

Request for Answer Clarification by jpalme-ga on 07 Aug 2002 01:27 PDT
I realized after I wrote my question that I did mot enough clarify my
question. I am aware of the "$" convention. But this convention
requires me to specify once and for all that a certain value is either
absolute or relative. I want to decide, every time I do a paste,
whether to paste absolute formulas (i.e. unchanged formulas) or
relative formulas (i.e. with cell references shifted).

Clarification of Answer by historybuff-ga on 07 Aug 2002 07:23 PDT

I have asked the editors to remove my answer.  I hope someone has a
solution for you!



Clarification of Answer by historybuff-ga on 08 Aug 2002 08:28 PDT
Please request a refund for this question.
Thank you!

jpalme-ga rated this answer:2 out of 5 stars

Subject: Re: Copying cells in Excel
From: shal-ga on 07 Aug 2002 13:33 PDT
Hello jpalme-ga!

If I understand your question correctly I think using the "Paste
Special" function will help you.

You can highlight the area you want to copy and paste then:
1. go to "Edit" select "Copy"
2. highlight the cells you want to paste into
3. go back to "Edit" select "Paste Special"
4. then you can select from a list of items what you want to paste,
i.e. all (including formulas with cell references shifted), formulas
only, values only, formats only, etc)

The short cut for "Paste Special" is alt + E + S +

A = all
F = Forumulas
V = Values
T = Formats

Other short cuts for the special item you want to paste are noted with
an underline in this menu.

I hope this is helpful (sorry if you already knew this and it is

Subject: Re: Copying cells in Excel
From: jpalme-ga on 08 Aug 2002 06:58 PDT
That sounds like the functionality I need. When I open
"Paste special" there is no choice "formulas only",
but maybe I should geta newer version of Excel. I am
using Excel 2001 for the Macintosh, maybe the
function I need was not available in that version
of Excel?
Subject: Re: Copying cells in Excel
From: respree-ga on 15 Aug 2002 17:26 PDT
Rather than having your cursor in the cell that you want to copy from,
put the cursor in the "Formula Bar" that displays for formula. 
Highlight the entire formula, copy (CTRL-C), then place the cursor on
the CELL you want to copy to.  Then paste (CTRL-V).  Viola!

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy