Question: How might it be possible in Excel to convert a formula into an array formula, without using a hotkey?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
Excel has a number of native formulas that allow you to create/ utilise arrays, for example
SUMPRODUCT() INDEX() OFFSET() SUMIFS() COUNTIFS() AVERAGEIFS()
Knowing these native formulas well will allow you to avoid the CSE array formulas in most cases (if that is your aim)
For example, try this for numbered ranges:
Furthermore, as a result of discussion below, this formula will work for all numbered and non numbered/mixed ranges:
One work-around for this limitation is to create a small VBA Sub, and assign it to a keyboard shortcut of your choice
This will convert the selected range into an array formula.
Sub ConvertToArrayFormula() Dim rng As Range Dim arr As Range Set rng = Selection For Each arr In rng.Areas arr.FormulaArray = arr.Cells(1, 1).Formula Next End Sub
One answer is to develop your solutions using Names for both Ranges and Formulas.
Thus, the look of your formula may be improved by defining 'v' to refer to =C2:C14. The formula =MAX(LEN(v)) still needs to be array entered but, if you define 'n' to refer to =LEN(v), the array calculation is carried out as the default behaviour of a named formula. Therefore =MAX(n) works without using CSE entry.