What is the meaning of t="shared" in a formula element?

Question

When inspecting a cell element in XLSX file, I find the following formula element:

<f t="shared" si="0"/>

What is the meaning of such a formula element?

Gluttonize

Answer 1

ECMA-376 Part 1 Section 18.3.1.40 says:

The possible values for the t attribute are defined by the simple type ST_CellFormulaType, and are as follows:

...
shared (Shared formula)
...

Shared formula. If a cell contains the same formula as another cell, the "shared" value can be used for the t attribute and the si attribute can be used to refer to the cell containing the formula. Two formulas are considered to be the same when their respective representations in R1C1-reference notation, are the same.

Basically it is a space saving optimisation. One that is guaranteed to be a pain for anyone parsing/modifying the file.

Elenore

"If a cell contains the same formula as another cell, the "shared" value can be used" is not completely true, only certain kinds of formulas work. – Licha

Answer 2

Let's say you have a xml (simplified):

<row r="3" spans="1:2">
    <c r="A3" s="0">
        <f si="0" t="shared" ref="A3:A5">SUM(A1,A2)</f>
    </c>
    <c r="B3" s="1">
        <f si="1" t="shared" ref="B3:B5">AVERAGE(B1,B2)</f>
    </c>
</row>

A3 has formula element, t="shared", si="0", ref="A3:A5", it mean A4, A5 will use the same R1C1 formula as A3. If you want to paste to A4, make A4 has shared formula with si="0":

<c r="A4" s="0"> 
     <f si="0" t="shared"/>
</c>

and with A5:

<c r="A5" s="0"> 
     <f si="0" t="shared"/>
</c>

B3 has formula t="shared", si="1", ref="B3:B5", with the shared-index=si="1" (I guess), make it diffirent to A3, and if you want to paste formula to B4, use:

<c r="B4" s="0"> 
     <f si="1" t="shared"/>
</c>

B4 will have formula =AVERAGE(B2+B3).

Note that some formula cannot be shared, ex VLOOKUP(), HLOOKUP().

Timeout