K2 Office Tips Header
Volume 11, Number 7 

July, 2016
It is summer time in Brampton and we are suffering through the highest temperatures in history. We have spent a lot of time hunkered down in our air conditioned home. Many days it was simply too hot to go out on our deck.
July has been a planning month for me. We are well along with plans for an outstanding Accounting Technology series this fall which will visit 14 cities from coast to coast. Registrations are closing in on 700 and some of the cities will sell out next month. To make sure you get a seat go to www.k2e.ca to register.
September will see two new ventures. The first one is 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.
The second is a series of free mini seminars. The first one focuses on the move away from paper. If you are an accountant, bookkeeper or tax professional you will have the opportunity to spend an hour with my partner Ward Blatch CPA and Don Emory CPA, who is the Director of Client Solutions at Doc-It while they cover "Working Smarter with Document Management and Workflow". This webinar will qualify for 1 CPD credit. To register for this free webinar on September 13st, 2016 at 3:00 pm EDT please go to:  https://attendee.gotowebinar.com/register/5734322950875004164

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

Alan Salmon

Excel Tips

ExcelTopActivating the Formula Bar With the Keyboard
Summary: Sometimes it is Useful to Activate the Formula Bar From the Keyboard. Here is how to do this:  
The Formula bar, which is located just above the work area in Excel, shows the formula or value in the currently selected cell. You can click on the formula bar to edit information within a cell. What happens if you want to activate the Formula bar by using the keyboard? 
The easiest way to do this is simply to press the F2 function key. By doing this you are telling Excel that you want to edit the contents of a cell. When you press F2, it is quite likely that Excel will simply expand the information in the cell and allow you to edit the information directly in the cell itself. If you don't like this behavior, but instead want F2 to activate the Formula bar, follow these steps:
  1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 - 2016 display the File tab of the ribbon and then click Options.)
  2. Click the Advanced option at the left of the dialog box.
  3. Make sure the Allow Editing Directly In Cell check box is cleared.
  4. Click on OK.
Now, whenever you press F2, the Formula bar is selected and you can edit the formula there.
Excel2AFinding and Replacing Error Values
Summary: In a large complex worksheet with hundreds of rows of calculated values, it is almost guaranteed that there will be a number of cells with #N/A error that you would like to be "0" (or some other value), so that the cells can be used in other formulas. Here is how to do this: 
We will use the GoTo feature in Excel. Simply follow these steps:
  1. Press F5. Excel displays the Go To dialog box
  2. Click Special. Excel displays the Go To Special dialog box
  3. Make sure the Formulas radio button is selected
  4. The only check box that should be selected under Formulas is Errors 
  5. Click OK. Excel selects all cells where the formula returned an error value 
  6. Type 0 or whatever value you want.
  7. Press Ctrl+Enter.
Note that this approach results in any error values being replaced, not just those with the #N/A error.

Excel3Entering Dates in Excel
Dates are a special case in Excel. If you enter information that can be translated as a date (by any stretch of the imagination), then Excel treats it as a date. It converts your data into a serial number that is internally used to represent dates and times. For instance, any of the following entries will be translated to a date by Excel:
  • 10/14
  • 10-14/11
  • 10 Dec
  • December 10, 2016
If you enter the first example, Excel will convert it to a date and display that date as best it can determine. If you leave off the year in your entry, Excel assumes you mean the current year. You could also use dashes instead of slashes and Excel will still figure out you are entering a date. 
Regardless of how you type a date, it is converted to a special serial number by Excel and stored internally in that format. How you see the date on your screen is a result of how the cell is formatted. Even though Excel stores dates in a standard format internally, they can be displayed using any number of different formats.



Word Tip


WordTip2Deleting Tab Stops
Summary: To clear individual tabs in a paragraph, follow these steps: 
  1. Make sure the insertion point is in the paragraph whose tabs you wish to clear.
  2. Display the Tabs dialog box. (Display the Home tab of the ribbon)
  3. Click the small icon at the bottom-right of the Paragraph group
  4. Then click Tabs in the tab list below the Tab Stop Position box and select the tab stop you want to clear.
  5. Click on Clear.
  6. Repeat steps 3 and 4 for each tab stop you want to clear.
  7. Click on OK.
If you have the ruler displayed on-screen, then you can also delete tabs by following these steps:
  1. Make sure the insertion point is in the paragraph whose tabs you wish to clear.
  2. Use the horizontal scroll bar to scroll left or right until you can see the offending tab on the ruler.
  3. On the ruler, click on the tab indicator and drag it off the ruler. When you release the mouse button, the tab should disappear from the ruler.
  4. Repeat steps 2 and 3 for each tab stop you wish to clear. 

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