K2 Office Tips Header
Volume 12, Number 3 

March , 2017
Spring is officially here but you wouldn't know it here in Brampton, ON. It is sunny and a cool -3 as I write this. 

Our monthly Excel Magic mini-webinars have been a great success. The next two sessions will be in May in conjunction with CCH and Doc-It. Register for the sessions before you get buried in tax returns by clicking the buttons below. Every registrant will receive a recording of the webinar along with the teaching files used during each session.

As many of you are aware, research is an important part of my life. I am currently conducting a survey to find out how you keep up to date on technology. Questions dealing with what Facebook and LinkedIn groups you belong to; how do you get your news: do you listen to podcasts; what conferences do you attend; how do you like to get your PD credits and more. There are only 20 questions and most of them are YES/NO so it shouldn't take you very long. I would appreciate it if you would CLICK HERE and give me your feedback. 

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

Alan Salmo n

Excel Tips

ExcelTopDec Moving Around Between Directories
Summary: If your Excel files are in different directories changing paths in the Open Dialogue box can be a pain. Here is a shortcut that helps to minimize that pain: 
Let's assume you are working on a workbook. You then open a second workbook in a different directory. (You find it and open it after clicking your way to it in the Open dialog box.)

Now you want to open a third workbook. When you display the Open dialog box, Excel assumes you want to start from where you opened the previous workbook. But what if you want to open it from the same directory where the first workbook is located? You could use the Open dialog box to go back to the original directory, but that is a pain. Here's a neat shortcut to ease the pain:
  1. Open the first workbook.
  2. Press F12. Excel then displays the Save As dialog box, starting from the directory in which the workbook was originally loaded. (This is the directory you want.)
  3. Close the Save As dialog box by immediately pressing Esc or clicking on Cancel.
Now when you use the Open dialog box, Excel starts in the folder you were last in, which was the one displayed in step 2-the one you want.

Summary: It is useful some times to know the Week Number of the Year. Here is how to do this calculation:
We can use the WEEKNUM function to quickly determine how many weeks have elapsed from January 1st to a given date in the same year.  The following formula returns 11 because 11 weeks have elapsed since January 1, 2017.
Results = 11
We can also reference a cell that contains a date.  If the date 3/15/2017 was in cell A2, then the following formula would return the same result.
It's important to note that the function only calculates the number of weeks from the specified date in the same year.  If we used "3/15/2016" in the WEEKNUM function, the result would be the number of weeks from January 1st, 2016, which is 12.  The week starts on Sunday, so the same date might have a different week number result from year to year.
  Back to top  
Summary: There are times when you want to move a column or contiguous columns to a different location on your worksheet. Here is a quick way to accomplished this task:  
If you want to move a column in Excel from one section of a worksheet to another (e.g. from Column E to column B you would probably use the Cut and Insert Cut Cells commands.
However, an easier and quicker way to do this is to do the following:
  • Click on the column letter/letters that you want to move (must be a contiguous selection).
  • Go to the vertical line at the side of the column(s) you want to move.
  • Your mouse pointer will turn into four arrows.
  • Keep your mouse button pressed and also press the Shift key on your keyboard.
  • Move the selection to the column where you want this data to be shifted to. When you bring the selection to the edge of any column, you should see a vertical line.
  • Release the mouse button.
  • Release the Shift key (remember to keep the Shift key pressed until the end).
  Your column(s) will be moved.


Word Tip


Summary: Deleting words and phrases can be a tedious task when you are working in a document. Here is a tip to speed up the process.
When you are editing a document, there are many occasions when you want to delete a word or a phrase. There are several different ways to handle this task. For many years, I just selected the text and hit the delete key. Or I kept pressing the  Delete or  Backspace keys until the unwanted text was gone.
Then I learned a neat little trick. All you need to do is hold down the  Ctrl key to speed up your deletions. Using  Ctrl+Delete deletes text from the insertion point to the end of the next word. For instance, if you wanted to delete six words to the right, you merely press  Ctrl+Delete six times. Similarly,  Ctrl+Backspace deletes words to the left of the insertion point.
You can also use this to speed up the editing process. For example, if you wanted to  change the word "bookkeeping" to "bookkeeper" You would usually simply delete "ing" and type "er." This would involve pressing Delete or Backspace three times to get rid of the unwanted portion of the word. You can make your edit faster if you just position the insertion point at the beginning of "ing," press  Ctrl+Delete once, and then type "er."  
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
Managing Director
K2E Canada Inc.