K2 Office Tips Header
Volume 9, Number 2  

February, 2014

February has been a good month for Nancy and me as we have just returned from ten days in Jamaica. It was great to feel the warm sun on our faces again.

 

For those of you who have attended one of our K2 seminars hosted by one of the accounting associations, have you thought about providing this training to your colleagues? This is a very affordable way to improve the Excel skills of the members of your team. 

 

We have a few openings this spring and more in the fall. If you would like additional information about our in house seminars, please contact me at the e-mail address below.

 

Coming in May is a new set of monthly Excel webinars.

 

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

 

Sincerely, 

 

Alan Salmon

Excel Tips

Excel1Extracting the Top Values in a Dataset

Summary: There are time when you need to extract the top nummber of values from a dataset. Here is the solution: 

 

Let's assume you have a three column data set in columns A, B, and C. Column C contains the balances. The data runs from A3 to A99. We want to extract the top five largest outstanding balances from this dataset. To do this, we use the LARGE function and Conditional Formatting which will highlight the data based on set criteria. To do this do the following: 

  1. Select cells A3 to C99
  2. Click on the Home Tab
  3. Select Conditional Formatting
  4. Select New Rule
  5. In the pop up window select 'Use a formula to determine which cells to format'
  6. Type in the following formula

              =$C3>=LARGE($C$3:$C$17,5) 

 

This formula is translated as follows:

  • $C3 is a mixed cell reference (only the column is fixed)
  • Various rows of data  can be selected  because  the row number is not fixed (absolute cell referencing)
  • $C$3:$C$99 refers to the data range for the balances
  • 5 implies that the top five balances will be highlighted 

6. Select OK.

7. The rows with the top five values will be highlighted




Excel2Combining Data from Several Cells         

Summary: There are times when you need to combine data from several cells. The =CONCATENATE function makes it easy to do this. Here's how:  


When you need to combine data from several cells the =CONCATENATE function can be used to combine the contents of several cells, or even to combine cell contents with other text. For example, if you wanted to combine the contents of cells A2 and B2, separate them by a space, and have the result appear in cell C2 you would enter the following formula in cell C2:

 

     =CONCATENATE(A2," ",B2) 

  

 

Summary: If you are working on a large worksheet  the data in the cells may be so small that it is difficult to read. Here is how to zoom the worksheet to make your data more readable.

 

To do this click on any cell in the worksheet.

  • Hold down the Control key
  • Move the wheel on your mouse forwards and backwords
  • Moving it forward will zoom the screen and make the data in the cells larger
  • Moving it towards you will zoom the screen and make the data in the cells smaller

 

Word Tip

 

WordDisplaying the Ruler             

Summary: The ruler appears at the top of each Word document window. It is highly useful when you need to adjust your formatting. There are times, however, when the ruler is not visible. When that happens here is how to get it back:  
 

Word allows you to control whether this ruler is displayed or not. If the ruler is not displaying do the following:

  1. Click on the View tab of the ribbon
  2. In the Show group click on the Ruler check box 

The ruler will now be visible

 

 
 PowerPoint Tips

 

Summary: Getting the slide structure rights is critical in developing a powerful presentation. Here's some easy to follows tips to ensure you get it right. 

  • Use 1-2 slides per minute of your presentation

  • Write in point form, not complete sentences

  • Include 4-5 points per slide

  • Avoid wordiness: use key words and phrases only

 Back to top 

 

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