November 17, 2016
Hi fellow Excel Addict,
The saying 'You don't know what you don't know' is so true, especially with Excel.
On Tuesday, I embarrassingly published a tip, 'Access Your Recent List of Workbooks From the Windows Taskbar' (see here), for something that I just discovered had been right under my nose for years.
What I wasn't expecting was the response that it generated. I received a flood emails from hundreds of Excel Addicts who also weren't aware of this tip. Many of them told me that this is one of their favourite tips and it will save them lots of time and frustration in the future.
| "THIS
WAS AN AMAZING TIP!!! Actually, I enjoy all your tips but
this one
really struck me for its simplicity and usefulness in my day-to-day
job. Can't wait to share it with my colleagues. " |
So maybe you don't know that you don't know today's tip. In today's 'Excel in Minutes' tip I'm going to show you 'How To Locate All Merged Cells On A Worksheet'. If it helps you, I'd love to hear from you.
I hope you're having a great week.
Keep on Excelling,
Francis Hayes (The Excel Addict)
Email: fhayes[AT]TheExcelAddict.com
If you missed my last newsletter, you can click here to view it online.
| TheExcelAddict.com Quote of the Day "We must accept finite disappointment, but never lose infinite hope." -- Martin Luther King, Jr. -- |
If
you have a favourite quote, send it to me and I may post it in my
newsletter.
How To Locate All Merged Cells On A Worksheet
Merging cells in a worksheet can sometimes be helpful but, more often than not, they can cause you plenty of grief when it comes to selecting ranges, using Paste commands, sorting, filtering and many other everyday functions.
Hopefully you have developed a habit of NOT using merged cells in your worksheets. But chances are, you will sometimes find yourself working with worksheets that someone else has created.
Merged cells are not always easy to locate in a worksheet. Depending on how the worksheet is formatted, merged cells are often indistinguishable from other cells.
If you want to 'exorcise' all merged cells from a worksheet, finding them all could be quite a challenge. So here's a simple technique I use for locating all merged cells in a worksheet.
CAUTION: You should always make a backup copy of any workbook before making major changes.
1) Press CTRL+F to open the Find and Replace dialog;
2) Make sure that the 'Find what' field is empty and the 'Match case' and 'Match entire cell contents' options are not checked;
3) Click the Format... button;
4) Select the Alignment tab;
5) Clear the 'Wrap text' and 'Shrink to fit' options** and check the 'Merge cells' option;
7) At the bottom of the Find and Replace dialog, click the Find All button. All merged cells will be shown at the bottom of the dialog;
8) With the dialog still open, press CTRL+A and all of the cells listed at the bottom of the dialog will be highlighted. Also notice that all of those cells are selected in the worksheet as well;
(a) Unmerge all of the cells in one step by clicking the small arrow on the right of the Merge & Center command (on the Home tab), then clicking Unmerge Cells;
(b) Close the Find and Replace dialog and apply a background/fill color to the selected cells to make them easier to identify for working with later, or...
(c) Keep the Find and Replace dialog open, drag it's header to move it out of the way and editing the cells in the worksheet. To select another group of merged cells in the worksheet, click a cell reference from the list at the bottom of the Find and Replace dialog. If you have unmerged some cells, you may want to click the Find All button again to update the list.
CAUTION: If you insert or delete rows or columns in the worksheet, some of your merged cells may be moved to different locations. The cell references in the Find and Replace dialog won't automatically update, so you will need to do the Find All / CTRL+A steps again.
Anytime you use 'Find and Replace' for finding formats, I recommend that you clear the formatting from the 'Find what' criteria by clicking the small arrow on the right side of the Format... button, then click Clear Find Format, OK. This could help avoid problems the next time you need to use the Find and Replace dialog. You can tell that a Find Format has been set if you see 'Preview*' to the left of the Format button. When the format has been cleared, it will say 'No Format Set'.
** If you find that this doesn't find some merged cells in your worksheet, it may be that the merged cells are also formatted as 'Wrap text' and/or 'Shrink to fit'. If that's the case, you will need to check one or both of these options, in addition to the 'Merge cells' option in the Find Format dialog.
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. |
'Excel in Seconds' on Tuesday & 'Excel in Minutes' on Wednesday
| You
can subscribe to my FREE
Excel Tips Newsletter at TheExcelAddict.com and get more tips like this every week |
Copyright Francis Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630