excelwindows-10keyboard-shortcutsarray-formulasdvorak

Excel array formula changes keyboard layout

To enter an array formula in Excel, one uses Ctrl+Shift+Enter and to switch keyboard layout, one uses Ctrl+Shift

Thus my keyboard layout changes every time I enter an array formula.

Is there a more elegant solution than switching back manually each time?

One solution would be to change the switch layout shortcut, but I can't find it in Windows 10 (I could in Win7). Where is this setting?

Solution

  • One alternative would be to create a macro to convert any formula to an array formula, and assigning your own shortcut (ctrl+m for example).

    Sub convert_to_array()

Dim formarr As Range
Dim oldrange As Range

Set oldrange = Selection

    For Each formarr In oldrange
        formarr.FormulaArray = formarr.Formula
    Next formarr

End Sub

    Then just write normal formulas, select all the ones you want, and hit ctrl+m (or whatever shortcut you assign).