A quick Excel keyboard trick for selecting large ranges

You'll want to add this quick selection trick to your repertoire. It's handy when the range isn't a complete data range or it comprises more than one data range.

You probably know about the [F5] key - pressing [F5] opens the Go To dialog. You enter a range name or a cell reference, click OK, and Excel selects a range or cell, accordingly. It's a quick and easy way to get from one spot to another. What you might not know is that you can use this feature to select a range, where no range exists. Excel uses the current cell as the top-left anchor cell and you enter the bottom-right anchor cell (or vice versa). Excel will select everything in between, including the two anchor cells. The trick is to hold down the [Shift] key.

Let's work through a simple example. You can use any sheet, even a blank one, but we'll use this technique to select a small data range. Specifically, to use [F5] select A5:C21, do the following:

  1. Select A5.
  2. Press [F5].
  3. Enter C21 in the Reference field, but don't click OK yet.
  4. Press and hold the [Shift] key.
  5. While holding down [Shift], click OK.

That certainly was easy! I purposely chose a subset because there's an easier way to select an entire data range: simply press [Ctrl]+[Shift]+8.

This [F5] key trick comes in handy when you want to select a subset of a larger range, or even an area that comprises more than one data range. In addition, in a small range, a quick drag might be quicker, but you won't always be working with a small range that's visible on screen. It's just one more selection technique to have in your bag of tricks.

There are many ways to approach most selection tasks. Feel free to share your favorites!

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks