Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In Excel (2007), when cells that contain absolute references (for example: $A$3) are copied, the absolute reference remains the same. This is by design, and the reason of using absolute references.

Problem -- However, sometimes I want copy a block of cells (that contain absolute and probably also relative references), and paste them with the absolute references shifted correctly for the new block. That is, I want the absolute references to behave like relative references when copying, but still be absolute references in the final copied result.

Example -- In the example screenshot, I want to copy the block A2:B3 downwards. When copied, I basically want to have the formula in B3 (=$A$3) changed so that it refers to the cell to the left of it, for example becoming =$A$11 when copied to B11, as in the bottom part of the screenshot.

example in Excel

Workaround -- I have found a workaround to do this, by:

  1. making a copy of the entire worksheet (ctrl-drag worksheet tab to new location),
  2. then cutting (ctrl-X) the relevant block of cells from the new worksheet
  3. pasting (ctrl-v) into the original worksheet.
  4. finally deleting the new, temporary worksheet (right-click worksheet tab and delete).

Question -- But this is too many actions for my taste. Is there an easier way (perhaps some Paste Special hidden option)?

share|improve this question
    
Why not just delete the second $ sign? Make cell B3 =$A3 instead of =$A$3. – Kevin Anthony Oppegaard Rose Feb 27 '13 at 9:40
    
@Kevin: This is just a toy example. In the real case (some months ago) I had something like a 20x20 block, where I had a few 'configuration' cells which were in a fixed position (relative to the block). For constructing the block (ie. expanding from a single row to 20 rows ), it is the most convenient to work with absolute cell references. However, when I make a copy of the block (to see the result with other values for the configuration cells) in any direction, the absolute cells lead to the problem described in my post. – Rabarberski Feb 27 '13 at 12:34
    
My guess is that you have tried the entering =$A3 in the cell then copying it and then Paste the Formula only in the target cell..... – Darius Apr 3 '13 at 23:34

you could always try writing a macro for that. excel has a really nice macro recording tool that you could use too, and then just run it as needed (provided you make some changes first of course to the program)

dim firstLetter as String 
dim secondLetter as String 
dim firstNumber as integer 
dim secondNumber as integer 
dim firstReference as string 
dim secondReference as string 
dim contents as string 
firstLetter = inputbox("Where's the first column? (it's letter)") 
firstNumber = inputbox("And what's the first row? (just the number)") 
secondLetter = inputbox("What column is this going to be moved to? (the letter only.)") 
secondNumber = inputbox("And what row? (the number.)") 
contents = range(firstletter + cstr(firstnumber)).formula 
range(secondletter + cstr(secondnumber)).formula = contents 
share|improve this answer
    
This isn't a recordable process. Obviously you can always write a custom macro to automate something but it would be more helpful to attempt to give some sample code. – lori_m Mar 13 '12 at 14:37
    
dim firstLetter as String dim secondLetter as String dim firstNumber as integer dim secondNumber as integer dim firstReference as string dim secondReference as string dim contents as string firstLetter = inputbox("Where's the first column? (it's letter)") firstNumber = inputbox("And what's the first row? (just the number)") secondLetter = inputbox("What column is this going to be moved to? (the letter only.)") secondNumber = inputbox("And what row? (the number.)") contents = range(firstletter + cstr(firstnumber)).formula range(secondletter + cstr(secondnumber)).formula = contents – SUPER MARIO BROTHERS Mar 15 '12 at 12:21
    
oh good god that didn't carry over well at all from notepad. but yeah, something like that might work -- you MAY have to play around with it a bit but I think 'formula' is the cell's formula where as the r1c1 thing does that but maintains relative reference which is what we don't want to do. – SUPER MARIO BROTHERS Mar 15 '12 at 12:22
    
I've tried to insert your code into the post, you can edit it from there. Hope this helps – lori_m Mar 15 '12 at 12:56

A work around I have used is to:

  • create the block of cells that you want to paste, which includes all of your absolute referennces (in your example the block labled 'Original Block')

  • then create the worksheet you would like to see in the end by copying and pasteing in whatever style formation you like. So to coninue with your example, you would use 'Original Block' and copy it say..10 times below that, which would be the final worksheet layout. (You will notice you still have the same problem with the absolute cell referencing the cell(s) from the original block.)

  • Take and highlight that whole worksheet and CUT and copy to a new worksheet

You will then notice all of cells have been linked with their respecitve absolutes that you desired on your new and improved sheet.

Good Luck :)

share|improve this answer

If I try to follow the steps to cut and paste from a new worksheet I find that all references in formulas stay fixed when copying to the new location including relative references. In fact in Excel 2010 i find that after cutting and pasting formulas, the first row and column contain links to the old sheets but other rows and columns reference the new sheet which looks like a bug??

If you are wanting to copy a block of formulas keeping all references the same, you can press Ctrl+` (backquote) to show formulas and then copy and paste by clicking the icon on the Clipboard task pane (activate using small arrow on clipboard section of Home Tab). If this is not what you are trying to achieve, a simple example would help.

share|improve this answer
    
I've added an example with illustration. Hope that makes it clear. – Rabarberski Mar 13 '12 at 13:56
    
That's clear, but your workaround does not achieve that result for me. I don't think there's an easy way to do this, I'd just do an edit replace for the absolute references i.e. replace $A$3 with $A$11. – lori_m Mar 13 '12 at 14:32
    
strange you have that issue. Mine (Excel 2007) is working fine – Rabarberski Mar 13 '12 at 14:52

I have been trying this macro (stored in Personal.xlsb and bound to shortcut key) to convert references to absolute before copying.

Sub ToAbsolute()
 Dim c As Variant
    Application.ScreenUpdating = False
    For Each c In Selection
        If (Not IsEmpty(c.Value)) Then
            c.Value = Application.ConvertFormula(c.Formula, xlA1, , xlAbsolute)
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

Sub ToRelative()
 Dim c As Variant
    Application.ScreenUpdating = False
    For Each c In Selection
        If (Not IsEmpty(c.Value)) Then
            c.Value = Application.ConvertFormula(c.Formula, xlA1, , xlRelative, c)
        End If
    Next c
    Application.ScreenUpdating = True
End Sub
share|improve this answer

The following will work with less complexity then writing your own macro and achieves the final result.

Yes, I know I'm not using the Absolute cell reference but as shown in OP's example you don't need it.

Select range that you want to copy

enter image description here

Then from the drop down menu of Paste choose the Formulas option as shown here

enter image description here

This has been tested by me and works in Excel 2007 and 2010. Enjoy :)

share|improve this answer
1  
Doesn't work for me (Excel 2007). I think the reason it appears to work for you is because in the formula =$A7 only the column (A) is absolute, but the row (7) is relative. If you use =$A$7 you'll see it doesn't work. In my original example, it is actually the absolute row reference that IS important. – Rabarberski Apr 4 '13 at 7:22
    
The question is, do you really, really (for really :D ) need the absolute reference. I found myself that the second I want to copy the absolute reference it appears I can get away with $ on either column or the row. – Darius Apr 4 '13 at 15:40

One way to tackle this problem is to use only relative references and follow the procedure below to copy the relative references in a way that treats them like absolutes. Sources for this answer include Stack Overflow and this page.

  1. Put Excel in formula view mode. The easiest way to do this is to press Ctrl+` (that character is a "backwards apostrophe," and is usually on the same key that has the ~ (tilde).
  2. Select the range to copy.
  3. Press Ctrl+C
  4. Start Windows Notepad
  5. Press Ctrl+V to past the copied data into Notepad
  6. In Notepad, press Ctrl+A followed by Ctrl+C to copy the text (In some cases, I found I had to go back to Excel and clear the existing selection before doing Ctrl+C in Notepad)
  7. Activate Excel and activate the upper left cell where you want to paste the formulas. And, make sure that the sheet you are copying to is in formula view mode.
  8. Press Ctrl+V to paste.
  9. Press Ctrl+` to toggle out of formula view mode.
share|improve this answer

I had a similar problem. I have a very big spreadsheet with thousands of formulas, some with column absolute, and some with both column and row absolute. I wanted to make a side-by-side duplicate on the same worksheet, instead of making a duplicate on a new spreadsheet. I needed to copy all of the formulas and have Excel treat absolute references as relative references.

Excel will change all addresses as if they are relative when you insert a column to the left of the range of cells with the formulas.

  1. Make a new sheet next to the original sheet. This will be the sheet with the final product.

  2. Go to the original sheet. Highlight the columns with the formulas. Copy.

  3. Go to the new sheet and highlight the same columns. Paste.

  4. Go to the original sheet. Insert the number of columns as your range is wide. For example, my range is 20 columns wide. So I inserted 20 columns to the left of my range, moving all of my formulas and changing all of the references as if they were relative.

  5. Highlight the new columns. Copy.

  6. Go to the new sheet. Highlight the exact same columns on the new sheet. Paste.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.