keyboard shortcuts - How to copy-paste absolute cell references temporarely as relative references in Excel?

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.

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

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 

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.

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

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

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

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