

Subject:
Copying cells in Excel
Category: Computers > Software Asked by: jpalmega 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: historybuffga 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. AB 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. AB 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)  
 
 

jpalmega rated this answer: 

Subject:
Re: Copying cells in Excel
From: shalga on 07 Aug 2002 13:33 PDT 
Hello jpalmega! 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 
Subject:
Re: Copying cells in Excel
From: jpalmega 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: respreega 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 (CTRLC), then place the cursor on the CELL you want to copy to. Then paste (CTRLV). Viola! 
If you feel that you have found inappropriate content, please let us know by emailing us at answerssupport@google.com with the question ID listed above. Thank you. 
Search Google Answers for 
Google Home  Answers FAQ  Terms of Service  Privacy Policy 