Professor Excel

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.

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.
  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.

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

var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 );

script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

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.

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.

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.

Hold on a second. Was this information helpful so far?
Connect with me on 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.

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

Exit mobile version