Here are two very useful keyboard shortcuts that you can use when working with large data ranges and creating formulas in Excel:
When working with large data ranges, two common needs are 1) to highlight the data range used in your formula, and 2) to get back to your formula after you’ve highlighted that range.
Here I have a large range of data and I want to use the SUMIF function in cell I2 to find the total sales for a specific salesman indicated in cell H2. The formula will be =SUMIF(B2:B110,H2,E2:E110).
Once I’ve started the formula with “=SUMIF(” I then want to highlight the range from B2:B110. To highlight that range with just keyboard shortcuts, I will use my cursor movement keys to get to cell B2, then use this keyboard shortcut: Ctrl + Shift + Down Arrow.
This will highlight the range from B2 down to the end of the data in that column.
Now that I’ve highlighted the criteria range, I need to get back to my formula to finish it. I can use the wheel on my mouse or grab the scroll bar to get back up, but an easier way is to use this shortcut: Ctrl + Backspace.
This brings me back to the active cell where I am building my formula and allows me to complete my task. I can now highlight the criteria, then, to highlight the sum range, use the same two keyboard shortcuts to make it simple and easy to finish the job.
So, the two keyboard shortcuts are:
Ctrl + Shift + Down Arrow
Ctrl + Backspace