K2 Office Tips Header
Volume 12, Number 8 

September , 2017
We are now in September and the summer has been a mixed bag of weather. Some rain and some sun.
This promises to be a busy fall. I will be doing a keynote and one of my Excel Magic sessions at the Institute of Professional Bookkeepers of Canada annual conference in late September in Toronto.
October is dedicated to the annual Accounting Technology Seminar Series. This  is the 25th year that we have traveled from coast to coast presenting this one-day update on the technology that accountants, bookkeepers and their clients use. Because it is our 25th anniversary, we will be celebrating. Each attendee will receive the 25th Anniversary webinar edition of my favorite Excel Tips. Many cities sell out quickly so CLICK HERE to register and hold your place.
Our monthly K2's Magic with Office Series mini-webinars have been a great success. On September 14th I will start a two part series on Excel Macros. Softrak will present Part 4 of their series on "Stepping Up from Sage 50 and QuickBooks". On September 26th I will start my Outlook Magic series with great tips to save time with your e-mail. VohCom will show their Billing/Invoice module. Every registrant will receive the recorded webinar so sign up today to ensure that you're on the list.     
September 14, 2017
12:00PM - 1:00PM EDT
September 26, 2017
12:00PM - 1:00PM EDT  
As usual, I would welcome your comments at   alan@k2e.ca

Alan Salmo n FCPB

Excel Tips

ExcelTopDecMoving Cells Using the Mouse
Summary: It is easy to move cells with your mouse from one section of a workbook to another. Here is how to do this:
Highlight the range of cells you want to move.
  1. Place your mouse pointer over the heavy border that surrounds the selected range. Your mouse pointer should turn into an arrow.
  2. Click and drag the range to the new location. As you are moving your mouse, the outline of the range moves with it.
  3. When you reach the new location, release the mouse pointer. The cells are then moved.
If there is data at the target location, Excel will ask you if you want to overwrite the existing cells. That decision is up to you.
If this doesn't work and there is no heavy border around your selected range, then drag-and-drop editing is probably turned off on your system. To check this, do the following steps:
  1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In later versions of Excel display the File tab of the ribbon and click Options.)
  2. Click the Advanced option at the left of the dialog box.
  3. In the Editing Options area, make sure the Enable Fill Handle and Cell Drag-and-Drop check box is checked.
  4. If you want to be warned before overwriting information during an edit, make sure the Alert Before Overwriting Cells check box is selected.
  5. Click on OK.
Summary: If you have mistakenly closed a file while working on it, or lost due to a power failure, you'll know how frustrating it can be. Fortunately, changes were made in Microsoft® Excel® 2010, 2013 and 2016 which make the process to recover an unsaved workbook much simpler. Here is how to do this:
In Excel 2007, 2010 and 2013 you need to have the following options enabled:
  • Save auto recover information
  • Keep the last autosaved version if I close without saving
To enable the Save auto recover information options:
  • Select File
  • Select Options
  • Select Save and tick the Save AutoRecover boxes 
Select the File menu tab and Info option.
  • On the info page, click on the Manage Versions button
  • Select Recover Unsaved Workbooks
  • Select the respective file and click Open 
The unsaved version of the workbook will be opened, and no data will be lost.
If you have the latest version of Excel 2016 Excel AutoSaves every couple of seconds so your worries are over. The Manage Version is now Manage Workbook.
Summary: There are times when you want to insert a row number into column A in your worksheet. The column should reflect the correct number of the row, even when you add or delete rows. There are a number of formulas you can use in column A that will return a row number.
The easiest to use is the ROW function:
This formula returns the row number of the cell in which the formula appears. If you want to account for headers in rows 1 and 2 and you want cell A3 to return a row value of 1, then you can modify the formula to reflect the desired adjustment:
Another option is to use a formula that actually examines the contents of the adjacent column (B) and return a row number only if there is something in that adjacent cell.
This formula in cell A1, examines the contents of cell B1. If there is something there, then the COUNTA function is used to count the number of occupied cells between cell B1 and whatever cell is to the right of where this formula is placed. The formula also places a period after the row number that is returned. Be sure the dollar signs are included, as shown, and then copy the formula down as many cells as necessary to create your row numbers.
The advantage of a formula such as this one is that it checks to see if something is in column B before it returns a row number. This means that you can copy the formula down beyond the actual end of your data rows, and only those rows that have data (triggered by something in column B) will have a row number. The same sort of technique could be used with the ROW function instead of the COUNTA function: 


Word Tip


WordTipMoving Section Breaks
Summary: There will be times in Word when you will want to move a section. Here is how to do this:
Section breaks in Word are treated the same as any other character. If you are using Draft view, section breaks appear as a thin double line from one side of your document to the other. If you are not working in Draft view, you may want to consider changing to Draft view, as editing special characters (such as the section break) is easier in that view.
To move a section break, follow these steps:
  1. Select the section break just as you would select any other text
  2. Press Ctrl+X. The section break is cut from your document
  3. Position the insertion point where you want to insert the section break
  4. Press Ctrl+V. The section break is inserted in your document
If you are working in Print Layout view, then section breaks are not normally visible. You can make them visible (along with all the other non-printing characters) by pressing  Ctrl+*  (that's the asterisk). This is the same as displaying the Home tab of the ribbon and clicking the Show/Hide tool in the Paragraph group. (The Show/Hide tool looks like a backwards P).  You can then move the section breaks by following the four steps above.
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
K2E Canada Inc.