# Convert a formula in Excel into an Array Formula without Ctrl-Shift-Enter?

Question: How might it be possible in Excel to convert a formula into an array formula, without using a hotkey?

## Array Formula

• I really like this question. Microsoft is inconsistent in this special regards. Every function can somehow be executed via menu - except this one – nixda Sep 19 '14 at 5:56
• @nixda the `=max(len)` specifically? – Raystafarian Sep 19 '14 at 11:41
• No, @Raystafarian, he means the conversion to an array formula from a normal formula. – David Michael Gregg Sep 22 '14 at 7:15

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:

``````=LEN(MAX(INDEX(C2:C14,,1)))
``````

Furthermore, as a result of discussion below, this formula will work for all numbered and non numbered/mixed ranges:

``````=MAX(INDEX(LEN(C2:C14),,1))
``````
• How would you adjust that to `=max(len)`? – Raystafarian Sep 19 '14 at 13:28
• @Raystafarian, I've added an example to my post – CallumDA Sep 19 '14 at 14:09
• That is taking the length of the maximum number, whereas the question is taking the longest length of the items in the list. Reversing it to `=max(len(index...` doesn't seem to produce the same result. Not criticizing, +1 – Raystafarian Sep 19 '14 at 15:08
• Curiously, you could write the formula like this, though `=MAX(INDEX(LEN(C2:C14),,1))` this will address non-number lists – CallumDA Sep 19 '14 at 15:38
• You should add that to the answer, that is pretty curious. And it works with non-numbers AND mixed lists! – Raystafarian Sep 19 '14 at 15:41

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
``````
• Good thought in general, but despite the title of my OP, I clarified my question in the post itself as "without using a hotkey?" Substituting one hotkey for another defeats the purpose. Now, if you can show me how to assign that Sub to a button on the toolbar -- that is just the sort of thing I'm looking for! – David Michael Gregg Sep 20 '14 at 13:05
• @MyPetOcean, was adjusting the formula not an option? You didn't explain why you need to be able to enter without using hotkey (I presume by "hotkey" you are talking about Ctrl+Shift+Enter) – CallumDA Sep 21 '14 at 11:37
• I mean I'd like to avoid any hotkey. My initial rationale is long-term simplicity (and memorability) for non-savvy users. But secondly, there just simply ought to be another way to do this. What other major software has important features only accessible via hotkey of all things? – David Michael Gregg Sep 21 '14 at 12:59

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.

• For people unfamiliar with using names ranges, it would be helpful to expand your answer a little to include a simple example of how to do it. – fixer1234 Apr 21 '15 at 20:00