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