K2 Office Tips Header
 Volume 4, Number 9
 September 1, 2010
In This Issue
Excel Tips
Word Tips
Outlook Tips
Quick Links
Follow me on Twitter

Top2It's time to put a great summer behind us and move into fall. Who could have asked for better summer weather than we had in Ontario this year. I got three working weeks at our cottage, which was a real treat.

As promised we are adding a Word and Outlook tips this month. Moving forward we will have a new format and additional Word, Outlook and PowerPoint tips in the coming months.

I hope you like our new tips newsletter. I would welcome your comments at [email protected].


For the 17th year I am doing the Accounting Technology seminar series in 13 cities across Canada. This free one day seminar provides accountants, bookkeepers and consultants with an overview of new technology for their practice and for their clients. Visit the registration page at http://attour.salmon.ca/index.htm for information on the day and to register.

Alan Salmon
Smart Tags 

Summary: Smarts, the small square in the bottom right hand corner of a cell can be useful but many Excel users find them irritating. Here's how to turn them off.   
Smart Tags are useful when you are formatting cells. For example, if you copy a range and then paste it to a different location, a Smart Tag appears at the lower right of the pasted range, Click on the Smart Tag and you will see a list of formatting options which enable you to specify how the data should be pasted.

Some Excel users find these Smart Tags to be helpful, while others think they are annoying. Here's how to turn off Smart Tags.

To remove all smart tags in a workbook, follow these steps for Excel 2003:
  1. Start Excel, and then open the workbook from which you want to remove the smart tags.
  2. On the Tools menu, click AutoCorrect Options.

    Note In Excel 2007, click the Microsoft Office Button, click Proofing, and then click AutoCorrect Options.
  3. On the Smart Tags tab, click to clear the Label data with smart tags check box.
  4. If it is necessary, click to clear the Embed smart tags in this workbook check box, and then click OK.
  5. Close and then reopen the workbook for your changes to take effect.
Back to top 
Entering a Fraction into a Cell 

Summary:  A reader asked me last month why, when she entered a fraction (1/4) into an Excel cell she got a date? Here's the solution.

If you type a fraction into a cell that has not been preformatted for fractions, you will get a date value instead of what you typed. For example, type 1/2 into cell A1,press Enter and Excel translates this into January 2. To return a fraction value, type 0 into cell A1 and press Enter.
Back to top
I Get a Blank Page When I Print a Worksheet
Summary: Many times when I print a worksheet I get a blank page at the end. This tip will show you how to get rid of that annoying extra page. 

When information is deleted from cells, Excel does not automatically delete the reference to the cells. To find out the last cell used in the spreadsheet and reset it to the correct area, try the following: From the Edit menu, choose Go To. Click the Special button. Select Last Cell and click OK. If the last cell is not accurate (beyond where it should be), select and delete the empty cells before the actual end of your data and the last cell, save and close the workbook, and reopen it. The last cell will reset to the correct location and the worksheet will print correctly.
One of My Workbooks has Ballooned to 50 MB 
Summary: A reader wrote to me and told me he had a workbook he had been using for over three years and the size of the file has ballooned to over 50 MB. HOwever, he has not added many rows and columns He wanted to know what he could do to reduce its size. Here's the tip.
For each worksheet in the file, you need to manually go to the bottom of the last used row and select the entire row. Hold down Ctrl+Shift and push the Down Arrow. This should select all unused rows. Then choose Edit>Clear>All. Repeat the same steps for columns (manually select the column after the last used one. Hold down CTRL+Shift and press the Right Arrow. Then choose Edit>Clear>All.) 

Once you complete these steps on all the worksheets, save the workbook and you should notice a significant difference in the file size.
Word 2007 Tip - Clearing Formatting from Text
Summary: When you're cutting and pasting text into your Word document, it is easy to introduce formatting inconsistencies. But, it isn't always so easy to rid your document of these formatting inconsistencies. Here how to get rid of the underlying formatting.
Here's how to get rid of the underlying inconsistent formatting in a document. 

You can try to apply formatting so that the text matches surrounding portions of your document. You might even use the Format Painter to help with this. But, sometimes, the best solution is to clear formatting from your text. You can then format it from scratch the way you want.

To clear formatting from text in Word 2007, select the text that contains the formatting you would like to remove. Then, locate the Font section of the Home ribbon. Click the Clear Formatting icon. The Clear Formatting button shows a white eraser, along with some letters.

When you click the Clear Formatting button, text and paragraph formatting is removed from the text. You can then apply new formatting. Or, you can leave it so that it matches your document template's default formatting.  

Sorting a List

Summary: Sorting a list in Word is easy. Here's how to do it.   
When you're creating a list in Microsoft Word, you don't need to worry about putting it in alphabetical order. You can put it in alphabetical order after you complete the list--without any time-consuming cutting and pasting. All you need to do is use Word's Sort feature.

Here's the steps to using Word's Sort feature:
  1. Start by highlighting the data you would like to sort.
  2. Click the Sort button in the Paragraph group of the Home ribbon.
  3. Use the dropdown box to select the type of data you selected. You can choose from text, date, or number.
  4. Choose where you would like to put the data in ascending or descending order.
Click OK.

Word automatically sorts your data.
Outlook Tip - Managing the Size of your PST File
Summary: As you work in Outlook your mail PST file will get bigger and bigger. Here's how to keep the size of the PST file manageable. 
As your use of Outlook grows, so, typically, does the time it takes Outlook to do what you want it to. This is because your PST is growing as well. Your PST of Personal Folders file is where Outlook keeps all your data, including calendars, contacts and emails.
There is a limit to the size of your PST file so it pays to keep the size of your main PST file small and manageable. You can have Outlook do some of that using AutoArchive. Or you divide your messages between more PST files, which can be painless and swift.
Archive Old Mail in Outlook and Keep the PST File Small
To create an archive of old messages in Outlook separate from the PST file you use every day:
  • Select File | Data File Management from the menu in Outlook.
  • Click Add....
  • Choose the desired format. Unless you may need to access the data with Outlook 2002 or earlier directly, it is safe to highlight Office Outlook Personal Folders File (.pst).
  • Click OK.
  • Enter the desired file name.

    Many users like yearly archives, and name the PST file after the year. Of course, you can choose monthly archives if you do have lots of big mail to deal with, or another scheme. Just make sure the resulting PST files' sizes are somewhere around 1-2 GB. Larger files tend to be less efficient.
  • Click OK.
  • Type the archive PST file's desired name under Name: Again, it makes sense to name your archive after its contents (a year's worth of mail in my case).
  • Optionally, protect access with a password.
  • Click OK.
  • Now click Close.
Move Mail to the Archive
To populate your newly created archive PST: 
  • Drag and drop entire folders to the root folder newly appearing under Mail Folders.
    This is handy if you already have an "Archive" folder that contains, say, all of last year's mail. Just drop it unto the archive PST.
  • Alternatively, to archive individual items:
        Click on the root folder named after your archive PST under Mail Folders with the right mouse button.
         Select New Folder... from the menu.
         Type the desired folder name.
         Make sure Mail and Post Items is selected underFolder contains: if you want to archive emails. For archiving other items, choose the appropriate category.
         Click OK.
         Drag and drop individual or groups of emails onto the newly created folder.
  • To move all mail preceding a certain date in a folder (or nested folders):
         Select File | Archive... from the menu in Outlook.
         Make sure Archive this folder and all subfolders: is selected.
         Highlight the desired folder.
         Specify the date emails prior to which (say, this year's January 1) you want to move under Archive items older than: __.
         Use the Browse... button to select the archive PST file created above.
         Click OK.
Close the Archive PST File
After you have archived all items, you can close the PST file in Outlook:
  • Click on the root folder of your archive PST under Mail Folders with the right mouse button.
  • Select Close "___" from the menu.
Access Mail from a Closed Archive PST File
To retrieve messages from an archive PST file you have closed: 
  • Select File | Open | Outlook Data File... from the menu in Outlook.
  • Highlight the desired archive PST file.
  • Click Open.
The PST file and its folders will appear under Mail Folders, ready for action.

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 Excel Workbooks.

To subscribe to our K2E Canada 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
President & CEO
K2 Enterprises Canada