2

I'm having issues using keyboard shortcuts to select a range for any variable after the first variable in a Google Sheets function. Here is a simple example.

Let's say I'm trying to write a sumif function in Google Sheets. I'd like to use keyboard shortcuts to select my ranges, but I can only do so for the first variable in the function.

Let's say I have an x in column A that indicates the column I want to sum, and column B contains the value I want to sum. In Excel, I can

  • type =sumif(
  • place my cursor in the first cell for the criteria
  • CMD + Shift + Down arrow to select the range for the criteria,
  • ,"=x",
  • place my cursor in the first cell for the value column
  • CMD + Shift + Down arrow to select the range for the value column
  • )

So the final formula might look something like =sumif(A2:A300,"=x",B2:B300) However, in Google Sheets the keyboard shortcuts only work for the first variable.

I also tried clicking in the first cell, holding down just the shift key, and using the down arrow key to highlight one cell at a time, and that didn't work either.

I did a few other tests.

  • I typed =sum(B2:B300) and was able to use the keyboard shortcut, but If I try =sum(b2:b50,b51:b300), I can use the keyboard shortcut for the first range and not the second.
  • I tried sum(b2:b50)+sum(b51:b300) to see if I could use the shortcut in a second function in the same formula, and I couldn't.
  • I ensured the light grey bracket appeared before clicking on the first cell of the second range.
    My conclusion is that the shortcuts only work in the first variable of a function. I know I can use a mouse, or I can manually type the function, but I have been using these keyboard shortcuts in Excel for likely a decade, and I'm just more efficient this way. "Old dog new tricks" I guess. I'm hoping someone knows something I can do to make this work in Sheets.

1 Answer 1

1

Cell selection through the keyboard or mouse works fine for an arbitrary number of formula arguments.

The cell selection mode is context-sensitive. When you are editing a formula, the mode gets activated after you type an argument delimiter (usually comma), or an opening parenthesis (, or an operator such as =, <>, +, -, *, /, &.

You can also activate and deactivate the mode manually by pressing Control+E while editing a formula (this is one of the few shortcuts that requires the use of Control key on macOS.)

When cell selection does not appear to be working, the reason is often the use of an incorrect argument delimiter. The delimiter is comma , in locales that use period as decimal mark, and semicolon ; in locales that use comma as decimal mark.

To set the delimiter, choose the correct locale in File > Settings. The locale setting is per spreadsheet. New spreadsheets inherit their locale from Google Account settings.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.