How do you highlight a range of cells, then enter a formula into only one without losing the others?

I'm trying to set a matrix formula in a cell, and the answer will be a column vector. To get a column vector as the answer, first I have to highlight the number of cells that will be in the vector, otherwise the answer will only be generated in the cell with the formula.

How do I highlight several cells in a column, then enter a formula into the first one without the others being automatically un-selected?


In Excel on a Mac, to be clear.

1 Answer

  • 3 years ago
    Favorite Answer

    Assuming your Mac Excel version can handle VBA, you can do it using a macro with a keyboard shortcut.

    Then, when you select a range of cells and press Ctrl + your keyboard shortcut, the formula will be returned in the first cell in the selection without deselecting the remaining cells in the selection. You would, of course, have to program your formula into the macro.

    For example assume you want to input a formula into the first cell of a selection that will return the sum of the values of all other cells in the selection, regardless of how many cells are selected.

    Copy this macro to the clipboard (highlight the entire macro, right click inside the highlighted area, and 'Copy'):

    Sub insFormula()

    Dim selCount

    selCount = Selection.Count

    Selection(1).Formula = "=Sum(" & Selection(2).Address & ":" & _

    Selection(selCount).Address & ")"

    End Sub

    Right click any sheet tab at the bottom and select 'View Code'.

    Select 'Insert > Module'.

    Paste the macro into the white editing area to the right (right click inside the area and 'Paste').

    Close the VBE (red button - top right).

    Press ALT + F8 (Function Key F8)

    Select the macro, if not already selected, and click 'Options...' at the bottom.

    Select a letter to be used as a keyboard shortcut, such as 'a' and click 'OK'. Close the Macro window.

    Select any number of contiguous cells containing numeric values, in any column.

    Press Ctrl + your shortcut letter. The first cell in the selected range will display the sum of all other cells in the selection.

    Of course, you would have to create your own formula.

Still have questions? Get your answers by asking now.