June 30, 2016
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.
Wishing you another great day of Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
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 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.
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.
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.
Not earth-shattering but anything to make your day a little easier is worth learning.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630