View Question
 Question
 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:
 ```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!```