MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Slimming Selection Post Merge

May 03, 2017 - by Bill Jelen

Slimming Selection Post Merge

Merged cells are evil. I often am selecting a range of data, but then accidentally touch a merged cell. Bam! My selection rectangle just became three columns wide. I usually curse and start selection again, but there is a keyboard shortcut to narrow the selection back to the original width.

Watch Video

  • Merged Cells are evil.
  • My one rant about a merged cell is that the selected range gets larger when you touch a merged cell
  • I was always frustrated because I couldn't make the selection narrower again.
  • Steve from Huntsville: keep holding down Shift key, click in the cell just below the merged cell.
  • Or - keep holding down the Shift key and press Down, Left, Left
  • This assumes that the merged cell is at the top, not in the middle of your range.
  • If the merged cell is in the middle, you can not narrow the selection - use Center Across Selection instead.

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2089 narrowing the selection after
  • touching a merged cell all right well
  • you if you watch the podcasts you've
  • probably heard me say that merge cells
  • are evil I hate merge cells but I
  • understand why people use them because
  • there's a big thing right up here on the
  • ribbon that encourages people to use
  • merge cells my number one complaint
  • about this yeah I know it makes it
  • difficult to sort and paste but I am
  • always navigating by using ctrl shift up
  • arrow when I press ctrl shift up arrow
  • from here I'm going to touch cell a1 and
  • watch what happens I'm doing in slow
  • motion as soon as I touch a one my
  • selection becomes three cells three
  • columns wide instead of one right board
  • I'm doing this control ship up arrow and
  • now I've selected the whole range and
  • all I wanted to do was just bold the
  • phrase column right and I was in
  • Huntsville Alabama doing a seminar and
  • just saying well you know now there's
  • nothing I can do I press the down arrow
  • and is not getting narrow again it
  • becomes like a permanent thing a guy and
  • the audience's name steve says well wait
  • a second what do you mean it's a
  • permanent thing all you have to do
  • anyhow me do it again so ctrl shift up
  • arrow he says keep holding down shift
  • and click on phrase or click on that
  • cell and sure enough that gets us back
  • in now admittedly the whole resize using
  • ctrl shift up arrows that I didn't have
  • to go touch the mouse I mean I was going
  • to touch the mouse I just hold down
  • shift and click on that although I guess
  • if I had a thousand rows I would be able
  • to see that but there we are and then
  • when I was testing Steve's trick you
  • know so if we start from that cell we
  • can shift click any cell and change the
  • range which I might be cool I'm not sure
  • that I knew that or at least I've never
  • used this trick before but it also made
  • me think well wait can I solve this with
  • the arrow so ctrl shift up arrow and I'm
  • do shift down arrow like I usually do
  • this is the point where I get frustrated
  • with Excel well all I would have to do
  • is while I keep holding down shift is
  • press left left and I'm back to the
  • selection that I was looking for all
  • right now Merchant Center I know that
  • that is popular because it's right there
  • but we can get the exact same effect if
  • we would go into the dialogue launcher
  • here and our horizontal choose center
  • across the
  • like that click okay all right we get
  • the exact same view but we don't have
  • that annoying thing of ctrl shift up
  • arrow selecting across if I needed just
  • to select up to phrase control shift up
  • arrow shift down arrow and everything is
  • good alright tips like these are the
  • tips that are in my book power excel
  • with MrExcel click that I on the top
  • right hand corner and the wrap-up merge
  • cells are evil and my one particular
  • rant is the selected range gets large
  • when you touch it merge cell and I can
  • never make the selection narrower again
  • but there's two easy ways Steve
  • Huntsville keep holding down the shift
  • key click in the cell just below the
  • merge cell or keep holding down the
  • shift key and press down arrow left left
  • to go back it's not horrible hey I want
  • to thank you for stopping by will see
  • you next time for another net cast from
  • MrExcel hey just an addenda now this
  • all assumes that the merge cell is at
  • the top the merge cells in the bill
  • there is nothing here it comes to you to
  • get that thing back down to a single
  • cell I mean you can shift click here but
  • there's if you need to select the whole
  • thing never going to work center cross
  • selection is the only way to go there

Download File

Download the sample file here: Podcast2089.xlsm

Title Photo: skeeze / 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.