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?**

**My free CheckDA tool allows you to check that the workbook 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 (as of July 2019 rolling out to the Office 365 Monthly Channel) 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 good: implicit intersections will be silently converted to the new @ syntax.

**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).

**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

**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 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.

## Conclusion

**Please let me know of any bugs you find, and send me your improvement ideas and experiences withCheckDA.**

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!

Thanks Doug – fixed.

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]

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

=IFNA(INDEX(T_PlumbingFixturesExpanded[Description],MATCH(ROW()-@ROW(PO_PlumbingFixtures_A[#Headers]),T_PlumbingFixturesExpanded[AutoPopOrder],0)),””)

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

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.

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?

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.

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?

Not sure excatly what you are looking for … Do you want a general purpose button for amalgamating ribbon tabs?

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

Fixed a bug when using CheckDA with a large number of non-contiguous areas: version 110 uploaded

Great! thank you again Charles!