K2 Office Tips Header
Volume 12, Number 2 

February , 2017
Hard to believe but spring came to Brampton, ON last week with temperatures rising to 17 degrees. I checked the long range forecast and it shows temperatures hovering just above freezing and no major storms through the middle of March. It would be nice to have an early spring.

Our monthly mini webinars have been a great success with 12 sold out episodes. We are on a break now until after tax season, but look for a new series of my Excel Magic tips in May.

This is the time of the year when I work on scheduling for our 2017 seminars, webinars and in house training. I am also working on two new ventures that we expect to launch in the 2nd quarter of 2017.  

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

Alan Salmo n

Excel Tips

ExcelTopDecDeleting Duplicates from a Dataset
Summary: It is very easy to delete duplicate values from a dataset. Here is how to do this: 
  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 dataset.
    Note there is no undo for this process. Save your work before you run this process. 
ExcelSecondDec Want to Automatically Sum a Column or Row From the Keyboard?
Summary: There are many different ways to sum a column or a row. Here is how to quickly do it from the keyboard:  
  1. Select the first empty cell at the bottom or to the right of the numbers you want to add.
  2. Select ALT+"=".
  3. Press the Enter key.
The numbers will be summed. 

  Back to top  

Summary: There are times when you want to copy a list of unique items to another column. This involves using filters in Excel.
Here are the steps to filter a dataset for unique items.
  1. Select the data to be filtered.
  2. Go to the Data tab.
  3. Click on Advanced in the Sort and Filter group.
  4. Select Copy to another location.
  5. Select Unique Records.
  6. In the Copy To box enter the cell location where you want Excel to place the Unique Records.
  7. Click on OK.
You will now have a list of the unique items in the original data set.

Back to top


Word Tip


WordDecDisplaying the Ruler
Summary: The ruler appears at the top of each Word document window. It is highly useful when you need to adust your formatting. There are times, however, when the ruler is not visible. When that happens here is how to get it back:
Word allows you to control whether the ruler is displayed or not. If the ruler is not displaying do the following:
  1. Click on the View tab of the ribbon
  2. In the Show group click on the ruler check box.
The ruler will now be visible.

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.