Confusing behaviour of Excel table style

Yevhen Rezohlazov 1

Hi,

I don't understand Excel's behaviour when viewing a document with a table.
The table has a style: white background and orange text and borders.
Cell A2 takes background color from the table style, but cell A3 overrides the table style color
In sheet1.xml the style for A2 is 3, and for A3 it is 5.
Those two styles are absolutely identical, except fillId:

<cellXfs count="6">  
        <xf numFmtId="0" fontId="0" fillId="2" borderId="0" xfId="0"/>  
        <xf numFmtId="0" fontId="0" fillId="3" borderId="0" xfId="0" applyFill="1"/>  
        <xf numFmtId="0" fontId="0" fillId="3" borderId="0" xfId="0" applyFill="1" applyAlignment="1">  
            <alignment vertical="center"/>  
        </xf>  
        <xf numFmtId="0" fontId="0" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1">  
            <alignment vertical="center"/>  
        </xf>  
        <xf numFmtId="0" fontId="4" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1" applyBorder="1"/>  
        <xf numFmtId="0" fontId="0" fillId="4" borderId="0" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1">  
            <alignment vertical="center"/>  
        </xf>  
    </cellXfs>

They have fillIds 2 and 4 respectively. But the fill styles 2 and 4 are identical as well!

<fills count="5">  
    <fill>  
        <patternFill patternType="none"/>  
    </fill>  
    <fill>  
        <patternFill patternType="gray125"/>  
    </fill>  
    <fill>  
        <patternFill patternType="solid">  
            <fgColor theme="9"/>  
            <bgColor indexed="64"/>  
        </patternFill>  
    </fill>  
    <fill>  
        <patternFill patternType="solid">  
            <fgColor theme="0"/>  
            <bgColor indexed="64"/>  
        </patternFill>  
    </fill>  
    <fill>  
        <patternFill patternType="solid">  
            <fgColor theme="9"/>  
            <bgColor indexed="64"/>  
        </patternFill>  
    </fill>  
</fills>

I can flip the background colors if I flip the fillIds of the cells, so it's not tied to cell position or style index.
Somehow Excel knows that it should not override the table style with fill #2, but it should override it with fill #4.
What am I missing here? How does it know which style to use?

Thank you,
Yevhen

91501-styles.xml
91502-table1.xml
91511-theme1.xml
91512-sheet1.xml

Jeff McCashland 476 Reputation points Microsoft Employee

2021-04-27T18:27:43.95+00:00

;Hello Yevhen,

Thank you for your question. One of our engineers will respond soon to assist.

Jeff McCashland
Microsoft Open Specifications
Hung-Chun Yu 976 Reputation points Microsoft Employee

2021-04-28T00:35:45.167+00:00

Hi Yevhen

II will be helping you looking into the issue.

Hung-Chun Yu
Microsoft Open Specifications
Hung-Chun Yu 976 Reputation points Microsoft Employee

2021-05-06T05:51:46.897+00:00

Hi Yevhen

I tried to recreate the excel file using the XML files you provided without much success. Could you send me the Excel file to dochelp at microsoft dot com, with body contains Hung-Chun Yu. Thank you very much.

Hung-Chun Yu
Microsoft Open Specifications
Yevhen Rezohlazov 1 Reputation point

2021-05-06T22:27:31.1+00:00

Hi Hung-Chun,

Done.

Thank you,
Yevhen

4 answers

Hung-Chun Yu 976 Reputation points Microsoft Employee

2021-05-19T07:43:05.15+00:00
Hi Yevhen

What you described is expected behavior. You need to exam sheet1.xml instead when you changed the style for particular cell. Where you should able to find the following <sheetData> section

<sheetData> <row r="1" spans="1:7" s="1" customFormat="1" ht="30" x14ac:dyDescent="0.4"> <c r="A1" s="4" t="s"> <v>0</v> </c> <c r="E1" s="6" t="s"> <v>3</v> </c> <c r="G1" s="1" t="s"> <v>4</v> </c> </row> <row r="2" spans="1:7" ht="21" customHeight="1" x14ac:dyDescent="0.3"> <c r="A2" s="3" t="s"> <v>1</v> </c> <c r="E2" s="7" t="s"> <v>1</v> </c> <c r="G2" s="2" t="s"> <v>5</v> </c> </row> <row r="3" spans="1:7" ht="21" customHeight="1" x14ac:dyDescent="0.3"> <c r="A3" s="5" t="s"> <v>2</v> </c> <c r="E3" s="7" t="s"> <v>2</v> </c> <c r="G3" s="5" t="s"> <v>6</v> </c> </row> </sheetData> Lets use <c r="A1" s="4" t="s"> as an example

Where <c stands for Cell
r stands references to cell “A1”
s (Style index) of “4”
t (Cell Data Type) of “s” which would be type string.

If you change the style of cell “A1” to something else save it. You will see that Style Index now will be updated for "A1". You might have noticed that no change take place with styles.xml.

For detail references on all the Cell Parts, please refer to ISO/IEC 29500-1:2016 Section 18.3.1.4 c (Cell)

Hopefully, I answered your question.
Please sign in to rate this answer.
Yevhen Rezohlazov 1 Reputation point

2021-05-19T18:40:30.55+00:00

Hi Hung-Chun

I do understand how the styles are referenced. What I do not understand is why I get different visual appearance of the cells that reference completely identical styles. Please have a look at the A2 and A3 cells in my document. They have style indices 3 and 5 respectively. The referenced styles are identical except for fillId, but the referenced fill styles are identical as well. Yet you can see that the cells have different background colours.

I also do understand that the A2 cell gets its background colour from the table style. But why doesn't A3 get the same colour?

Also FYI: if I right-click the A2 cell and select "Format Cells", the "Fill" tab shows the brown colour specified in the style instead of the actual displayed cell colour.

Thank you,
Yevhen
Sign in to comment

Hung-Chun Yu 976 Microsoft Employee

We believed we found the issue, under sheet1.xml

    <sheetData>
        <row r="1" spans="1:1" s="1" customFormat="1" ht="30" x14ac:dyDescent="0.4">
            <c r="A1" s="4" t="s">
                <v>0</v>
            </c>
        </row>
        <row r="2" spans="1:1" ht="21" customHeight="1" x14ac:dyDescent="0.3">
            <c r="A2" s="3" t="s">
                <v>1</v>
            </c>
        </row>
        <row r="3" spans="1:1" ht="21" customHeight="1" x14ac:dyDescent="0.3">
            <c r="A3" s="5" t="s">
                <v>2</v>
            </c>
        </row>
    </sheetData>

where <c r="A3" s="5" t="s"> its using 6th Style, they are 0 base. Yet, we only 5 of them defined in <cellStyleXfs count="5">

    <cellStyleXfs count="5">
        <xf numFmtId="0" fontId="0" fillId="2" borderId="0"/>
        <xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyNumberFormat="0" applyFill="0" applyProtection="0">
            <alignment vertical="center"/>
        </xf>
        <xf numFmtId="0" fontId="1" fillId="0" borderId="0" applyNumberFormat="0" applyFill="0" applyProtection="0">
            <alignment vertical="center"/>
        </xf>
        <xf numFmtId="0" fontId="3" fillId="0" borderId="0" applyNumberFormat="0" applyFill="0" applyProtection="0">
            <alignment vertical="center"/>
        </xf>
        <xf numFmtId="0" fontId="4" fillId="2" borderId="0" applyNumberFormat="0" applyProtection="0"/>
    </cellStyleXfs>

Since s="5" doesn't even exist so Excel probably just uses the default style which is "Normal" and has that brown bg.

Hung-Chun Yu 976 Reputation points Microsoft Employee

2021-05-19T19:45:55.873+00:00
<sheetData>
<row r="1" spans="1:1" s="1" customFormat="1" ht="30" x14ac:dyDescent="0.4">
<c r="A1" s="4" t="s">
<v>0</v>
</c>
</row>
<row r="2" spans="1:1" ht="21" customHeight="1" x14ac:dyDescent="0.3">
<c r="A2" s="3" t="s">
<v>1</v>
</c>
</row>
<row r="3" spans="1:1" ht="21" customHeight="1" x14ac:dyDescent="0.3">
<c r="A3" s="5" t="s">
<v>2</v>
</c>
</row>
</sheetData>

<c r="A2" s="3" t="s"> Cell A2 is using style ID "3" <c r="A3" s="5" t="s"> Cell A3 is using style ID "5"

StyleID "3" is defined as

 <sz val="55"/> <color theme="4"/> <name val="Franklin Gothic Medium"/> <family val="2"/> <scheme val="major"/> 

StyleID "5" is defined as

 <sz val="24"/> <color theme="0"/> <name val="Franklin Gothic Medium"/> <family val="2"/> <scheme val="minor"/> 

One way to see the difference is use different Font name for each style, doing so, will help you see the differences much easier.

They are defined in styles.xml

<fonts count="5" x14ac:knownFonts="1"> <sz val="11"/> <color theme="0"/> <name val="Franklin Gothic Medium"/> <family val="2"/> <scheme val="minor"/> <sz val="55"/> <color theme="6"/> <name val="Franklin Gothic Medium"/> <family val="2"/> <scheme val="minor"/> <sz val="55"/> <color theme="4"/> <name val="Franklin Gothic Medium"/> <family val="2"/> <scheme val="major"/> <sz val="55"/> <color theme="5"/> <name val="Franklin Gothic Medium"/> <family val="2"/> <scheme val="minor"/> <sz val="24"/> <color theme="0"/> <name val="Franklin Gothic Medium"/> <family val="2"/> <scheme val="minor"/> </fonts>

Hence you see the difference in visual effect. Does this helped?
Please sign in to rate this answer.
Yevhen Rezohlazov 1 Reputation point

2021-05-19T22:37:17.997+00:00

According to ISO/IEC 29500-1:2016 Section 18.3.1.4 c (Cell)

Office specifies that @s indexes into the cellXfs collection in the style part

My document has the following cellXfs collection:

As you can see, styles 3 and 5 are identical, except for fillId (fill 2 for style 3 and fill 4 for style 5).
According to section 18.18.30 of ISO/IEC 29500-1, fillId is an index into fills collection.
My document has the following fills collection:

Thus, the fills are identical as well. But the background colour is different.

It has nothing to do with fonts, as both cell styles are referencing the same font style (fontId="0")

Hung-Chun Yu 976 Reputation points Microsoft Employee

2021-05-20T16:08:33.473+00:00

I stand correct. I was playing with different font and font color and not actual using fill.

I see the issue now, where just like you stated. They have fillIds 2 and 4 respectively. But the fill styles 2 and 4 are identical as well!. If I changed the fillds4 to red, it does show up. However, as soon as I revert back to what you have and resave the file, now fillIds 2 and 4 appeared to be identical again.

<patternFill patternType="solid"> <fgColor rgb="FFFF0000"/> <bgColor indexed="64"/> </patternFill>

When I insert another Table into the same sheet, I am not able to repro the issue.

Couple questions for you -

How was this file created? Did you create the file via code?

If file was created via Excel, which version of Excel, steps you used to insert the table and apply the fill.

Hung-Chun Yu 976 Reputation points Microsoft Employee

2021-05-20T16:48:45.157+00:00

I am able to repro the behavior by applying the table style TableStyleLight1, and looking at table2.xml <tableStyleInfo name="TableStyleLight1" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0"/>, I can see that showRowStripes="1". However, that is not the case for Table 1. You might have uncover an interesting design flaw with Excel. If you can provide a repro steps on how to reproduce the issue, I can file a product bug on your behave.

Yevhen Rezohlazov 1 Reputation point

2021-05-21T00:08:15.023+00:00

Unfortunately, this is a document from our testing set that consists of documents submitted by customers or scraped from the web, so I do not know how it was created and in which Excel version. There are more documents with this issue. If it helps, I can send them to you as well.
Sign in to comment
Hung-Chun Yu 976 Reputation points Microsoft Employee

2021-05-24T23:47:15.733+00:00
We believed we found the reason.

18.8.15 dxfs (Formats)
This element contains the master differential formatting records (dxf's) which define formatting for all non-cell
formatting in this workbook. Whereas xf records fully specify a particular aspect of formatting (e.g., cell borders)
by referencing those formatting definitions elsewhere in the Styles part, dxf records specify incremental (or
differential) aspects of formatting directly inline within the dxf element. The dxf formatting is to be applied on
top of or in addition to any formatting already present on the object using the dxf record.

So the dxf records apply formatting on top of formatting done by cell (xfs) formatting

Here is the repro steps

Apply normal cell format to selected cells (should have brown background now).

Convert those cells into a Table

Apply Custom Table Style

Exam the Style.xml for the new Table..

Hoped this help.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

Confusing behaviour of Excel table style

4 answers