K2 Office Tips Header
 Volume 4, Number 10
 October 3, 2010
In This Issue 
Excel Tips
OutlooktopIn This Issue
 Outlook Tip
Follow me on Twitter

Top2Fall is now officially here and I'm on the road for the next 12 weeks doing my seminars across Canada. Thanks to all of you who took the time to comment on our new format. We're making some more minor changes in this issue. You will now find it easier to jump between the tips. Also, we are starting an archive file so you will be to go back to earlier issues. This month you'll be only be able to call up the September issue.

As usual I would welcome your comments at alan@k2e.ca.

Alan Salmon

Excel Tips

Excel1Fixing Non Numeric Numbers 

Summary: Sometimes Excel does not import data correctly from other sources. Here's how to fix this problem.   
There are times when Excel treats imported numbers as text. This can be very frustrating because if you try to sum these numbers you get a zero for an answer even though the numbers look like numbers.
Here's one of several ways to convert these text numbers to values:

  1. Click on any empty cell in the worksheet.
  2. Press Ctrl + C to copy the empty cell to the clipboard.
  3. Select the range that contains the non-numbers.
  4. Select Home | Clipboard | Paste | Paste Special to display the Paste Special dialog box.
  5. In the Paste Special dialog box select the Add operation.
  6. Click OK.
Excel adds nothing to these values, but in the process turns those cells into actual values.
Excel2Deleting Values While Keeping the Formulas
Summary:  Many Excel users have workbooks that track information for a defined period such as a year. Next year they save the file with a new name and delete the values, while keeping the formulas. This can take a lot of time. Here's a shortcut:
A common type of workbook contains input cells and formulas that work with these input cells. At the start of a new period (year) you want to delete the values in the input cells and keep the formulas. The steps are as follows:
  1. Select the range you want to work with.
  2. Choose Home | Editing | Find and Select | Go to Special
  3. In the Go to Special dialog box, select the Constants option.
  4. Select Numbers and uncheck text, logicals and errors.
  5. Click OK and all of the numbers are selected.
  6. Press the delete key to delete these values.
If you need to do this on multiple sheets, select the sheet tabs before you do steps 1 to 6 above.
Back to top
Excel3Restricting the Cursor Movement to Input Cells
Summary: There are times when you want to restrict the movement of your cursor to cells where you input data. Here's the way to do this. 

Most Excel workbooks contain input cells and formula cells. You enter data into the input cells, and the formula cells calculate and display the results. To prevent a user from accidently deleting the formula cells you need to do the following:

Setting up this arrangement is a two step process. First you unlock the input cells and then you protect the other cells in the worksheet. Here are the specific instructions:
  1. Select the range of input cells.
  2. Press Ctrl + 1 to display the Format Cells dialog box.
  3. In the Format Cells dialog box, click on the Protection tab, and deselect the Locked check box and click OK.
  4. Select Review | Changes | Protect Sheet.
  5. In the Protect Sheet dialog box, deselect the Select Locked Cells check box and make sure that the Select Unlocked Cells check box is selected.
  6. (Optional) Specify a password that will be required to unprotect the sheet.
  7. Click OK.
After you have performed these steps, only the unlocked cells can be selected. If you need to make changes to the worksheet you will have to unprotect the sheet first by choosing Review | Changes | Unprotect Sheet. 

Word Tips

Word1Adding a Watermark to a Word 2007/2010 Document
Summary: It is often nice to be able to add a Watermark which appears behind the text in a Word document. An example would be marking a document as a draft.
Watermarks are text or pictures that apprear behind the text. They can add interest or identify the document's status by marking it as draft. 
  1. Open a new blank document, or open an existing document.
  2. Click on the Page Layout Tab in the Ribbon.
  3. In the Page Background group, click Watermark.
  4. Click on a design in the gallery or create a custom watermark.
  5. Select the Picture, if you are creating a Picture watermark.
  6. Enter the Text, if you are creating a Text watermakr.
  7. Use the Print Layout view to see the watermark as it will print.
The watermark displays on the background of each page.
Word2Adding a Drop Cap
Summary: A Drop Cap is when the first letter of the first word in a paragraph is displayed in a large font. 
To add a Drop Cap to a Word 2007/2010 document:
Click in the paragraph that will contain the drop cap.
  1. Click on the Insert tab on the Ribbon.
  2. In the Text group, click Drop Cap.
  3. Select either Dropped or in Margin.
  4. Click on OK and your Drop Cap is created. 
  5. Click OK to finish. 

Outlook Tip

Outlook2Your Life in Outlook

Summary: All of your mail, contacts, calendars and almost every other detail of your life is in Outlook. A computer crash could be a disaster if you lose your Outlook file. Here's how to create a backup of your Outlook data.

To create a copy of your mail, contacts, calendars and other data in Outlook do the following:  
  1. Go to your Outlook folder in Windows Explorer.
  2. Highlight the files ending in .pst
  3. Make sure you select the files outlook.pst and archive.pst.
  4. Select copy to copy these files to the clipboard.
  5. Open the folder where you want to save your backup files.
  6. This should be on another computer or on a portable hard drive or on a jump drive or on the Internet.
Do this on a regular basis and you will have protected your life in Outlook.

K2 Enterprises Canada is a leading provider of professional development seminars for the Canadian accounting world.

Each month we publish this 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
President & CEO
K2 Enterprises Canada