MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Shortcuts - Current Region


July 28, 2017 - by Bill Jelen

Excel Shortcuts - Current Region

The next article in our list of Excel shortcuts involves the Current Region. Learn how to select all of the data set around the current cell.

Ctrl + *
Ctrl + *

This one is easier if you have a number keypad so you don’t have to press shift to get to an asterisk. If I could slow down enough to stop pressing Ctrl + Shift + Down Arrow followed by Ctrl + Shift + Right Arrow, I would realize that Ctrl + * is much shorter and does not get tripped up by blank cells. It is really superior in every way to keyboard tip #2. But my muscle memory still prefers tip #2.

Thanks to @Excelforo.

Watch Video

Video Transcript

Learn Excel From MrExcel, Podcast Episode 2119: Control* To Select The Current Region.

Alright. So, if we have a huge data set, we want to select that data set, just press CONTROL* and it will select all the way to the edge of the data set in all directions, alright? So, it's when it has a completely, completely blank edge.

Let me show you what I mean by that. Okay, so, right here, if we start here, it's going to go to column I to row 9, column N, and then when it hits the edge of the worksheet, so row 1, like this, CONTROL*, alright, and it…can you have some blanks in there? Yes, it's okay to have some blanks but you can't have a completely blank row, like that, and I realized that row isn't blank but, at least from the point of view of this current region, that's not blank, right here, that cell, if that cells not filled in so I took that cell, cleared it out, and do CONTROL*, you'll have that.

Alright, now, you know, just kind of as a bonus tip here, one thing that I hate that managers ask for sometimes are these tiny little columns between the columns, alright? This is a disaster waiting to happen, those tiny little columns. I worked for this guy once and he didn't like if we would just underscores like -- HEAD 1 -- if we would go into CONTROL+1 and say that we wanted to underline that with a single accounting underline or single underline, it doesn't underline the whole column, alright, and he wanted the whole column underlined but he didn't want one single block of data. So, if we would come here and use the bottom border, then it's just a single line all the way across. He wanted these tiny little gaps and so we had to add the extra gaps.

Well, the reason I hate this is CONTROL* doesn't work anymore, alright? So, what we would do is when he would set up his data like this and send us the spreadsheet, we would secretly sneak in here and put a space on each of those. That way, when we used CONTROL*, it would work, alright, but, hey, let's get even better here.

Let's eliminate this problem. Go back to just any 4 cells here. We'll take this one -- HEAD 1, 2, 3, 4 -- alright, and instead of this underline -- which was the underline he didn't want, the regular underline -- we'll do CONTROL 1, and then go to the UNDERLINE drop down and choose SINGLE ACCOUNTING. Now, hey, I know what this does for numbers and I know what DOUBLE ACCOUNTING does for numbers, but, for text, this is awesome because what it does is it underlines the whole cell all the way, not just the characters but it underlines all the way, but it leaves tiny little gaps between those. So, you can eliminate that whole problem there.

Alright, so, CONTROL*, select current region, plus a little bit extra there on the single accounting underline.

Thanks for stopping by. We'll see you next time for another netcast from MrExcel.

Title Photo: stevepb / Pixabay


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.