K2 Office Tips Header
Volume 11, Number 4 

April, 2016
Still too cool to sit out on our deck, but hopefully that will change soon.
It's the end of tax season for many of you and time for a brush up on your Excel knowledge. This month I am going to share with you my favorite all time Excel tip, plus two more useful ones.
I will be presenting my popular Tech Update session at the Canadian Accounting Technology Show in Toronto on May 16th.  I did the show last year and it was a lot of fun.
Lots of new things in the works. We just launched a completely new www.k2e.ca website. You can now find information on all of the seminars that we teach across Canada. This includes dates, course information and links to register. Shortly we will be announcing a new relationship with the CPA community that will provide you with a catalog of webinars and recorded videos that are excellent and will qualify for full CPD credits.
We will launch registrations for the 2016 Accounting Technology Seminar series shortly. We are excited about this year's program which features a revised schedule with two new excellent partners. You can register at www.k2e.ca.
And finally we are looking for a part time instructor who is located in either Calgary or Vancouver. If you have excellent Excel skills and some teaching experience please contact me at alan@k2e.ca

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

Alan Salmon

Excel Tips

ExcelTopCtrl S - My All Time Favorite Excel Tip
Summary: I often get asked what my all time favorite Excel Tip is and I always answer Ctrl S:  
I have been around too long so I grew up in the computer world using the keyboard. As a result I use as many of the over 175 Excel keyboard shortcuts as possible.
Ctrl S which saves the current workbook has saved my life many times. Every couple of minutes I hit those two keys. My brain is programmed to to this. 
Note: If you are interested in a list of the over 175 Excell keyboard shortcuts drop me a note at alan@k2e.ca and I will be happy to send it to you.
Excel2ASumming Visible Cells  
The Sum function in Excel adds all of the cells in a column or row, even if the columns and rows are hidden. The solution to just adding the visible cells is to use the =SUBTOTAL function. 
 Assume that you have a column of numbers in column A from A1:A100 and that some of the cells are hidden. 
To get the total for just the visible cells you would use the following formula: 
The formula only calculates the sum of the visible cells in the range.

Excel3Deleting Duplicates from a Dataset 
  1. Select a cell in the dataset.
  2. Go to the Data Tab.
  3. Click on the Remove Duplicates in the Data Tools group.
  4. Excel will highlight the dataset. 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 dataset 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 dataset 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. 


Word Tip


WordTip2Stopping Word from Capitalizing a Word Automatically
Summary: Microsoft Word will sometimes change the capitalization of a word that you type when it shouldn't be captialized. If this is happening to you frequently here are some steps that you can take to prevent this from happening.  
If you want to prevent Word from capitalizing proper names, such as Alan or Nancy, you can try the following:
  • Go to the Word Options dialog box. In Word 2007 click on the Office Button and then click on Word Options. In Word 2010 , 2013 and 2016 click on the File tab in the ribbon and then click Options.
  • Click Proofing.
  • Click on AutoCorrect.
  • Clear the Correct Two Initial Capitals check box.
  • Clear the Capitalize First Letter of Sentences check box.
  • Clear the Capitalize Names of Days check box.
  • Then go through the list of AutoCorrect entries and delete or modify any entries that capitalize a word.
  • Clear the Automatically Use Suggestions from the Spell Checker check box.
  • 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.

K2E Canada Inc. | Unit 10 - 8550 Torbram Road | Suite 121 | Brampton | Ontario | L6S 6H4 | Canada