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

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 21 19 control asterisk to select
  • the current region alright so if we have
  • a huge data set we want to select that
  • data set just press control asterisk and
  • it will select all the way to the edge
  • of the data set in all directions
  • alright so it's 1 it has a completely
  • completely blank edge let me show you
  • what I mean by that ok so right here if
  • we start here it's going to go to 2
  • column I to row nine column N and then
  • when it hits the edge of the worksheet
  • so Row 1 like this control asterisk
  • 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
  • though row isn't blank but at least from
  • the point of view of this current region
  • that's not playing right here that cell
  • if that cells not filled in so I took
  • that cell cleared it out and do control
  • a asterisk you'll have that right 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 on I work for this
  • guy once and he didn't like if we would
  • just use under under underscores like
  • head with head one if we would go into
  • control one and say we wanted to
  • underline that with a single accounting
  • underlying or single underline it
  • doesn't underline the whole column
  • alright and he wanted the whole column
  • underline 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 see one
  • of these tiny little gaps and so we had
  • to add the extra cash well the reason I
  • hate this is ctrl asterisk 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 use control
  • asterisk it would work all right but hey
  • let's just let's get even better here
  • let's let's eliminate this problem I'd
  • go back to just any four cells here
  • we'll take this one head one two three
  • four all right and instead of this
  • underline which was the underline he
  • didn't want the regular underline we'll
  • do control one and then go to the
  • underline drop-down
  • choose single accounting 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
  • underlies all the way but it links tiny
  • little gaps between those you can
  • eliminate that whole problem there
  • all right so ctrl asterisk 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 net cast from mr.
  • Excel

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.