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:
- making a copy of the entire worksheet (ctrl-drag worksheet tab to new location),
- then cutting (ctrl-X) the relevant block of cells from the new worksheet
- pasting (ctrl-v) into the original worksheet.
- 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)?