K2 Office Tips Header
Volume 13, Number 1 

January , 2018
Happy New Year from a snowy Brampton, ON. As we start the 13th year of the K2E Canada Inc. newsletter I want to share a look ahead at our new year's activities.
Research has been a passion of mine for many years and we have launched the 2018 Canadian Accounting and Bookkeeping Operations and 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.  Your ROI in return for 15 minutes of your time is:  
  • a copy of the survey report, a $99.95 value; 
  • a special Excel Magic webinar worth $49.95 and one non-verifiable CPD;
  • a chance to win an iPad Mini;
  • a chance to win one of six one hour consultations with me to discuss your Excel problems
  • a chance to win one of 10 paper copies of "The E-Myth Bookkeeper"
Please help me with my research by clicking on  TAKE THE SURVEY.
In 2018 we will continue our "Road to Excellence" mini-webinars. 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.    
New for 2018 is our broad curriculum of over 50 technology-focused CPD live 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
As usual, I welcome your comments at  alan@k2e.ca
Alan Salmo n, FCPB


Excel Tips


ExcelAutoFilterUsing Text to Columns to Convert Text to Dates            
Summary: Working with dates requires those dates to be in an Excel Date format. Here is how to convert a text date to one of the many Excel date formats:     
  • Select the range of text dates.
  • Click the Data tab..
  • Select Data Tools | Text to Columns and a wizard dialog box will open.
  • In the wizard dialog box, click Next once to open the next window.
  • Check fixed width.
  • Click Next once to open the next window. 
  • Double click on each of the vertical lines so the converted date will appear in a single cell.
  • Click next.
  • Select the Date Radial Button and select the date format that you want to use.
  • Click Finish and the text dates will convert to dates that can be formatted and used in calculations.
Summary: It is often useful to have the file name 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. 

Summary: Analyzing data is easy with Excel's AutoFilter. Here is how to use it:
If AutoFiltering is not already turned on, display the Data tab of the Ribbon and click the Filter tool.
Use the drop-down arrow to the right of a column label to select Number Filters | Custom Filter or Text Filters | Custom Filter. (The names of the options, and thus the choices you make, depend on the composition of your data.) Excel displays the Custom AutoFilter dialog box.  
Use the controls in the dialog box to set the criteria you want used for filtering your list.

Click on OK.

You can use the Custom AutoFilter dialog box to set any combination of criteria that you need. For example, you can tell Excel that you want to see any values below, within, or above any given thresholds. The filtering criteria will also work with text values. For instance, you can tell Excel to display only records that are greater than CE. This means that anything beginning with AA through CE will not be displayed in the filtered list. 

Excel also allows you to use wildcard characters to filter text values. These are the same wildcards that you can use in other programs. The question mark matches any single character, and the asterisk matches any number of characters. If you wanted to only display records that have the letter C in the second character position, you would use the equal sign operator (=) and a value of ?C*. This means the first character can be anything, the second character must be a C, and the rest can be anything. 

AutoFilter is a highly useful tool for when you are analyzing large sets of data.


Word Tip


WordTipHighlighting Information in a Word Document Using Shading          
Summary: There are times when you would like to highlight a piece of text in your Word document. Here is how to do this: 

You can do this with Word's shading tool.  
  • To add shading to a paragraph involves the following steps: Select the text that you want to shade. (Tip - Triple Click anywhere inside the paragraph and your paragraph will be selected).
  • Click on the Home tab of the ribbon.
  • In the Paragraph group, click on the down-arrow to the right of the Shading tool. (This tool looks like a tilted paint bucket, spilling paint to the right.) Word then displays a shading palette.
  • Hover over a color and your paragraph will be shaded with that color. 
When you find a colour that you like, click on the colour and your paragraph will be shaded. 

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.