K2 Office Tips Header
 Volume 6, Number 11  November 1, 2011
ExceltopIn This Issue 
Excel Tips

Quick Link
 Technology Tips Video
OutlooktopIn This Issue
 Outlook Tip

Quick Links


Previous versions of Office Tips



Follow me on Twitter





Seven weeks until Christmas and I am right in the middle of our busiest time of the year. Doing a seminar somewhere in Canada every day until the week before Christmas. No quite true as I get three days in Bermuda teaching the third week in November. Tiring at times but still fun. 


The end of the year is fast approaching and if you are short of PD credits my eBooks are a bargain. There are seven available, with great tips and you get you 2 CPDs for each course. Included in the 7 eBooks are two eBooks on PivotTables. Great way to learn how to use this great Excel tool. Each eBook is $34.95. The link to my web store is http://www.mcssl.com/store/alanswebstore.


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



Alan Salmon

Excel Tips

Excel1E-mailing a Single Worksheet 

Summary: Many times, you may need to email a single Excel worksheet - and not an entire workbook. While there are multiple ways to complete this task, selecting the best way is contingent upon whether you want the recipient to retain worksheet capabilities. In this tip, we will show you two of the quickest and easiest ways to accomplish this task.

Method 1

First add the Send to Mail Recipient icon to your Quick Access Toolbar in Excel 2007/2010. If you do this, when you click the icon while in Excel, Excel will prompt you to see if you want to send the entire workbook as an attachment, or if you want to send the current worksheet in the body of the message.

Selecting the option labeled Send the current sheet as the message body and then clicking OK causes the current spreadsheet to be entered in the body of an outbound email message; you need only to enter the recipient's address and send the message.

While this method works great if you only wish for your recipient to see the data, the downside is that the recipient does not have Excel functionality with the data when they receive it, as it is the body of the message.

Method 2

If you would like to send the current worksheet as an attachment - and thereby preserve Excel functionality for your recipients - begin by right-clicking on the worksheet tab you wish to send and choose Move or Copy... to open the Move or Copy dialogue box. 

In the Move or Copy dialog box, select new book in the To book: drop-down list and check the box next to Create a copy. This will create a new workbook with only the selected worksheet in it. From there, you could use the Send to Mail Recipient option discussed previously to send the new, single-sheet workbook as an email attachment to your selected recipient. Using this method, the recipient will have full Excel functionality.

Excel2AOptimizing a Result With Goal Seek

Summary: Excel has a powerful tool called Goal Seek for finding a way to reach your goals. Here is how to do this:

For example, if you are buying a home your goal may be to pay a specific amount each month on your mortgage. You want a mortgage of $200,00 and you can afford a monthly payment of $900.


You can use the Goal Seek tool in Excel to adjust one of the loan terms, such as the interest rate or the loan amount to reach your goal of $900 a month. In this example, we want the monthly mortgage payment to be $900.


Your first step is to create a simple table to calculate the monthly payment using Excel's =PMT() function. See the the example below. Then do the following:

  1. Click on the cell that contains the value you want to reach.
  2. Click on the Data tab. 
  3. Click on the What-if analysis icon.
  4. Click on Goal Seek.
    The Goal Seek dialogue box will appear. 
  5. Type the value you want to reach.
  6. Click or type the cell address of the cell whose value you want to change.
  7. Click OK.
  8. The Goal Seek status dialogue box will appear.
  9. The results will appear in the worksheet.
  10. Click OK to accept the change.
In this example, the principal is kept the same. The $900 monthly payment can be reached by finding an interest rate of 4.2%.
Back to top


Excel2Sorting by Color Using the Sort Dialogue Box
Summary:  Sorting by values is the usual type of sorting you do in Excel. However, Excel 2007 and Excel 2010 let you sort by font color, fill color or icon set. Here is how to sort by color.
  1. Make sure that your data doesn't have any blank rows or columns.
  2. Select a cell in the data set. Excel will use this cell to define the boundaries of your data set.
  3. Right click on the cell and select Sort, Custom Sort.
  4. Be sure that the My Data Has Headers, check box in the upper right hand corner is selected.
  5. Be sure that all the data columns are selected. If they are not all selected, a blank column exists, and that confuses Excel as to the size of your data.
  6. From the Sort By drop down, select the first column header to sort by.
  7. From the Sort By drop down, select Cell Color.
  8. From the first Order drop down, select the color by which the column's data should be sorted.
  9. From the second Order drop down, select whether the color should be sorted to the top or bottom of the data. If you select multiple colors to sort at the top of the data, the colors will still appear in the order chosen.
  10. If you want to sort the data again by another column, click on Add a Level and repeat steps 6 though 9. Repeat these steps until all the columns you want to sort by are configured.
  11. If you get the order wrong, use the Up or Down arrows to move it to the right location.
  12. Click OK to sort the data.
Back to top 

Word Tips

Word1Creating Horizontal Lines as You Type          
Summary: You can add horizontal lines in a Word document as you type. Here is how you do it: 


You can add a line across a paragraph as you type.

  1. Press the Enter key to create a new paragraph.
  2. Type --- (three hyphens) and press Enter.
  3. A single, light horizontal line will be created between the left and right margins.
  4. Type === (three equal signs) and press Enter.
  5. A double horizontal line will be created between the left and right margins.
  6. Type ___ (three underscores) and press Enter.
  7. A single, heavy horizontal line will be created between the left and right margins.   

  Back to top


Word2Adding Borders and Shading to Text


Summary: If Borders and shading can be added to any amount of text. Here is how to do this:
  1. Select the text for which you want to have a border or shading.
  2. In the Home tab Paragraph group, click the Borders down arrow, and then select the type of border you want to apply. If you have selected less than a full paragraph, you can only select a four-sided box.
  3. To add Page Borders, click the Page Border tab, click the type of box, the line style, colour, and width you want.
  4. To add shading, click the Shading tab, and click the colour or shading, or fill you want.
  5. To add shading, click the Shading tab, and click the colour of shading, or fill you want.
  6. When you are done with the Borders and Shading, click OK to close it. 

Back to top


Outlook Tip

Outlook2Embedding a Picture in an E-mail Message 


Summary: There are times when it is useful to embed a picture into the body of an e-mail message. Here are the steps to do this: 

  1. Click in the message body of your e-mail.
  2. Click on the Insert Tab and click on Picture in the Illustration group.
    The Insert Picture dialogue box appears.
  3. Select the picture file you want, and click the Insert down arrow.
  4. From the sub menu click Insert to embed the picture in the message.  You can then drag a corner to resize the image; click in the middle of the image to move it around; and right mouse click to display the Format Picture dialogue box and edit the picture. 

Back to top


K2 Enterprises Canada 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
President & CEO
K2 Enterprises Canada