K2 Office Tips Header
Volume 11, Number 10 

October , 2016
Fall is certainly upon us and my coffee breaks on our amazing deck with Nancy have come to an end. 
At the end of September we travelled to Vancouver to attend the Institute of Professional Bookkeepers of Canada annual conference in Richmond, BC. It was a great event and  I had the privilege of doing one of the keynote speeches.
All good things have to come to an end. The flight to Vancouver was my last flight. It is just too challenging and exhausting now for Nancy and I to fly. So after 60 years of flying, almost 2,000 flights, 2.5 million miles in the air and 9 trips around the world, it is time to tell Air Traffic Control (ATC) to please close my flight plan.
Our latest venture is a series of FREE 1 hour mini webinars that are held once a month. The first one this week, entitled "K2's Excel Magic and TaxCycle Excel Integration", was full within a day and we had to add a 2nd session, which was also full.
The next one is scheduled for November 16th at 12:00 PM EST and is entitled "K2's Excel Magic and CCH iFirm - Simplify and Take Control of Your Practice". It lasts for an hour and features 25 minutes of my Excel Magic tips  and then switches over to CCH where you get a first look at their connected online solution that will help you run a better business and keep your staff and clients connected and happy 
The session will be recorded, so if you can't join us for the live session, register and you can view the webinar on your time. You will receive 1 CPD for attending.  CLICK  HERE to register for this free event.

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

Alan Salmo n

Excel Tips

ExcelTopE-mailing a Single Worksheet
Summary:  Many times you may need to e-mail a single Excel worksheet; not the entire workbook. There are multiple ways of accomplishing this task and the best way is contingent on whether you want the recipient to retain worksheet capabilities. This tip will show you two of the quickest ways to accomplish this task.

Method 1
First add the Send to Mail Recipient to your Quick Access Tool Bar. If you do this and click on the Icon, 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 labled Send the Current Sheet as the message body and then clicking on OK causes the current worksheet to be entered in the body of an outbound e-mail message. You only need to enter the recipient's address and send the message.
While this method works great if only want 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 in the body of the message.
Method 2
If you would like to send the current worksheet as an attachment and preserve Excel functionality for your recipients do the following steps:
  1. Right click on the worksheet tab you want to send and select Move or Copy to open the Move or Copy dialogue box.
  2. In the Move or Copy dialog box, select new book in the the TO drop-down list.
  3. This will create a new workbook with only the selected worksheet in it.
  4. Go to File, Send to send the new single sheet workbook as an e-mail attachment to your recipient.
Using this method, the recipient will have full Excel functionality.

ExcelTip2Inserting the Filename and Date Into the Header
Summary: It is often useful to have the filename and date in the header of a workbook. Here is how to do this:   
  1. Go to the View tab.
  2. Click on Page Layout.
  3. Move your mouse to the top of the worksheet where it says "Click to Add Header".
  4. Click on the left header field.
  5. Go to Header & Footer, Tools Design and select the File Name. The code for this is &[File] and it will be placed in the header.
  6. Click on the right header field.
  7. Go to Header & Footer, Tools Design and select Current Date. The code for this is &[Date] and it will be placed in the header.
  8. Click anywhere in the worksheet to leave the header.
The codes will update and show the actual file name and date.

Selecting a Range By Using the Shift and Arrow Keys
One of the easiest ways to select a range is to press and hold the Shift key and then use the arrow keys to highlight the cells. For larger areas you can use the Shift key plus the PgDn or PgUp key.  
You can also use the End key to quickly extend a select to the last non-empty cell in a row or column. For example to select the Range B3:B8, select cell B3. Then hold down the Shift key while you press the End key, followed by the down-arrow 
key. In the same manner, to select B3:D3, hold down the Shift key while you press the End key, followed by the the right-arrow key
  Selecting the Current Range
When you need to select a large area of cells move your cursor anywhere within the range you want to select, and press Ctrl+A.

Selecting a Range by Shift+Clicking
When you want to select a very large range, using the mouse is usually the most effective method, but dragging is not required. Select the upper left cell in the range. Then move to the lower right hand corner of the range and press Shift, and then click on the lower right cell.
Selecting Non-Contiguous Ranges
There are times when you will want to select multiple ranges in a worksheet. An example would be when you want to apply formatting to cells in different areas of a worksheet. Instead of doing each section separately, do it all at once with one of the following tips.
Using the mouse, hold down the Ctrl key while you select the different ranges.

From the keyboard, select the first range by holding down the Shift key and then highlighting the range. Then press Shift+F8 to select another range without cancelling the previous range. Repeat this as often as necessary. 
Selecting Entire Rows
To select a single row, click on the Row number on the left hand side of the worksheet. Or select any cell in the row and then press Shift+spacebar.  To select multiple adjacent rows, click and drag in the row number area.

Selecting Entire Columns
To select a single column, click on the Column letter at the top of the worksheet. Or select any cell in the row and then press Ctrl+spacebar.   To select multiple adjacent columns, click and drag in the column letter area.

Selecting Multiple Sheet Ranges
Ranges can extend across multiple worksheets. To select the same range on multiple worksheets, select the range in the first sheet. Then select the worksheets to include in the range. Click on the first sheet tab. Then hold down the Control key and click on the tabs of the sheets you want to select.
If the sheets are in order, select the range in the first sheet. Then hold down the Shift key and click on the last tab in the group.

  Back to top


Word Tip


Summary: Adjusting the line spacing in a paragraph can often improve the readability of your text. Here is how to do this:
  1. Click in a paragraph , or select all the paragraphs whose line spacing you want to change.
  2. On the Home tab, click on the Line Spacing button.
  3. If you do not see the spacing you want, click Line Spacing Options.
  4. Select At Least to create a minimum space between lines, which can increase if large font sizes are used.
  5. Select Multiple to specify how many lines of space you want between the lines of text. Use the Arrow or type a value.
  6. For Exactly and At Least settings, this is is a distance setting measurement, usually in Points.
  7. For Multiple, this is the number of lines of space.
  8. Click on OK.

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 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
Managing Director
K2E Canada Inc.