K2 Office Tips Header
Volume 12, Number 11 

December , 2017
Winter is now upon us and Nancy and I are getting ready for Christmas with our families.     
In October we hosted the annual Accounting Technology Seminar Series. This was the 25th year that we have traveled with Alan Salmon & Associates Inc. from coast to coast presenting this one-day update on the technology that accountants, bookkeepers and their clients use. It was the best one yet.   
Research has been a passion of mine for many years. I am currently putting the finishing touches on the 2018 Canadian Accounting and Bookkeeping Technology Survey. This comprehensive instrument will survey the demographics, practice management and technology that accountants and bookkeepers are currently using and what their plans are for future investments in IT. Look for your invitation to participate in early January.  
Our mini-webinars twice a month are turning out to be very popular. You will find information on them below. Attend in person and earn 1 verified CPD credit when you answer a minimum of 3 polling questions during the session. Every registrant will receive the recorded webinar so sign up today to ensure that you're on the list.   
On behalf of the K2E Canada Inc. team I want to extend to all of you season's greetings and best wishes for a prosperous 2018.    
More Ways to Earn Verified CPD credits with K2E Canada Inc.  
K2E Canada Inc. along with our partner K2 Enterprises offers a broad curriculum of over 50 technology-focused CPD webinars accessible from the comfort of your home or office. We invite you to explore our calendar of Live Webinars covering a wide range of topics including Securing Your Data, Practical Tools for Protecting Information , and Detecting and Preventing Spreadsheet Errors  
As usual, I welcome your comments at  alan@k2e.ca
Alan Salmo n, FCPB


Excel Tips


ExcelAutoFilterMoving a Worksheet from One Workbook to another            
Summary:  There are times when you want to move a worksheet from one workbook to another. Here is  how to do this:
  1. Make sure both the source and target workbooks are open.
  2. Display the worksheet you want to move.
  3. Choose Move or Copy Sheet from the Format icon in the Cells group on the Home tab. This displays the Move or Copy dialog box.
  4. Using the To Book pull-down list, and select the name of the target workbook. The list of worksheets in the Before Sheet list will change to reflect the workbook you selected.
  5. In the Before Sheet list, select the worksheet that should come after the worksheet you selected in step 2.
  6. Click on OK.
The worksheet is moved.
QuickCloseCalculating the Day of the Year
Let's assume you have a date in cell A2'. In C2 you want to calculate what the day of the year is. Use the following formula to do the calculation.


Dates and times are stored as numbers in Excel and count the number of days since January 1, 1900. December 3, 2017 is the same as 43072. The DATE function accepts three arguments: year, month and day. 

DATE(YEAR(2017),12,31) or 31-Dec-2017 is the same as 43100. Subtracting these numbers (43100 - 43072 = 28.  Of course, once you enter the formulas, you need to format the cells as regular numbers. (Excel will, by default, try to format the cells as dates.) With the two cells selected, follow these steps:
  1. Select the Cells option from the Format menu. Excel displays the Format Cells dialog box. 
  2. Make sure the Number tab is selected. 
  3. In the Category list, choose Number.
  4. Make sure the Decimal Places option is set to 0.
  5. Click on OK. 
One of the worksheet functions provided by Excel allows you to quickly and easily count the number of blank cells in a range. The format of the function is as follows:
The function returns an integer value representing the number of blank cells in the range. Be careful though. If you have the display of zero values suppressed for the worksheet, a cell can appear blank when it is not really blank. COUNTBLANK returns blank cells, not counting those that would have a zero displayed if you chose to display such values.   


Word Tip


WordTipAdding Hyphenating a Document          
Summary: Hyphenation is the process of adding hyphens to words at the beginning of lines so the hyphenated part will fit at the end of the previous line. To use the hyphenated tool follow these steps:
The hyphens are inserted between syllables in a word. You can let Word automatically hyphenate your document, or you can use the hyphenation tool to manually hyphenate your document.
  1. Go to the beginning of your document (this is not necessary, but saves time when hyphenating).Display the Layout tab of the ribbon. (In Word 2007 or Word 2010 select the Page Layout tab of the ribbon.)
  2. In the Page Setup group, click the Hyphenation tool. Word displays a series of choices.
  3. Select Hyphenation Options. Word displays the Hyphenation dialog box. 
  4. Use the controls in the dialog box to specify parameters of how the hyphenation is done.
  5. Select the Automatically Hyphenate Document check box if you want Word to make all the hyphenation decisions for you. When you subsequently click on OK, you can skip the rest of these steps.
  6. Click on the Manual button.
  7. You are shown the first possible hyphenation.
  8. If the word being proposed for hyphenation is long enough, you may be able to specify a different place for it to be hyphenated. Simply click on one of the proposed hyphenation points.
  9. Click on Yes to hyphenate the word at the noted point, or click on No to skip hyphenating the word. You are then shown the next possible hyphenation.
  10. Repeat steps 9 and 10 for the remainder of the document.
Back to top    
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   CLICK HERE
Your information is always kept secure and never shared.
Alan Salmon
K2E Canada Inc.