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