Volume 10, Number 4

April, 2015

In This Issue

Excel Tips

Selecting Only the Visible Cells in a Worksheet

Displaying Values as Thousands

Adding Just the Positive or Negative Numbers in a Column

Quick Link

Technology Tips Video

Linking Excel Data into a Word Document

In This Issue

Word Tip

Using the Format Painter

Quick Links

Previous Issues of Office Tips

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.

Sincerely,

Alan Salmon

Excel Tips

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

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

Adding Just the Positive or Negative Numbers in a Column

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:

=SUMIF(A1:A40,">0")

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:

=SUMIF(A1:A40,"<0")

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

Word Tip

Using the Format Painter

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:

Select a portion of your document containing the formatting you would like to apply.
Click the Format Painter button (It is positioned on the Standard Toolbar and looks like a paint brush).
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.

647-722-4741