It's time for you to finally learn how to use Pivot Tables in Excel!

The Excel Addict - Help with Excel 2013, 2010,
2007, 2003

November 17, 2016

Greetings from The Excel Addict

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
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]

Francis Hayes (

If you missed my last newsletter, you can click here to view it online. 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.

Today's Microsoft Excel Tip

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.

How To Locate All Merged Cells In Report in Microsoft Excel 2007 2010 2013 2016 365

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;

Find Merge Cells Format in Microsoft Excel 2007 2010 2013 2016 365
6) Click OK to close the Find Format dialog;

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;

Select All Merged Cells In A Worksheet in Microsoft Excel 2007 2010 2013 2016 365
At this point you can either:

(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;

Unmerge All Cells In Worksheet in Microsoft Excel 2007 2010 2013 2016 365

(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'.

Find Not Working Clear Format in Microsoft Excel 2007 2010 2013 2016 365

** 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.

It's time for you to finally learn how to use Pivot Tables in Excel!

Thanks for supporting this newsletter and website

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.
"Spreadsheets Tips From An Excel Addict" is a twice weekly publication of
'Excel in Seconds' on Tuesday & 'Excel in Minutes' on Wednesday

You can subscribe to my FREE Excel Tips Newsletter at
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