K2 Office Tips Header
Volume 9, Number 4  

April, 2014
ExceltopIn This Issue 
Excel Tips    
 Updating Data Without Using Formulas
Quick Links
Technology Tips Video
Quick Links  
Word Tip
Quick Links

 

Previous versions of Office Tips 

 

 

Like us on Facebook 

 

Follow us on Twitter 

 

   

View our profile on LinkedIn 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Spring is definitely here in Brampton and it is sure a welcome change from old man winter. I have been busy in April working on two projects.

 

The first is a new series of webinars with a difference. These will be "on-demand" sessions which you can take any time and anywhere. The first one is titled "Excel Best Unknown Features for Accountants". It will cost $39.95, plus tax, and you earn 2 CPDs. To be notified when it is available please "CLICK HERE."

  The second project is our annual Accounting Technology Tour. This is the 22nd year that we have gone coast to coast, visiting 13 cities, to provide public accountants, bookkeepers and consultants with a full day of what's new in the technology world for their clients and their practices. The cost for the day is $39.95, plus taxes and you will receive 8 CPDs.  Nine of the thirteen cities sold out last year so you need to register early. To register for the 2014 Accounting Technology seminar please "CLICK HERE." 

 

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

 

Sincerely, 

 

Alan Salmon

Excel Tips

Excel1Extracting the First Word in a String

Summary: There are times when you want to extract text from a cell. Here is how to extract the first word in a string: 

 

To extract the first word in a string, a formula must locate the position of the first space character and then use this information as an argument for the LEFT function. Assuming the text is in cell A1, the following formula does that:

 

     =LEFT(A1,FIND(" ",A1)-1)

This formula returns all the text before the first space in cell A1. However, the formula will return an error if the text in A1 contains no space characters, because it is a single word. Use the new IFERROR function to display the entire cell contents, if an error occurs. Here's the formula:

    =IFERROR(LEFT(A1,FIND(" ",A1)-1),A1)


Excel2Calculating a Remainder           

Summary: When you divide two numbers, if the result isn't a whole number you end up with a remainder. Sometimes you need to know what that remainder is. Here is how to do this:  

  

The solution is to use the MOD function which returns the remainder when you divide two numbers. For example, if you have 187 manuals to be divided among five offices, you would use this formula:
 
    =INT(187/5)
 
The answer is 37
 
Now if you want to calculate how many books will be left over, you would use this formula:
 
     =MOD(187,5)
 
The answer will be 2
 

 

 

Summary: There are times when you need to update a column of figures by the same amount. Here is the quick way to do this:
 

Let assume that you have a budget worksheet and you want to increase the selling price of a list of products by 5%.

  1. In an empty cell enter the value 1.05
  2. Press Ctrl + C to copy the value in the above cell
  3. Select the range to be updated
  4. Select the Home tab on the ribbon
  5. Click on the Paste icon
  6. Click on Paste Special
  7. In the Paste Special dialog box select Multiply
  8. Click OK
All of the values in the selected range will now be increased by 5%. Note there will be no formulas in the cells.  

  


 

Word Tip

 

WordDeleting Page Borders

           

Summary: There are times when you would like to delete the borders around a page. Here is how to do this:  

Sometimes when you have added a border around a paragraph in your document, you may later want to remove the border. Here are the steps to do this:

  1. Click inside the paragraph from which you want the border removed.
  2. Click on the Home tab of the ribbon.
  3. Click on the down-arrow at the right side of the Borders tool in the Paragraph group. 
  4. Word displays a series of options.

In the options box click on No Border.

Word then removes the border from the paragraph.

 

 

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

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.
647-722-4741