K2 Office Tips Header
Volume 7, Number 7  
July 1
, 2012
ExceltopIn This Issue 
Excel Tips
PivotTable Quick Sort

Quick Link
 Technology Tips Video
WordtopIn This Issue 
Word Tip
OutlooktopIn This Issue
 Outlook Tip

PPTTopIn This Issue
PowerPoint Tip
Quick Links



Previous versions of Office Tips



Like us on Facebook 


Follow us on Twitter



View our profile on LinkedIn 

















Summer is now upon us. Nancy and I will get to enjoy our cottage for six weeks in August and early September. This month's office tips is a little late because I was working in Busan, Korea in late June and early July.


I am pleased with the response to my latest eBook, entitled Excel Charts - Part 1. This ninety page book is chock full of tips that will help you create great charts in Excel. Each of my seven eBook sells for $34.95 and gets you 2 CPDs. Look for my first Word eBook by the end of this month.


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

Excel1How to Quick Sort a PivotTable 

Summary: The quick sort buttons in Excel offer one-click access to sorting cell values. There are four entry points to the quick sort buttons. Here is how to do this: 

  1. On the Home tab, select Editing, Sort and Filter, Sort A to Z or Sort Z to A.  or
  2. On the Data tab, select Sort & Filter, select either the AZ or ZA quick sort buttons to sort the active field. or
  3. Rick-click in a cell in the PivotTable, select Sort and choose from Sort A to Z or Z to A.
  4. From a PivotTable filter drop down, select Sort A - Z or Sort Z - A.
Unlike sorting outside a PivotTable, it doesn't matter if you have more that one selected during the sort. Excel will automatically sort the entire PivotTable. If multiple columns are selected Excel will only sort the left most column in the selection. 


Excel2AExcel Header and Footer Codes      

Summary: Excel has a number of header and footer codes that provide short cuts to entering data in a header and/or footer. This makes entering such data much easier. Here is a table that contains the most important codes: 


The codes are entered directly into a header or footer by typing an ampersand character (&) followed by the command. The table below contains a list of the most important controls.



 &F       Workbook Name

&A       Worksheet Name

&P        Current Page Number

&&       Ampersand character

&D       Current Date

&T        Current Time  

Back to top


Excel3Deleting Duplicates from a Data Set   
Summary:  It is very easy to delete duplicate values from a data set. Here is how to do this.
  1. Select a cell in the data set.
  2. Go to the Data Tab.
  3. Click on the Remove Duplicates in the Data Tools group.
  4. Excel will highlight the data set. If there are columns missing in the selection, go back and make sure there are no blank columns in the selection.
  5. Make sure that My Data has Headers is selected if your data set has headers.
  6. By default, all the columns are selected. A selected column means that the tool will use all the columns to check for duplicates. Duplicates in an unselected column will be ignored. Select the columns that you want to match for duplicates. 
  7. Click OK. The data set will update, deleting any duplicate rows. A message will appear to tell you how many rows were deleted and how many rows remain in the data set.

    Note there is no undo for this process. Save your work before you run this process. 
Back to top 


Word Tips


Word1Adding Hyphenation


Summary: Word has a hyphenation feature that automatically hyphenates words at the end of lines. This provides a better fit and eliminates uneven margins. Here is how to do this: 
  1. Click on the Page Layout tab.
  2. Click Hyphenation in the Page Setup Group.
  3. Click Hyphenation Options to open the Hyphenation dialog box.
  4. Select the option you want. The options are as follows:
    1. Automatically Hyphenate Document enables automatic hyphenation as you type.
    2. Hyphenate Words in CAPS hyphenates words typed in all upper case letters.
    3. Hyphenate Zone sets the distance from the right margin within which you want to hyphenate the document. (The lower the value, the more words are hyphenated.)
    4. Limit Consecutive Hyphens sets the maximum number of hyphens that can appear in consecutive lines.
  5. Click on OK.


Outlook Tip


Summary: An electronic business card packs a lot of information into a small package. If you receive an electronic card in an e-mail you can automatically create a new contact when you save the message.  Here is how to do this::

  1. Open Outlook.
  2. Open the e-mail message.
  3. Double click on the electronic business card attachment. This will open the contact window.
  4. Click Save and Close.
  5. If you already have a contact with the same name, Outlook will display the Duplicate Contact dialog box.
    1. Click Add New Contact if you want a duplicate contact.
    2. Click Update Information of Selected Contact, and then double click on the contact you want to update. The contact window will open. Make the necessary changes, and save the contact information.
  6. Click to select any desired conditions that limit which messages the rule applies to, and then click the link in the description panel, which may display a dialogue box to specify the exact criteria.
  7. Click Update.

You will now be returned to the Rules and Alerts dialogue box, which will now show your next rule.


 PowerPoint Tip


PowerPointarticleDrawing a Line on a Slide


Summary: There are times when you will want to add a line of an arrow on a slide. Here is how to do this:


  1. Click on the Home Tab or the Insert Tab.
  2. Click on the Shape Button in the Drawing Group.
  3. Select a Line shape from the available list.
  4. Click and drag the mouse pointer to add the line to your slide.
  5. You can make the line as long as you want.
  6. Release the mouse button to draw the line.
  7. Next you will probably want to change the style of the line.
  8. Click on the line.
  9. Click on the Home tab.
  10. Click on the Quick Styles button in the Drawing Group.
  11. Highlight the style that you want to use.
  12. Click on it to use it.
Be careful if you use the Replace All button that you are certain that only the text you have selected will change. You do not want to change part of a word because it closely matches your criteria.


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