K2 Office Tips Header
Volume 8, Number 8  

August, 2013

We are now half way through summer and the weather has sure been weird. From steamy hot, to cool and from dry to a deluge. Oh well, that's one of the things we have absolutely no control over, so I guess we just have to grin and bear it.


In June I launched the first of a series of Technology Webinars. The first one, Alan's Excel Tips got great reviews. They will be offered once a month and for 90 minutes I will share with you my favorite Excel Tips, new programs of interest to accountants and other useful technology information.


These webinars are highly affordable at $29.95, including HST, and qualify for 1.5 CPDs. There is no webinar in August. The next one will be in September where I will introduce you to PivotTables, a powerful analysis and reporting tool in Excel. For more information and to register please CLICK HERE. 


As usual I would welcome your comments at [email protected].




Alan Salmon

Excel Tips

Excel2AEncrypting an Excel Workbook 

Summary: If your Excel Workbook contains sensitive data that you don't want other people to access, you can encrypt it. This provide an additional layer of protection over and beyond including a password to open the file. Here is how to do this:   


When you apply encryption you password protect the file. But in addition, the file is scrambled, so with the password the data is just gibberish. To apply encryption:
  1. Click on the File Tab. (The Office Button in Excel 2007)
  2. Click on Protect Workbook. (Click on Prepare in Office 2007)
  3. Click on Encrypt Document.
  4. Type a password that is secure (at least 7 characters or numbers).
  5. Click OK.
  6. Re-enter the password when  prompted to do so.
  7. Click OK.

When you attempt to open the file enter the password and click OK.


Excel2ASelecting the Current Region                

Summary: Excel defines areas of a worksheet as regions. It is often useful to understand the boundaries of a region. Here is how to do this:


Many of the the tasks that you do in Excel are done to a specific region of the worksheet. A good example of this is when you sort. When you start a sort, Excel first selects a region to sort. A region is the area around the active cell that is bounded by blank rows and columns.


If you want to see your current region, select a cell and then press Shift+Ctrl+*. The current region will then be highlighted. 



Excel3Automatically Numbering the Rows in a Worksheet
Summary: There are times when you want to insert a row number into a column of your worksheet. When you do this you will want the column to display the correct number of the row, even when you add or delete rows. Here is how to do this: 

The easiest solution is to use the ROW function, like this:     



This formula returns the row number of the cell in which the formula appears. If you want to offset the row number if you have some headers in rows 1 and 2 and you want cell A3 to return a row value of 1, then you can modify the formula like this:





Word Tip


Word1Displaying a Single Page             

Summary: Many times Word displays two pages of a document side by side, but you only want to display one page the full width of the screen. Here is how to do this:    

As you adjust the zoom settings used by Word, Word automatically displays multiple pages of a document at the same time. This is particularly true if you are using a widescreen monitor at a high resolution. As the zoom factor approaches 50%, Word automatically switches to display two pages.


There are different ways to get you back to a single page. A quick fix to display just a single page is to simply "zoom out" a bit by holding down the Ctrl key as you move the scroll wheel on your mouse. As you zoom in and out Word discovers that it can no longer display two pages on the screen, so it switches automatically to show only a single page.


Another approach is to choose Zoom from the View menu in order to display the Zoom dialog box. Select the Page Width button and click OK. Word calculates the proper zoom percentage to show a single page at the width available on your screen. If you prefer, you can click one of the other single-page options-Whole Page or Text Width. 


Back to top 

 PowerPoint Tips


Summary: Here are three tips that you should follow when you are creating your presentation. 

Write a script.  

A little planning goes a long way. Most presentations are written in PowerPoint without any sort of rhyme or reason. You should know what you intend to say and then figure out how to visualize it. Unless you are an expert at improvising, make sure you write out or at least outline your presentation before trying to put together slides.

And make sure your script follows good storytelling conventions: give it a beginning, middle, and end; have a clear arc that builds towards some sort of climax; make your audience appreciate each slide but be anxious to find out what's next; and when possible, always leave 'em wanting more.

One thing at a time, please. 
At any given moment, what should be on the screen is the thing you're talking about. Your audience will almost instantly read every slide as soon as it's displayed; if you have the next four points you plan to make up there, they'll be three steps ahead of you, waiting for you to catch up rather than listening with interest to the point you're making.

Plan your presentation so just one new point is displayed at any given moment. Bullet points can be revealed one at a time as you reach them. Charts can be put on the next slide to be referenced when you get to the data the chart displays. Your job as the presenter is to control the flow of information so that you and your audience stay in sync.   

No paragraphs. Where most presentations fail is that their authors, convinced they are producing some kind of stand-alone document, put everything they want to say onto their slides, in great big chunky blocks of text.

Congratulations. You've just killed a roomful of people. Cause of death: terminal boredom poisoning.

Your slides are the illustrations for your presentation, not the presentation itself. They should underline and reinforce what you're saying as you give your presentation - save the paragraphs of text for your script. PowerPoint has functions to display notes onto the presenter's screen that do not get sent to the projector, or you can use notecards, a separate word processor document, or your memory. Just don't put it on the screen - and for goodness' sake, if you do for some reason put it on the screen, don't stand with your back to your audience and read it from the screen!  

 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 Inc. newsletter send an e-mail to

[email protected]  with "Subscribe to newsletter" in the Subject. 
Your information is always kept secure and never shared
Alan Salmon
Managing Director
K2E Canada Inc.