K2 Office Tips Header
Volume 10, Number 4  

April, 2015

Spring is here in Brampton and Nancy and I have enjoyed several coffee breaks on our deck overlooking the forest behind our house. 


April is always a busy month for me. It is a time to get all of our teaching manuals and files in place for the spring seminars that we will be doing. We also have to complete the planning for the 2015 Accounting Technology seminar series this fall, as we launch the registration process the first week in May. 


However, the most exciting project for Nancy and me this month has been planning for our upcoming visit to Ireland. We take off on April 30th for ten days. It should be a great experience.

As always, I welcome your comments at alan@k2e.ca




Alan Salmon

Excel Tips

Excel1Selecting Only the Visible Cells in a Worksheet  

Summary: There are times when you want to only copy the visible data in a worksheet. Here is how to do this:  

An example of this is when you need to copy SubTotals that have hidden rows and columns. A normal copy and paste will include the hidden data.

Here are the steps:

  • Select the range of cells
  • From the Home tab, in the Editing group, select Find & Select
  • Click on Go To Special
  • Select Visible cells only
  • Select OK
  • On the Home tab, in the Clipboard group, select Copy
  • Activate the worksheet you want to copy the data to
  • On the Home tab, in the Clipboard group, select Paste
 Just the visible cells that you selected will be pasted into the worksheet. 

Excel2ADisplaying Values as Thousands             

Summary:  Many times, it is often useful for display purposes, to show the numbers in a report, as if each number was divided by thousands. Here is how to do this:

  • Select the desired cell/cells.
  • Right-click on the selected cell/cells.
  • Select Format Cells.
  • Select the Number tab.
  • Under Category select Custom.
  • In the Type Box enter "0,".
  • Select OK.

All of the numbers will now display and print as if they were divided by thousands. The key to this custom format is the comma after the 0. This is a very basic format. If you add $ signs and format to a number of decimals you use this custom format, but again put the "." at the end of the format. 


The advantage to this custom format is that it retains the original number(s) so there will not be errors in calculations that involve cells where you are not dividing by thousands.


A bonus hint. If you want to divide by millions use two commas.



Back to top


Summary: There are times when you just want to add the positive or negative numbers in a range of cells. Here is how to do this: 

The trick is to use the SUMIF function. Let's assume that you have a mixture of positive and negative numbers in cells A1 through A40. You want to know what the sum is of just the positive numbers and just the negative numbers.
Select the cell where you want the sum of the positive numbers to show and enter the following formula:
Press Enter. You will now have the sum of all the positive numbers in the range.


Now select the cell where you want the sum of the negative numbers enter the following formula:




Press Enter. You will now have the sum of all the negative numbers in the range.


Word Tip


Summary: Word's Format Painter is an often overlooked tool. Here's how to use it to expedite the formatting of your Word documents.  


The Format Painter is the icon in the Home Tab that looks like a paint brush. Using it enables you to copy formatting from specific portions of your document and apply it to other portions of your document with a few clicks.

To use the Format Painter, follow these steps: 

  1. Select a portion of your document containing the formatting you would like to apply.
  2. Click the Format Painter button (It is positioned on the Standard Toolbar and looks like a paint brush).
  3. Select the portion of your document where you would like to apply the formatting.

Word will apply the formatting to your document. The Format Painter is then deactivated, so if you select another part of your document, the formatting will not be applied again.

If you do want to apply the formatting to several areas of your document, double click the Format Painter button after selecting the portion of your document containing the formats you would like to apply. Then, individually select the portions of your document where you'd like to apply the text. When you're done, click the Format Painter button once to deactivate it.

A bonus tip. The Format Painter works exactly the same way in Excel.


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.