K2 Office Tips Header
Volume 10, Number 1  

January, 2015

Happy New Year. 2015 has arrived and it promises to be another busy year. Nancy and I had a wonderful Christmas with visits from our children, grandchildren and in my case, my great grandson.

 

This month marks the tenth year and the one hundred and fifth issue of my Office Tips newsletter. It now goes out to over eleven thousand accountants and bookkeepers in Canada. It has been a pleasure producing it each month.

 

I will be making a number of enhancements to this section of the newsletter this year. From time to time, I'll post a link where you can download relevant content; starting this month by posting a link to some of my favourite Windows keyboard shortcuts. To download this small Word file CLICK HERE.
  
Many of you have asked for an index of past tips and I expect to have the 2014 index available for download when you get the February newsletter. 

 

Once in a while, I will let you know about events that I think you would like to be made aware of. There are two this month; the first is Intuit Canada's Big Tax event in Toronto on January 19 -21, featuring an impressive list of speakers. For more information CLICK HEREIf you are going to register use the code INTUIT50  to get a fifty percent discount off the registration fee.

 

The second is Sage's Inspire Tour in Vancouver on February 5th.

The Cupcake Girls are the featured speakers. For more information CLICK HERE.

 

As always, I welcome your comments at [email protected]

 

Sincerely, 

 

Alan Salmon

Excel Tips

Excel1Getting Rid of Spaces in Cells  

Summary: Many times when you import data into Excel from other programs, some of the cells will contain extra spaces. The cells appear to be empty, but in reality spaces are there and will give you incorrect answers when you do things like "counts". Here is how to get rid of these annoying extra spaces: 
 
 

The easiest solution is to use the Find and Replace dialog box in Excel. 
  1. Click on the Editing Group on the Home tab of the ribbon.
  2. Click on Find and Select.
  3. Click on Replace.
  4. In the Find What box, enter two spaces.
  5. Leave the Replace With box empty.
  6. Click on  the Options Button.
  7. Select the Match Entire Contents Check Box.
  8. Click on Replace All.
  9. Repeat Steps 1 through 5, but this time only use one space in Step 2.
  10. Close the Find and Replace Dialog box.
The spaces have now been removed from the selected cells.

 


Excel2ADeleting Duplicate Columns          

Summary:  With large worksheets you sometimes have two columns with exactly the same data in them. Before you delete one of the columns you want to make sure the data is exactly the same. Here is how to do this: 


The first step is to check if the two columns are identical. Let's assume you have 1,000 rows of data in column A and column B and you think the data is identical. We will use an array formula to confirm that the data is identical as follows:

 

           {=AND(A1:A1000=B1:B1000)}
 

Instead of hitting the enter key after we have entered the formula we enter Shift+Ctrl+Enter. When you do this note the { symbol at the beginning of the formula and the } symbol at the end. This turns it into an array formula.  This array formula then compares all the values in the first 1,000 rows of Columns A and B. If they are the same the formula will return TRUE. If any of the cells are different, then the formula returns FALSE. If the answer is TRUE you can then safely delete one of the columns.

Back to top

 

Summary: Calculating the Median of a set of numbers is easy with Excel's =MEDIAN function. Here is how to do this: 

Let's assume you have a column with 1,000 rows of data and you need the median of the set. Here is the formula to do the calculation:

       =MEDIAN(A1:A1000)

It's that easy. 


 

Word Tip

 

        

Summary: Normally, we select a character, a word, a sentence or a paragraph. These are all horizontal selections. Sometimes you may need to select vertically. Here is how to do this: 

 

          1  Text 1

          2  Text 2

          3  Text 3

          4  Text 4

          5  Text 5 

 

To select text vertically, hold down the ALT key and click and drag vertically to make the selection. Remember to release the ALT key before you release the mouse. 

 

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

[email protected]  with "Subscribe to newsletter" in the Subject. 
Your information is always kept secure and never shared
 
Alan Salmon
Managing Director
K2E Canada Inc.
647-722-4741