Hyperlinks - how much do they change the size of a file

Copper Contributor

Hi, I am new to the community but have been working with Excel for a long time.  The one thing that I am not sure of is if I am using hyperlinks just how much they add to the size of the file.  I have an already very large file and we just started hyperlinking in it this year so that is of concern as we already have issues due to the size and number of formula that we have.  Would appreciate any help.  Thank you

16 Replies

Hi Julie,

 

Don't worry about it!

I've done a simple test and you may be surprised when you know its result!

 

I created a workbook and copied the link of this community ten thousand times (10,000) in the range A1:A10000 of the Sheet1, then I made a copy of this worksheet in a separate workbook and deleted the links from it!

Then I compared the difference in size between the two workbooks, which is less than 1 KB!

 

Before: 62.1 KB

After: 61.5 KB

 

Please find them in the attachments!

Thank you.  I did the test too, but I think there were skeptics working with me and wanted a second opinion.

If so, I would recommend you to send my previous reply to them via email!

 

Reply.png

 

Good luck

Hi Haytham,

 

That's if you have exactly the same hyperlink repeated 10 thousand times. Is it hyperlink or not - adds practically nothing.

 

If all hyperlinks are different I guess it'll be added about few hundreds bytes on the top for each hyperlink.

 

And another story if use HYPERLINK() function, it shall add less.

 

In any case that's not a lot.

Hi Sergei,

 

Thanks for this good point!

 

But each hyperlink has an independent entity in an independent cell even if it is the same!

The process of directing you to the link address in the browser is only a functionality and does not affect the size.

 

In fact, when you remove the hyperlinks you actually remove the URL formats which is blue font color and underlined text!

 

Conclusion
The difference between the plain text and the hyperlink
is just the format!

The specific format of the hyperlink is associated with an event to directing you the to link address in the browser, and this is just a functionality and does not affect the size.

 

With regards to HYPERLINK function, it increases the size by ~30 KB as the attached file, and this is not a big difference!

Haytham,

 

For each unique URL into XML of Excel file will be added XML description of such with reference on ID for it.

 

If you have only one URL, doesn't matter how many time you use it, it'll be only one description. If 10 thousand different URL:s - ten thousand such descriptions and ID:s.

 

Anyway, that's not something what increases the size significantly.

Hi Sergei,

 

In XML each cell has an independent tag since it has an independent entity!

 

I've converted the workbook to an XML file, and get access to its source code, and the below code is a part of it.

 

Please take a deep look at this code and you'll see how each cell has an independent tag that includes all its contents!

 

You will notice how href="https://techcommunity.microsoft.com/" will repeat in each tag.

 

 

<table:table table:name="Sheet1" table:style-name="ta1">
    <office:forms form:automatic-focus="false" form:apply-design-mode="false"/>
    <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
    <table:table-column table:style-name="co2" table:number-columns-repeated="1023" table:default-cell-style-name="Default"/>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p><text:a xlink:href="https://techcommunity.microsoft.com/" xlink:type="simple">https://techcommunity.microsoft.com/</text:a></text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="1023"/>

 

 

I mean not conversion to XML but internal Excel file structure if you unzip it. Here each resource (URL text) is defined only once and cell value is referencing on it. Text of the URL is not repeated for each cell definition.

 

Sergei,

 

Please note that the Excel file in XLSX extension is an XML-based format.

XML is a markup language and it's responsible for the Excel file structure.

 

The code that you saw before is really what is going on inside the Excel file!

Even with other extensions, you'll see the same thing but in a different syntax!

Haytham,

 

In which exactly XML file is the above code? The biggest one is sheet1.xml and it looks differently.

Sergei,
 
Your last reply is unclear or vague!

 

Again, the code above is part of the structure of the workbook "before" that I previously attached!

It's what's going on behind the scenes!

 

You said before: "If you have only one URL, doesn't matter how many time you use it, it'll be only one description. If 10 thousand different URL:s - ten thousand such descriptions and ID:s."

 

The code proved that this is wrong!

 

I will not discuss more because it has become useless.

Hi Haytham,

 

It looks strange for me. If unzip your Before file its structure as in attaches before.txt file. Biggest file here which describes the sheet is sheet1.xml. If open it, all what defines the content with hyperlinks is at very bottom of the file

<hyperlinks><hyperlink ref="A1" r:id="rId1" xr:uid="{B7B34BD7-8C51-483C-B6EE-3A89EFD0E69A}"/>
<hyperlink ref="A2:A10000" r:id="rId2" display="https://techcommunity.microsoft.com/" xr:uid="{8583AAC1-002D-49A8-B3C5-A3CEE3F36A01}"/></hyperlinks>

Value of each row is the reference on resource defined in tiny resource file (between <v> and </v>) and looks like 

<row r="10000" spans="1:1" x14ac:dyDescent="0.35"><c r="A10000" s="1" t="s"><v>0</v></c></row>

File After is exactly the same except small block with hyperlinks at the bottom of the file.

 

To compare, I generated another file with 3 different hyperlinks repeat in 10 cells each. Entire block which describes them is

<hyperlinks><hyperlink ref="A1" r:id="rId1" xr:uid="{65A09A39-A823-4B29-831D-35135F8B94C8}"/>
<hyperlink ref="A2:A10" r:id="rId2" display="http://techcommunity.microsoft.com" xr:uid="{445541D1-C4DC-4651-A162-47AE45477BCF}"/>
<hyperlink ref="A11" r:id="rId3" xr:uid="{ED775096-F6A1-4801-9196-FE06349F96F8}"/>
<hyperlink ref="A12:A20" r:id="rId4" display="www.microsoft.com" xr:uid="{E07D5B65-8D06-4F4F-B908-19ADD0C32BEA}"/>
<hyperlink ref="A21" r:id="rId5" xr:uid="{D978AABC-E1CD-4864-811E-4A8DC1F21E56}"/><hyperlink ref="A22:A30" r:id="rId6" display="https://vivaldi.com/" xr:uid="{A75F23EE-CA26-477D-839C-A9B8857EE103}"/></hyperlinks>

This block is 3 times bigger since we have 3 different hyperlinks instead of only one.

 

Oops, txt files are not allowed to attach. Zipped.

Hi Sergei,

 

Now I know where the file sheet1.xml came from!

 

I'm not an XML language expert and how it is used in Excel files, but after reading this article, I admit that the code I mentioned before doesn't represent the actual structure of the Excel file.

What described in this article is actually the structure of the Excel file and what happened behind the scenes!

 

But however, the subject is not as you think!

What I did in the Before file was holding the fill handle in the first cell and then dragging it until I reached the cell A10000.
This is why the definition is uniform across the range.


But this definition may differ depending on how you create the links.
If you enter the links in the cells (cell after cell) without using the fill handle, the definition will appear as follows:

 NOTE: You will find this code spreads horizontally in the single line. (For clarification, I've moved each hyperlink in a separate line)NOTE: You will find this code spreads horizontally in the single line. (For clarification, I've moved each hyperlink in a separate line)

 

You will notice that the link is the same in the workbook, but after you unzip it you will find it defined 15 times, and it's not visible here, it appears to be encrypted on every line, and has a unique ID!
This is because I entered the links manually without using the fill handle!

 

But the interesting fact is that each link in the range A1:A15 is an instance of the parent link which is located in a separate file called sharedStrings.xml and the link is counted 15 times in this file.

 

sharedStrings.png

 

What do you think of this?

 

Some people may think that this subject has taken up more discussion than it deserves!
In fact, it is fun to dive into the Excel to this extent and learn all these stuff!

 

Thank you
Regards

Hi Julie,

Hyperlinks aren't any different in size to the texts that created them and although it will increase the file size by 1 or 2 times compared to its visible text. So, I wouldn't worry about the file size increased if it is increases only using =Hyperlink( , ) function.

However, some folks are usually mistaken between pasted hyperlinks and hyperlinks using Hyperlink function. If user has numerous hyperlinks using pasted links then that for sure increases the size of workbook significantly and plz allow me to demonstrate here:

Open an empty Excel file and enter a text e.g. Myfolder there and then right click and click hyperlink and then select a folder as the address of the hyperlink C:\Users\Julie\Downloads\ and then save the workbook somewhere
then open a new workbook and type this somewhere =Hyperlink("C:\Users\Julie\Downloads\","Myfolder") and save in the same folder as the first workbook.

If you check the size of the above two workbooks, you will see that the second workbook is smaller in size than the first one. This was just one hyperlink, if you have many hyperlinks the difference of size will be significant.

Whenever linking cells either with a folder or file, instead of copy and Paste (control + v ) use paste-values only and create link using hyperlink() function and this way it will open quickly and will not increase file size.

Hi Jamil,

 

In some cases, the size will vary slightly and this difference will not have a significant impact even if the number of links increases.

This is in case if the link is external!

Please see the attached files.

 

It may also be different if these links are duplicate and external as in the case I mentioned earlier, and how the links in the HYPERLINK function have increased the size of the workbook by about 30 KB.

This may be thanks to the sharedStrings.xml which is has the responsibility of reducing the size of the hyperlinks strings.

 

Not always the hyperlinks in HYPERLINK function are lower in size!

Hi Julie

I would suggest that if hyperlinking adds benefit to your operations then it may outweight any negatives from whatever file size increases you experience.

Best wishes.

Cheers
Damien