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 of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.