K2 Office Tips Header
Volume 9, Number 5  

May, 2014

The leaves are out and the tulips are about to bloom here in Brampton. Summer will soon be upon us.


This month I launched my first "on-demand" webinar which you can take any time and anywhere. The first one entitled "Excel Best Unknown Features for Accountants" has been well received. It costs $39.95, plus tax, and you earn 2 CPDs. To register please "CLICK HERE."

My next webinar will deal with PivotTables, a powerful but little known tool in Excel. This webinar will be available the second week in June. To be notified when it is available please "CLICK HERE."

As usual, I would welcome your comments at
 [email protected]




Alan Salmon

Excel Tips

Excel1Defining a Constant

Summary: In many of your workbooks you use the same value in different cells. Excel provides a way to name that value or formula and then use it without typing the value or formula repeatedly. Here is how to do this:


To create a constant in Excel you need to type its value in the New Name dialog box. Here is how to create a constant that will enter the value of the HST for Ontario, which is 13%.

  1. Click on the Formula tab
  2. Click Define Name and the New Name dialog box will appear
  3. Type HST as the name for your constant
  4. Click on the Scope down arrow and select whether you want the constant to apply to the entire workbook, or just this worksheet.
  5. In the Refers To box type "=.13"
  6. Click OK

You have now created the constant, so it's time to use it

  1. Click in a cell
  2. Type an "=" sign followed by the first letter or letters of the name of the constant. In this case you would type an 'h"
  3. A menu will now appear with a list of names
  4. Double click on HST
  5. Press Enter
  6. The value .13 will now appear in the cell.
Constants are a great way to save time when you are entering the same information in different cells in a workbook.

Excel2Moving Around the Corners of a Range           

Summary: There are times when you need to quickly move to a corner of a selected range. This is very easy to do and here is how to do it: 


Here are the steps:
  1. Select the range
  2. Hold down the Ctrl key and press the period key.
  3. Each time you press "Ctrl +." Excel moves you to the next corner of the range
  4. You can also move from the top left corner of the selection to the bottom right corner by pressing Shift+Tab

To move back to the top left corner you press the Tab key.




Summary: Excel has certain default settings that can be changed to meet your specific requirements. Here is how to do this:


The default font in Excel is Calibra, 11 point. If you would prefer to work with a different font you need to change the default font and font size settings in Excel. Here are the steps to do this:

  1. Open the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 and Excel 2013 click on the File tab of the ribbon and then click on Options.)
  2. Click on Popular (Excel 2007) or General (Excel 2010 and Excel 2013)
  3. Change the Use this Font drop-down list (Excel 2007 and Excel 2010) or the Use This As the Default Font drop-down list (Excel 2013) and select the font you want to use
  4. Click on the Font Size drop-down list to pick the size of font you want Excel to use
  5. Click OK.
  6. Restart Excel.  

Once you do this Excel will use these settings as the default. Note that changing your default font and font size will not change the settings in any of your existing workbooks. 



Word Tip


WordChanging the Default Start-Up Directory 


Summary: The default directory where you save your files is usually your "My Documents" folder. If you would like your Word documents to be always saved in a different directory, here is how to do this:  

  1. Open the Word Options dialog box. (In Word 2007 click on the Office button and then click Word Options. In Word 2010 and Word 2013 click on the File tab on the ribbon and then click on Options)
  2. Click on the Save option at the left side of the dialog box click Save
  3. To the right of the Default File Location field (Word 2007) or the Default Local File Location field (in Word 2010 and Word 2013), click on the Browse button
  4. Select the directory you want as the start-up directory for your documents
  5. After you have selected that directory click on the OK button
  6. Close the Word Options dialog box by clicking on the OK button
  7. Exit and restart Word

Now that directory will be be the default folder when you open or save a file.



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 Inc. 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.