Software optimize

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 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.

22 comments
hulyalkar
hulyalkar

Instead of using GoTo commad you may use NameBox 1.Select A5. 2.Enter C21 in the NameBox, but don’t click OK yet. 3.Press and hold the [Shift] key. 4.While holding down [Shift], click OK. You will get same result

wilkisa
wilkisa

An even easier way than using the F5 GoTo box is to use the Name Box that is already on the screen. Put the cursor in the cell where the range begins (or just click in the name box and type in the range to go to it). Then, type in the last cell of the range, hold the Shift key, and hit Enter. Excel will select the entire range without having to open another box. I like Wlodeek's idea even better! That's something I had not thought of.

AtlantaTerry
AtlantaTerry

Many years ago I had to create an involved series of Excel spreadsheets that included my creating custom macros. Since I had no idea of the ranges that would be in use over time, I used the Ctrl + Shift + arrow method in my macros. I would suppose some of the other keyboard shortcuts might also work well in macros whereas others might not. Test.

Wlodeek
Wlodeek

maybe just type A5:C21 in Name BOX.

spiras
spiras

...you can use Ctrl + * to select the current block instead of Ctrl + Shift + 8. By the way, if you select a block this way, the current cell cursor will move to the top left corner of the selection. But if you use Ctrl + Shift + Spacebar, the current cell will remain where it is.

lovesreborn
lovesreborn

to select a range: select the first (top left) cell, then scroll to where ever you want, then hold Shift and click the last cell (bottom right). done, the range is selected instead of using the [F5] and what not... to select the whole table, select any cell, then hold CTRL + "a".

MommaTRex
MommaTRex

Thanks Lena, for the whole column and whole row shortcuts! I also love CTRL + Page Down and CTRL + Page Up. Even though I know mentally that CTRL + SHIFT + 8 would work just as well if not better, I can't stop myself from using CTRL + SHIFT + END to select to the bottom of the range.

uwishtoo
uwishtoo

It's faster for me to just do it the old way and not drive myself nuts trying to remember all of these shortcuts lol

jbenton
jbenton

[b]visible cells only[/b] ALT + ; [b]collapse selection to current cell only[/b] CTRL + [i]backspace[/i] [b]cycle round corners of selection[/b] CTRL + .

Lena Weber
Lena Weber

Good thread! My most used Excel shortcuts for selection are: [b]Whole column[/b] CTRL + SPACE [b]Whole row[/b] SHIFT + SPACE [b]Table[/b] SHIFT + CTRL + SPACE bar [b]All cells with comments[/b] CTRL + SHIFT + O [b]Next sheet[/b] CTRL + Page Down [b]Previous sheet[/b] CTRL + Page Up [b]Go to[/b] F5 Can???t wait to learn more tricks??? Cheers :) Lena

aikimark
aikimark

click on A5 Scroll over/down to the diagonal corner of the range you want to select if the cell isn't currently visible. Press Shift when you click on C21

guillegr123
guillegr123

Just press: Ctrl + Shift + arrow (up, down, left or right, depending where the data ends).

Mark W. Kaelin
Mark W. Kaelin

What other selection keyboard tricks do you use in Excel?

Marshwiggle
Marshwiggle

... the [F5] isn't really needed. In fact, after a little experimentation I discovered that merely holding [Shift] and clicking any diagonal corner (top right/bottom left, bottom right/top left, etc.) will do the trick. I think that may be something I once knew, but forgot from lack of use.

yourwork
yourwork

I have been doing it the quick short cut way (above) for the last 25 years, this is nothing new in MS Excel and is a whole lot quicker than the F5 method.

jbenton
jbenton

CTRL + SHIFT + END gives a slightly different result CTRL + SHIFT + 8 selects the current region - ie all used cells around current one, up to the nearest continuous border of blanks CTRL + SHIFT + END selects from current cell to the last row/column containing non-blank or formatted cells (excepting whole row/colum formats) CTRL + SHIFT + SPACE is very similar to CTRL + SHIFT + 8, except that it leaves the current cell as the "live" one, whereas ~8 enables the top left cell and repeating CTRL + SHIFT + SPACE will select the whole sheet

ssharkins
ssharkins

I write them on post its and keep them around until they're ingrained... it works for me -- well, more often than it doesn't anyway!

yourwork
yourwork

Whole column CTRL + SPACE ___ or click the column header A,B,C,D ad inf. Whole row SHIFT + SPACE ___ or click the row header 1,2,3,4 ad inf.

yourwork
yourwork

Thanks for these I shall put them in my repertoire

jbenton
jbenton

perhaps it's the lack of positive contribution to the discussion?