Backward Compatibility of Office 365 Dynamic Arrays (Updated 23 Sep 2020)

Office 365 Excel Dynamic Arrays are great, but:

What happens when you create a workbook with Dynamic Array  Excel (Excel DA) and send it to someone who does not have Excel DA, and how do you write VBA code that works in both Excel DA and Excel Non-DA?

My free CheckDA tool allows you to check that the formulas you create using Excel DA will not cause problems when opened in prior Excel versions.

The introduction of Dynamic Array formulas in Office 365 Excel has made a fundamental change to Excel formula behavior and syntax:

  • In previous versions such as Excel 2013, Excel automatically used implicit intersection to select a single value from a range in many circumstances.
  • In Office 365 Dynamic Array Excel 2016 (Excel DA), Excel treats all formulas as array formulas unless the implicit intersection operator @ is used, and single-cell dynamic array formulas will spill their arrays to surrounding cells.

Forward Compatibility

For workbooks authored in previous versions of Excel compatibility is great: implicit intersections will be silently converted to the new @ syntax, and VBA code generally works fine.

Backward Compatibility

When workbooks authored in Dynamic Array Excel (Excel DA) are opened in previous Excel versions, in most cases the formulas will be silently converted to the old syntax (@ will be removed where possible, and spilling dynamic arrays converted to array (CSE) formulas).

Formulas created by VBA using Range.Formula will work correctly in both DA and non-DA Excel

But it is possible to create formulas in Excel DA which cause difficulties when opened in previous versions.

CheckDA is a free open-source VBA addin that can scan Excel DA formulas, highlight potential backward compatibility problems and optionally convert some problem formulas to more compatible versions.

Backward Compatibility Problem Areas

VBA code creating formulas

For forward compatibility reasons formulas created using Range.Formula are treated as old-style non-DA formulas. To create a DA formula using VBA you should use the new Range.Formula2.

Therefore if you want your VBA code to run in both DA and non-DA Excel use Range.Formula.
For more details see

VBA Object Model Extensions for Dynamic Arrays

In addition to .Formula2 the Excel team has added a few useful extensions to the VBA object model:

  • Range.HasSpill returns True for both the parent DA formula and a spilled cell
  • Range.SpillParent returns the Range object of the parent DA cell
  • Range.SpillingToRange returns a range object covering the entire spill range
  • Range.SavedAsArray returns true if the range will be converted to a CSE array formula in Non-DA Excel

Functions that do not exist in previous versions

Excel DA introduces many new functions that work with Dynamic Arrays (SORT, UNIQUE, LET …). When you open a workbook containing these functions in non-DA Excel:

  • The cells will still show the values calculated in DA Excel, until they are recalculated.
  • In Non-DA Excel the functions will be prefaced by xlfn. and as soon as you recalculate will show #Name.
  • When the workbook is re-opened in DA Excel the xlfn. prefix is removed and the cells can be recalculated.

This means that limited DA<->NonDA round-tripping is possible even when these functions do not exist in NonDA!

Unexpected @ in a formula producing =_xlfn.SINGLE()

Excel will only remove @ from a formula where previous Excel versions would have used Implicit Intersection to return a single value from a Range or Named Range or function parameter.

You will be warned by Excel DA if you try to enter a formula with @ in an unexpected place, but it is still possible to create such a formula. An unexpected @ will create a _xlfn.SINGLE() in previous versions and will result in #Name when calculated.

For example =@A1 will be converted to =_xlfn.SINGLE(A1) because Excel cannot do Implicit Intersection on a single cell.

CheckDA will flag such formulas as type @

Formula containing a Spill Reference producing _xlfn_ANCHORARRAY()

A formula reference to a dynamic array can use the spill reference suffix #. For example =SUM($B$3#) will SUM the entire dynamic array that starts in $B$3. Spill references get converted to _xlfn_ANCHORARRAY(Reference) in previous versions and will result in #Name when calculated.

CheckDA will flag such formulas as type {#}

Unwanted Array Formulas (CSE) in previous versions

Any formula that Excel DA thinks could return an array (even if it is actually only returning a single value) is converted to a CSE array formula in previous versions.

This may or may not produce a backward compatibility problem – you have to inspect these formulas carefully.

CheckDA flags these formulas as types:

  • {1} a single-cell non-spilling formula
  • # An Excel DA Dynamic Array formula that is spilling or blocked (#Spill!)  will be converted to a fixed CSE formula in previous Non-DA Excel versions such as Excel 2013.
  • {n} a formula entered in Excel DA as a multi-cell CSE formula

Using CheckDA

CheckDA is an open-source VBA XLAM addin.
CheckDA only runs in Windows Office 365 Dynamic Array Excel.

To protect against accidental change CheckDA is protected with the password dm.

You can download the CheckDA addin from here. The zip file contains the CheckDA.xlam and the CheckDa.docx Word document.

Install the CheckDA.XLAM file using Excel’s Addin Manager if you want it permanently installed, or just open the xlam workbook using Excel File Open if you only want to use it in the current Excel session. Once successfully installed you will see CheckDA available on the ribbon.


Control-Shift-J or clicking the Check DA Formulas button shows the Check DA form.


The form:

  • Shows formulas in Non-DA Syntax
  • Is Modeless (you can edit formulas while still showing the form)
  • Is Resizeable
  • At start-up scans the active worksheet for unique formulas
  • At start-up filters out formulas with unexpected @ or Spill Ref or Spill or single-cell array formulas

Restate Selected Formulas

Clicking the Restate Selected Formulas button will change any @ or {1} formulas selected in the form to more compatible syntax:

  • @ formulas will have their unexpected @s removed.
  • {1} formulas will be re-entered as non-array formulas.
  • # Spill formulas and {#} Spill Ref formulas cannot be automatically restated.

Undo Restate

Clicking the Undo Restate button will undo the restatement of any formulas restated in this session of CheckDA.

Show Office 365 DA Syntax

This button toggles between showing the formulas in Non-DA syntax and Office 365 Dynamic Array syntax.


The Microsoft Excel team have done an outstanding job of creating, as far as is possible, compatibility between Dynamic Array Excel and previous versions. With a little care you can easily create workbooks using Excel DA that work correctly in Non-DA Excel.

Further Reading

This entry was posted in arrays, Dynamic Arrays, Excel, Uncategorized, VBA and tagged . Bookmark the permalink.

21 Responses to Backward Compatibility of Office 365 Dynamic Arrays (Updated 23 Sep 2020)

  1. dougaj4 says:

    Thanks Charles, this looks like a great tool.

    Just to be picky:
    “(as of May 2016 only available to Office Insiders)”

    … and still only available to Office Insiders 3 years later!

  2. Jason says:

    Wonder if you have had this error or warning.
    I have the insider version with DA on my computer and send files with others who do not.
    I’ve stayed away from using new dynamic arrays on files shared with them, but they get an error on some non-DA formulas. When I go to edit the formula it says: “This style of formula is not supported by some older versions of Excel. Would you like to use this variation instead?” It shows the proposed formula, but it is the exact same as what it is replacing. If you accept it, the formula is the same, however others are able to view the results without the #Name error. It is a formula in a table so the “@” is included. I suppose something is changing, just not visually. The proposed still has the “@” included. I just had to go through and edit and accept each formula.

    • Jason, could you post the formula? The dialog should not be shown if there are no changes, so this sounds like a bug. [Joe from the Excel Team]

      • Jason says:

        I have the screen shot showing the formula and the recommended variation if you would like. I can Tweet it to you or something.


  3. fastexcel says:

    That message is supposed to be shown when you enter a formula that contains extra @ s. Don’t know why you could not see the formula difference (maybe you hit a bug that got resolved?) But that kind of problem is exactly what CheckDA should help with: those formulas should show flagged with an @ and you can select them in the CheckDA form and click “Restate” to resolve the problem

    • Jason says:

      I thought I was missing it too until I took a screen shot of the before and after and also did a FORMULATEXT and checked exact. Thanks for putting CheckDA together. I want to start using dynamic arrays more, but need to have it friendly for everyone.

      • fastexcel says:

        Also there is a suggested improvement for CheckDA: have a button that converts Dynamic Arrays and Spill Refs to static arrays and refs. Do you think that would be useful?

      • Jason says:

        Possibly. I haven’t played around enough with it yet to know what Excel already converts or converts correctly. I have seen that “_xlfnI” before though. I spent some time converting a lot of stuff on one of our models over to DA, but realized it would be difficult to maintain a DA version and a non-DA version with the almost daily changes I have to make to it. I will probably wait until DA goes wide before incorporating. I have enough problems with compatibility, backwards or otherwise. I have fearful recollections of the time I had to track down an error where I had used a single TEXTJOIN formula deep in the data before those were widely available.

  4. Alan says:

    I hate having to many ribbon bars installed for 1 or 2 extra tools if they can be combined with something else. Would you consider adding this as buttons to FastExcel v3 and v4.x? Then one less ribbon bar to mess with?

  5. Saved a long work with this add-in. Thank you for sharing!

  6. wayne says:

    hi, i added the addin but it just doesnt show up on the ribbon.
    checked the excel add-ins and it’s loaded.
    any idea? thanks

  7. fastexcel says:

    OK I recreated the XLAM and code-signed it – could you try again?

    • wayne says:

      Hi, sorry for the delay, knocked off the other day.
      Managed to load the updated addin now but the functions seem to have malfunction.
      For eg, the filters dont work other than “All formula” and i cant restate any formula
      Prompted me “no formulas have been restated”

  8. fastexcel says:

    That will happen if none of the selected formulas have potential compatibility problems

  9. J. Woolley says:

    Thank you for describing the four new Range properties. I found Microsoft documentation for HasSpill, SpillParent, and SavedAsArray by Google Search, but nothing for SpillingToRange. None of them are included in the Microsoft Excel Range object document at

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s