title: Array Formulas in Conditional Formatting of Excel XML Spreadsheet files?
body: <p>Excel usually treats Conditional Formatting formulas as if they are array formulas, <strong>except</strong> when loading them from an Excel 2002/2003 XML Spreadsheet file.</p>
  
  <p>This is only an issue with the Excel 2002/2003 XML Spreadsheet format... the native Excel  format works fine, as does the newer Excel 2007 XML format (xlsx).</p>
  
  <p>After loading the spreadsheet, it is possible to make it work correctly by selecting the formatted range, going to the Conditional Formatting dialog, and clicking OK--but this only fixes the problem for the session.</p>
  
  <p><strong>Test case:</strong></p>
  
  <p>Enter the following into a new sheet:</p>
  
  <pre><code>  A B C
  1 N N N
  2 x x x
  3 x x x</code></pre>
  
  <p>Create this conditional format formula on cells A1:C1 (your choice of pretty colors for the format):</p>
  
  <pre><code>=(SUM(($A1:$C1="N")*($A$2:$C$2=A$3))>0)</code></pre>
  
  <p>This is an array formula that activates for A1, B1, and C1 whenever any of them has an "N" and the cell in row 2 below the "N" is equal to the cell in row 3 of the current column. </p>
  
  <p>(This has been simplified from a real-world business spreadsheet. Sorry for the complexity of the test case, I am trying to find an easier test case to present here.)</p>
  
  <p>And it works... you can alter the N's or the x's in any way you want and the formatting works just fine.</p>
  
  <p>Save this as an XML Spreadsheet. Close Excel, and re-open the file. Formatting is now broken. Now, you can only activate conditional formatting if A1 is an "N" and A2 is the same as A3, B3, or C3. The values of B1, B2, C1, and C2 have no effect on the formatting.</p>
  
  <p>Now, select A1:C1 and look at the conditional formatting formula. Exactly the same as before. Hit OK. Conditional formatting starts working again, and will work during the entire session the file is open.</p>
  
  <p><strong>Workarounds considered:</strong></p>
  
  <ol>
  <li><p>Providing the file in native (BIFF) Excel format. Not an option, these spreadsheets are generated on the fly by a web server and this is only one of dozens of types of workbooks generated dynamically by our system.</p></li>
  <li><p>Providing the file in the Excel 2007 native XML format (xlsx). Not an option, current user base does not have Office 2007 or the compatibility plug-in.</p></li>
  <li><p>Asking users to select the range, enter the Conditional Formatting dialog, and hitting ok. Not an option in this case, unsophisticated users.</p></li>
  <li><p>Asking users to open the XML spreadsheet, save as native XLS, close, and re-open the XLS file. <strong>This does not work!</strong> Formatting remains broken in the native XLS format if it was loaded broken from an XML file. If (3) above is performed before saving, the XLS file will work properly.</p></li>
  </ol>
  
  <p><strong>I ended up rewriting the conditional formatting to not use array formulas. So I guess this is "answered" to some degree, but it's still an undocumented, if obscure, bug in Excel 2002/2003's handling of XML files.</strong></p>