Excel Shortcuts - Current Region

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.