Excel: Copying with Relative Links

Copying a range of cells but keeping the targets for relative links? Doesn’t quite work as you expected. Or hoped.

The greens cells below each contain a relative link to the “data” cell five places to the left.

An Excel spreadsheet. A region of data and another region with relative links to the original data.

If we simply Copy and Paste those green cells then we see that the relative links move as well. This is often what you want to happen. Often but not always. What about if you want the links in the copied cells to be preserved? None of the various Paste options offered in Excel will do this.

An Excel spreadsheet. Effect of simple copy-paste on region containing relative links.

But there is a way around this problem. First Copy the range of cells in question and Paste to a temporary location. Next Cut the range of cells and Paste to the new location. The Cut and Paste action will not alter the relative links.

An Excel spreadsheet. Cut-paste the region containing relative links to temporary location. Copy-paste to create new region with updated relative links.

Finally Copy and Paste from the temporary location back to the original location. Delete the temporary copy.

An Excel spreadsheet. Copy-paste back from temporary location.