How to Paste Links to Source Cells Instead of Values

Paste Links in Excel instead of values.

You have finished your calculations and now you are about to present your results? Well built Excel models usually separate the calculations from the results. Therefore “Paste Links” might be helpful for you, especially when your colleagues should not mess your data source.

paste, links, paste links, paste as link, excel, How to Paste Cell Links

This article describes how to paste links to cells instead of values and the next steps for also applying formatting with just a few klicks (or better: key strokes…).

How to paste links instead of values

Pasting links to the source cells instead of values or formulas is quite easy and Excel offers this option within the paste special window. The following number are corresponding to the picture above: 

Paste links: The "Paste Special" window (opens with Ctrl + Alt + V) has a "Paste Link" button in the bottom left corner.
Paste links: The “Paste Special” window (opens with Ctrl + Alt + V) has a “Paste Link” button in the bottom left corner.
  1. Instead of working with the original data, you could just link to it by copying it with “Ctrl + c”. 
  2. Select the cell which you want to paste the copied cells as a link. 
  3. Instead of pasting it now with “Ctrl + v”, paste it using “Ctrl + Alt + v” (Paste Special). 
  4. Click on “Paste Link”.

In case of large Excel models, it’s recommended doing this with a new Excel worksheet, so that your results are really separated from the calculations. As your data is linked to your original source, you can edit the original source as usual and the linked table refreshes automatically. Instead of sending your original data to others, you now only send the new sheet.

Instead of pasting values, the "Paste Link" function inserts cell references to the copied cell range.
Instead of pasting values, the “Paste Link” function inserts cell references to the copied cell range.

Result: This is, how the paste links function looks like:


Next step: Don’t only paste links but also the formatting

After you have inserted simple links, you can next also paste the formatting. That way, the pasted cells look like your original cells.

Because the paste links cells are unformatted, you can in the next step also paste the formatting.
Because the paste links cells are unformatted, you can in the next step also paste the formatting.

The good thing is that usually your original cells are still copied. So, before you click anywhere or type anything, simply re-do the paste special. Instead of pasting links, you paste the formatting this time.

  1. (Just, if your original cells aren’t copied any longer. So, the big green, dashed borders are gone): Copy the original cells again. Also, select the target cell.
  2. Open the paste special window again. Either click on the small arrow of the Paste button on the Home ribbon (on the far left) or simply press Ctrl + Alt + V on the keyboard.
  3. Select “Formats”.
  4. Click on “OK”.
After pasting the formatting, only "0" (zeroes" still bother, when the linked cells are blank.
After pasting the formatting, only “0” (zeroes” still bother, when the linked cells are blank.

That’s it, your cells should look something like this.

And the last step: Hide zeroes

Admittedly, that doesn’t look perfect yet. The zeroes bother me. You can choose between different approaches:

  1. Hide zero values: Please refer to this article.
  2. Delete all zeroes. Therefore, select all zeroes and press Del on the keyboard.
    In case you have inserted a lot of links to blank cells, you might want to use the Search function in Excel to select all zeroes:
If you want to delete all values linking to empty cells, you can use the Find dialog to select all these cells. Once selected, press "Del" on the keyboard.
If you want to delete all values linking to empty cells, you can use the Find dialog to select all these cells. Once selected, press “Del” on the keyboard.

Hold on a second. Was this information helpful so far?
Connect with me on LinkedIn...
Professor Excel at LinkedIn

Boost your Excel skills: Learn the best Excel tricks and tutorials!


One more example: Link to non-coherent cells

Very practical: You don’t have to use coherent cell ranges. You can also select and copy separate ranges of cells:

Copy and paste links to non-coherent cell ranges.
Copy and paste links to non-coherent cell ranges.

One limitation: Your copied cell ranges must have the same shape.

Convert absolute to relative references: Insert $-signs to all formulas

Thanks for the question in the comments below! The question was how to insert $-signs to all pasted cell links. Because this question would be relevant in many different scenarios (not only when pasting cell links), I’ve written a whole article about it. The short answer: Use a VBA macro to insert $-signs to all cell references.

Please read this article for more information.

Image by Денис Марчук from Pixabay

By Henrik Schiffner

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

7 comments

  1. Thanks for your tip! I appreciate your post. A note on deleting zeroes: I, too, like zeroes to be deleted but some cells were already zero before I copied and pasted the link (zero growth for certain rows) and by deleting zeroes you also may be deleting what you may need to show to your colleagues. So my recommendation is to go line-by-line to delete what you do not need.

    1. Thanks, ExcelPro, for the comment. Yes, fully agree – often selecting zeroes manually line by line is not only the safest way but also the fastest way.

  2. How do I paste a link in Excel without 0?
    Open Excel. Click on File< Options< Advanced. Under Display options for this worksheet group, uncheck the option Show a zero in cells that have zero value and click Ok.

  3. Hi, is there any way I can paste links which are automatically locked ie:

    ='[LINKED FILE]LINKED SHEET’!$A$1

    Not

    ='[LINKED FILE]LINKED SHEET’!A1

    Or a way of inserting $..$.. into a range with different cell references, so cannot do bulk search and replace.

    thanks

Leave a comment

Your email address will not be published. Required fields are marked *