Select a Current Region of Data in Excel

by Gregory on June 19, 2010

There are several ways to quickly select an entire region of data in Excel. The active cell must be inside the region for all of them to work.

I like to use the keyboard shortcut Ctrl+* (asterisk) to select the current region around the active cell. If you have a number keypad, then Crtl+* will work, otherwise on a regular keyboard pressing the keys Ctrl+Shift+* will do the trick.

The same thing can be accomplished by pressing the F5 key, which brings up the Go To dialogue box, then select the Special button, which brings up the Go To Special dialog box where you select Current Region, and then click OK. You can also bring up the Go To dialog box by using the keyboard shortcut Ctrl+G.

Another way to do the same thing is by using the menus. In Excel 2003 select Go To from the Edit Menu, click Special, select Current region, and click OK. In 2007 from the Home menu, click Find & Select, select Go To Special, click Current region, and hit OK.

The F4 key will repeat the last action, so after using one of the previous methods to select a current region of data, using F4 to select other regions or ranges of data is an even faster method to use. It shortens the shortest shortcut. 🙂

In the picture below there are five different regions of data. Cell A1 is a one-cell region. The range A3:E7 is a region, along with ranges G3:H7, A9:E10, and G9:H10.


If the active cell is adjacent to more than one region, all regions will be selected, including the row or columns of blank cells containing the active cell. For instance, if the active cell is A2, then the keyboard shortcut Ctrl+* will select the region A2:E7.  And if the active cell is G11, the region selected will be G9:H11, which includes two blank cells.

The video below demonstrates what I’m blabbering about.

In writing this post I came across some other shortcuts that work in selecting the current region of data. Why they work is beyond me. I can’t find any documentation that supports their use for this particular purpose.

The first is the keyboard shortcut Ctrl+A , which will select the entire sheet if the active cell is not inside a range or adjacent to at least one range. However, if the active cell is inside the range or between one or more ranges Ctr+A will select the current region.

The second is the keyboard shortcut Alt+Enter, which is normally used to enter a line break inside a cell. However, it works just like Ctrl+* and I’m at a loss as to why. The mysteries of Excel.

Related Posts Plugin for WordPress, Blogger...
Pavel June 29, 2010 at 7:37 am

Alt-Enter appears to repeat the last action, just like F4.

Gregory June 29, 2010 at 9:13 am

I believe your right. Good catch!

Florian Arendt March 10, 2012 at 12:27 pm

Shift+Ctrl+Space also selects the current region. It’s a combination of Shift-Space and Ctrl-Space, which select the whole row and the whole column, respectively. You can actually do them after each other to the same effect. Repeating Shift+Ctrl+Space selects the whole sheet.

I think they are mostly included to achieve some “compatibilty” with older versions and other products. Cmd+A, for instance, is very commonly used for “select all” in other programs.

Gregory March 10, 2012 at 12:29 pm

Thanks for the tip.

Comments on this entry are closed.

Previous post:

Next post: