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.

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.

---A----B
5  100
6  200  =A5
7


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.

---A----B
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
follows:

 =A5*$C$1

================================================================================

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

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

Regards,

historybuff

search strategy:

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

---

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

jpalme,

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

Regards,

historybuff

---

Clarification of Answer by historybuff-ga on 08 Aug 2002 08:28 PDT

Please request a refund for this question.
Thank you!

historybuff

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

Cheers,
Shal

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?

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!