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

June 30, 2016

Greetings from The Excel Addict
Hi fellow Excel Addict,

Tomorrow (Friday, July 1) is Canada's 149th birthday and also the 100th anniversary of the Battle of the Somme during the First World War where 700 Newfoundlanders died in battle on that one day. This is a very special day for Newfoundland and I will be attending the ceremonies at the local war memorial.

On Saturday, I will be running in my 2nd road race (running) since I recuperated from my 18-month layoff due to a knee injury.

Hoping you will enjoy your weekend as well.

If you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you 'Use Excel to Easily Remove the Background From Portrait Image'. You can read the tip here.

ishing you another great day of Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]

Francis Hayes (The Excel Addict)

If you missed my last newsletter, you can click here to view it online. Quote of the Day

"It’s not what you say out of your mouth that determines your life;
it’s what you whisper to yourself that has the most power."

-- Robert Kiyosaki

If you have a favourite quote, send it to me and I may post it in my newsletter.

Today's Microsoft Excel Tip

Quickly Merge Multiple Ranges of Cells Across Or Down

I'm not recommending using Merged Cells into all your spreadsheets because they can cause a whole bunch of problems with selecting ranges, sorting, filtering, and more. In worksheets where you are frequently manipulating data, I recommend you avoid merged cells.

However, there may be times when you are required to create form-type worksheets. Merged cells, in some cases, can help you better align and present your information.

Creating merged cells can sometimes be frustrating, so here are some tips to help you with merging cells more efficiently.

Merge Cells Across
When you need to merger cells across multiple columns for multiple individual rows, there is the hard way and there is the easy way. Most people do it the hard way, that is, they merger the cells for each row individually or they merge the cells on the first row and then copy the format (using Format Painter) to the other rows.

A much quicker and easier way is to (1) select the entire block of cells you want to merge across for individual rows, (2) from the Home tab, click the small dropdown arrow to the right of Merge & Center and (3) choose Merge Across from the menu.

In one step, the cells (i.e. columns) will be merged into a single cell for each individual row.

See this example...

Merge Cells Across Multiple Rows in Microsoft Excel 2007 2010 2013 2016 365

Merge Cells Down

Say you want to merge cells on several rows and repeat this down the column. Again, there is a hard way and an easy way.

You don't need to know the hard way.

One easy way is to merge the first three cells (e.g. B2:B4), click the Format Painter, then drag down to the cells below where you want to apply the same merged cells pattern.

Copy Merged Cells To Multiple Ranges Using Format Painter in Microsoft Excel 2007 2010 2013 2016 365
Another way, to copy merged cells which is a little easier and faster is to select the merged cells and drag the Fill Handle down (or across) as far as you need.

Copy Merged Cells To Multiple Ranges Using Fill Handle in Microsoft Excel 2007 2010 2013 2016 365
If you need to copy the merged cells pattern to non-contiguous areas of your worksheet, select the merged cells, double-click on the Format Painter and click the top left cell of each area where you want to paste.
Copy Merged Cells To Multiple Non-Contiguous Ranges Using Format Painter in Microsoft Excel 2007 2010 2013 2016 365
Not earth-shattering but anything to make your day a little easier is worth learning.

Thanks for supporting this newsletter and website

Subscription Information: Spreadsheet Tips From An Excel Addict is available only to subscribers of my newsletter. If this newsletter was forwarded to you and you would like to get your own copy, please visit or send a blank email to theexceladdict(AT)

 If you would like to share this newsletter with others...
1) Forward this newsletter by email, but first delete the unsubscribe link at the very bottom so you don't get accidentally unsubscribed
2) Ask your friend/colleague to visit or send a blank email to theexceladdict(AT)
3)  Post a link to in a company newsletter or 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 weekly publication of
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630