K2 Office Tips Header
 Volume 5, Number 11
 November 1, 2010
In This Issue 
Excel Tips



OutlooktopIn This Issue
 Outlook Tip

Follow me on Twitter
 

Top2The end of the year is rapidly approaching. Many of you who have accounting or bookkeeping designations may be short of the required CPD credits. If so, you should check out our Excel Study courses. We now have 4 two hour Excel Tips courses that cover over 50 useful Excel tips in each course. To get information about these courses go to

http://k2ecanada.coursehost.com.

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


Sincerely,

Alan Salmon

Excel Tips

Excel1Showing the Folder Location in a Cell in an Excel Workbook
Summary: There are times when it is useful to be able to view the folder in which a workbook has been saved. Here's how to do this in all versions of Excel. 

The following formula in a cell will display the folder in which the workbook is saved.

 

=LEFT(CELL("Filename",$A$1),FIND("[",CELL("Filename",$A$1))-1)

 

This works fine as long as your workbook has been saved. If you haven't saved it you will get a #VALUE! Error message. This problem goes away if you replace the above formula with the one below that incorporates an =IF statement:

 

=IF(CELL("Filename",$A$1)>"",LEFT(CELL("Filename",$A$1),FIND("[",CELL("Filename",$A$1))-1),"")

 

The formula uses the =CELL function to determine if the filename has been set. If it has, then the directory name is inserted in the cell. If it hasn't it returns a blank in the cell.

 

 
Excel2Printing Only Visible Data
Summary:  Many times in a large worksheet you may only want to print the visible data. Here's how to make this happen in all versions in Excel:

It is tricky to print just what is on the screen in a large workbook, especially if you are using Freeze Panes. You could highlight the area you want to print and then print this selection. However, this doesn't work if you have turned on Freeze Panes because you are trying to print several different areas of the worksheet.

 

To solve this problem you need to use the Repeating Rows option in Excel and then define your print area. Here are the steps you need to take to accomplish this task:

 

·         Select the Page Setup option from the File menu or the Page Setup tab if you are using Excel 2007 or 2010 click the small icon at the lower-right of the Page Setup group.)

·         Select the Sheet tab.

·         Put your insertion point in the Rows to Repeat at Top box.

·         In your worksheet, select the rows you want to appear at the top of each page. The row reference will appear in the dialog box.

·         Put your insertion point in the Columns to Repeat at Left box.

·         In your worksheet, select the columns you want to appear at the left of each page. The column reference will appear in the dialog box.

·         Click on OK to close the Page Setup dialog box.

·         Select the range of cells you want to print, but make sure you do not highlight the cells in the rows and columns that you defined in the Rows and Columns to Repeat steps above.

·         Now selected Print from the File menu and Excel will display the Print dialog box. To do this step in Excel 2007 or 2010 click on the Office button and then select Print.

·         Choose the Selection radio button to indicate that you only want to print the selected area.

·         Click on OK.

Your printout will now only contain the area of the worksheet that you selected, plus the frozen rows and columns. 

 
 
Excel3Deleting Rows that Meet a Particular Condition

Summary: There are times when you want to deleted a number of rows based on a value in a particular cell in each row. Here's how to use AutoFilter to do this. This will work with all versions of Excel. 


To delete rows when a condition is met you can use Excel's AutoFilter tool. This works really well if you have simple criteria to define which rows to delete. Here's the steps involved:

 

1.       Select the Header Row

2.       Turn on AutoFilter by selected Data|Filter|AutoFilter. In Excel 2007 and 2010 select the Sort and Filter icon in the Home Tab and select Filter.

3.       This puts pull-down buttons at the right side of each cell in the data table's header row. Using these pull-down buttons you can select the records you want to display. Select a filter value that will only display the rows that you want to delete.

4.       Now select the rows that are displayed and delete them.

5.       Turn off AutoFilter and you will be left with just the rows you want to display.

 

Click OK.

  

Word Tips

Summary: A Section break is used to split a Word document into two or more sections that can then be independently formatted. There are times when you want to move a Section break and here are the steps to do that in Word 2007 and 2010.

Section breaks in Word are treated the same as any other formatting character. If you are working in the Draft view, section breaks appear as a thin double line from one side of your document to the other. If you are not working in Draft view they are not visible, so you should change to the Draft view to make your editing easier or follow the steps below.

 

Section breaks are also not visible in the Print Layout view. To make them and all the other non-printing characters visible press Ctrl+* You can also do this by going to the Home tab of the Ribbon and selecting the Show/Hide tool in the Paragraph group.

 

Here are the steps to move a Section break:

 

1.       Click on the Section break.

2.       Press Ctrl+X to cut the Section break from your document.

3.       Place the insertion point where you want to insert the new Section break.

4.       Press Ctrl+V. The section break is inserted in your document.


Word2Capturing a Screen Shot in Word 2010
Summary: There are many times when it is useful to add a Screen Shot to a Word document. Word  2010 makes it easy to do this with its new Screenshot tool.   With this tool you can capture: an entire window or just a portion of the screen. Here are the steps to capture an entire window:

1.    Select the insert tab on the ribbon.

2.       Click the Screenshot tool, in the Illustrations group.

3.       Select the window you want inserted.

4.       Word will now display the captured screen in your document.

 

To display a portion of a window do the following:

 

1.       Select the Insert tab on the ribbon.

2.       Click the Screenshot tool, in the Illustrations group.

3.       The Word window is now minimized and a "gauze effect" appears over the screen.

4.       Your mouse pointer will change to a set of crosshairs.

5.       Click on the upper-left corner of the screen segment you want to capture and then drag your mouse to the lower-right corner. 


Outlook Tip

Outlook2How to Archive Old Mail in Outlook and Keep Your PST File Small

 

Summary: As you continue to send and receive mail in Outlook your Oulook .PST file grows and so does the time it takes Outlook to do what you want it to do. Here is how to speed up your Outlook and keep the .PST file manageable. 

 

A user's mailbox grows as items are created and received. As a mailbox grows larger, Outlook slows down in day-to-day operations, and the probability of data file corruption increases. To manage these potential problems, many users manually clean out old messages, tasks, and calendar items, but Outlook provides an automated process - AutoArchive - to handle this time-consuming task.

AutoArchive is a mystery for many Outlook users. By default, AutoArchive runs every two weeks and moves all of the messages and other items that are older than six months to an archive folder - a .pst file located on a user's computer. While almost all users have seen the prompt from Outlook to run AutoArchive, most cancel the operation because they are not sure what the process entails.

To set up AutoArchive properly requires three simple steps: 1) review and change the global settings to meet a user's needs, 2) apply the changes to all folders, and 3) customize the global settings for specific folders.

 To change the global settings for auto-archive:

  1. From the menu, select Tools, Options to open the Options dialog box.
  2. On the Other tab, click AutoArchive.
  3. In the AutoArchive dialog box, review and modify the settings to meet a user's needs.

In default, AutoArchive runs every fourteen days, archives all messages and items older than six months to a file named archive.pst, and deletes expired items. The expiration date for all messages and items is six months, except for Calendar items (which do not expire) and Sent Items and Deleted Items (which expire in 2 months).

K2 Enterprises recommends that users uncheck Delete expired items (e-mail folders only). All other settings should be configured to meet the needs of individual users.

Note that the name and location of the archive.pst file can be modified in this dialog box if desired.

Before closing the AutoArchive dialog box, make sure to apply the changes to all existing folders:

  1. Click Apply settings to all folders now.
  2. Click OK, OK to close the dialog box and complete the process.

Now that the modified AutoArchive settings have been applied to all folders, the only task remaining is to customize the settings for individual folders as necessary:

  1. In the Navigation Pane, right-click on a folder for which the archive settings need to be customized and select Properties from the context-sensitive menu.
  2. On the AutoArchive tab, adjust the folder's archive settings as required.

Among the folders that may require individual archive settings are Action/Hold, Company or Personal inboxes, InfoPath Form folders, and Calendar and Task folders. The Contacts folder is never archived. Follow along with your instructor as we explore AutoArchive.

Once AutoArchive runs, the Archive folder will appear in the Navigation Pane at the bottom of the folder list. Expanding the Archive folder will reveal a set of folders identical to those contained in a user's mailbox. As a message or item is archived, it is moved to the same location in the Archive folder.

In other words, a message in the Company Inbox will be moved to the Company Inbox in the Archive folder during the archiving process. All messages and items in the Archive folders can be accessed just as any other messages or items in a user's mailbox.

They can be searched with Instant Search, or moved to other folders in the archive or back to the main mailbox using drag-and-drop. The point is that users will have the same access to messages and other items that they have always had, notwithstanding that the messages and other items have been relocated to the Archive folder. The advantages gained are that a user's mailbox is smaller, opens faster and performs better, is less cumbersome to use, and is less likely to corrupt. 

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
647-722-4741