K2 Office Tips Header
Volume 10, Number 12  

December, 2015

We are enjoying some amazing weather here in Brampton, Ontario. No snow yet and warmer than usual.


Nancy and I have just returned from a 66 hour flying trip to London, England where I received the ICB-Global Luca award for my contribution to the bookkeeping profession. The award was presented by Prince Michael of Kent, a cousin of Queen Elizabeth II. It was a tiring three days but what a memorable experience.    


Looking ahead to the new year, I will be launching a new series of e-books on advanced Excel. The first one will be titled "Excel's Best Unknown Features for Accountants and Bookkeepers". That will be be followed by a book on "Array Formulas".  My goal is to publish one per month.


Nancy and I want to wish all our readers Season's Greetings and best wishes for a prosperous New Year.

As usual, I would welcome your comments at




Alan Salmon

Excel Tips

Summary: There are times when you need to copy the contents of a single cell to a very large range. Here are two fast ways to do this:
  1. Select cell A1.
  2. Press Ctrl+C to copy its contents to the Clipboard.
  3. Click once in the Name box, above column A. (Before you click, the Name box contains "A3," which is the cell you just copied.)
  4. Type D1:D99999 and press Enter. The range is selected.
  5. Press Ctrl+V.
A similar approach to selecting large ranges could also be used with the Go To box, in this manner:
  1. Select cell A1.
  2. Press Ctrl+C to copy its contents to the Clipboard.
  3. Press F5 to display the Go To dialog box.
  4. In the Reference box type D1:D99999.
  5. Click OK. The range is selected.
  6. Press Ctrl+V.
In both cases you have copied the contents of a single cell and pasted it into thousands of other cells.
Summary: If you need to move cells to a different location in your worksheet you can use your mouse to accomplish this task. Here is how to do this:  
  1. Select the range of cells you want to move by highlighting the range with the mouse.
  2. Position the mouse pointer over the heavy border that surrounds the selected range. The pointer should turn into an arrow.
  3. Click and drag the range to a new location. As you move the mouse, the outline of the range moves.
  4. When you are satisfied with the new location, release the mouse pointer. The cells are moved.
If there is already content where you are moving the cells, Excel will ask you if you want to overwrite the existing cells. You can respond according to if you want to overwrite these cells.

If this doesn't work, or if no heavy border appears around the  range you have selected, then drag-and-drop editing has been turned off on your system. To turn it on, follow these steps:
  1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 display the File tab of the ribbon and click Options.
  2. Click the Advanced option at the left of the dialog box. In the Editing Options area, clear the Enable Fill Handle and Cell Drag-and-Drop check box.
  3. If you want to be warned before overwriting information during an edit, make sure the Alert Before Overwriting Cells check box is selected.
  4. Click on OK.
Now redo the steps in the first section above. 

Back to top

Summary: If you have a series of values in a range of cells, you can use Conditional Formatting to distinguish the odd numbers from the even numbers. Here is how to do this: 
  1. Select the cells that contain the odd and even values.
  2. Select Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box.
  3. Use the left-most drop-down list and choose Formula Is. 
  4. In the formula area, enter =MOD(A1,2)=1. This formula will return True if the cell contains an odd value.
  5. Click on Format and change the format to reflect the formatting you want applied to the cells containing odd values.Click on the button In the Conditional Formatting dialog box.
  6. Repeat steps 3 through 5 for the second condition, but this time use the formula =MOD(A1,2)=0. This condition returns True if the cell contains an even value.
  7. Click on OK to apply the conditional formats to the cells.
When this conditional formatting applied, if the cell is odd it will be one color and if even it will be another. If the cell contains text, the cell will not be colored; it will have the color of the cell before you added the conditional formatting. The conditional formatting takes precedent over any formatting you applied to a cell, so even if you try to change the cell color via the toolbar, the conditional formatting takes precedent.



Word Tip


Summary: You can easily insert a sound file in your document. Here is how to do this:
  1. Position the insertion point where you want the sound file inserted.
  2. Display the Insert tab of the ribbon.
  3. Click Object in the Text group. Word displays the Object dialog box.
  4. Click on the Create from File tab.
  5. Use the controls on the dialog box to locate a sound file that you want included with your document.
  6. Click on OK. An icon that looks like a speaker is inserted in your document.
Later you listen to your sound file by double-clicking on the speaker icon.
Back to top


K2E Canada Inc. is a leading provider of professional development seminars for the Canadian accounting world. Each month we publish this free Office Tips e-mail newsletter. These tips will save you time and enhance the appearance of your Office files.


To subscribe to our K2E Canada newsletter send an e-mail to

alan@k2e.ca  with "Subscribe to newsletter" in the Subject. 
Your information is always kept secure and never shared.
Alan Salmon
Managing Director
K2E Canada Inc.