Slimming Selection Post Merge

Learn Excel from MrExcel Podcast, Episode 2089: Narrowing the Selection After Touching a Merged Cell.

Alright, well, 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.  And when I press Ctrl+Shift+Up Arrow from here I'm going to touch cell A1 and watch what happens.  I'm doing it in slow motion.  As soon as I touch A1, my selection becomes 3 cells, 3 columns wide instead of 1, alright.  Board under and it’s Ctrl+Shift+Up Arrow, and now I've selected the whole range and all I wanted to do was just Bold the Phrase column, alright.  And I was in Huntsville, Alabama doing a seminar and just saying, well, you know, now there's nothing I can do.  I pressed the Down Arrow and it’s not getting narrow again.  It becomes like a permanent thing. 

And a guy in the audience, his name is Steve, says, “Well, wait a second.  What do you mean it's a permanent thing?  All you have to do, let me do it again.”  So Ctrl+Shift+Up Arrow.”   He’s keyboarding 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 Arrow is that I didn't have to go touch the mouse.  I mean, if 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 wouldn’t 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, yeah, it 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 have to 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. 

Alright now, Merge & 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 Dialog Launcher here and on Horizontal, I’ll choose Center Across Selection, like that, click OK.  Alright, 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, Ctrl+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 that the selected range gets large when you touched a merge cell.  And I can never make the selection narrower again, but there's two easy ways.  Steve from 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.

Well, hey, I want to thank you for stopping by.  We’ll see you next time for another netcast from MrExcel.

Hey, just an addenda.  Now this all assumes that the merge cell is at the top of the merge cells in the middle.  There is nothing here, you’re unable 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 Across Selection is the only way to go there.