K2 Office Tips Header
Volume 9, Number 6  

June, 2014

Summer is finally here and Nancy and I are enjoying our garden from the deck almost every day. We just got back from a three day vacation at a cottage in the Muskoka Lakes area north of Toronto. We had a very enjoyable time.

 

Last month I launched my second "on-demand" webinar which you can take any time and anywhere. Both "Excel Best Unknown Features for Accountants" and "Excel PivotTables" have been well received. They cost $39.95 each, plus tax and you earn 2 CPDs. To register please "CLICK HERE."
  
My next webinar focuses on "Cloud vs. On-Premise - Risks vs. Rewards", which will help you to understand the pros and cons of moving to the cloud. This webinar will be available the second week in July. To be notified when it is available please "CLICK HERE."   
 

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

 

Sincerely, 

 

Alan Salmon

Excel Tips

Excel1Checking Your Formulas

Summary: When you create a nesting formula it is challenging to determine the accuracy of the numbers. Here is how to use the Evaluate Formulas dialog box to do this:

 

The Evaluate Formula dialog box steps you through the calculation, one expression at a time, so you can see the answer to each step. Here's how you do this:

  1. Click in the cell that contains the formula you want to check
  2. Click on the Formula Tab on the Ribbon
  3. Click Evaluate Formulas in the Formula Auditing Group
  4. When the Evaluate Formulas box appears click on Evaluate
  5. Excel will evaluate the first part of the expression
  6. Continue clicking to review each expression
  7. Click Step In to review the results of an expression
  8. Click Step Out to return to the expression
  9. When Excel reaches the end of the formula, it displays the results
  10. Click Restart to evaluate the formula again
  11. Click Close to close the dialog box
The Evaluate Formulas tool is a great way to debug a complex formula.

Article2Copying Styles           

Summary: Excel allows you to create custom styles, but that style is unique to the workbook it is created in. Many times you will want to use that style in another workbook. Here is how to copy a custom style from one workbook to another. 

  

To copy styles you need to open both the workbooks from which you want to copy the style and the workbook to which you want to apply it. Here are the steps: 
  1. Open the file that contains your custom style
  2. Open the workbook into which you want to merge your custom style
  3. Click on the Home Tab
  4. Click on Cell Styles
  5. When the Style Gallery appears click on Merge Styles
  6. When the Merge Styles dialog box appears, click on the workbook whose style you want to use
  7. Click OK

The copied styles are now available in the new workbook

 

 

Article3How to Maintain Data Integrity by Identifying Blank Cells in a Worksheet
 

Summary: To maintain data integrity in your worksheet  you can start by identifying cells that are blank.

 

This could be due to a data capturing error or because they have been left as place holders for vital information and forgotten. Identifying these blank cells gives you the opportunity to complete capturing and have an accurate data set, which is vital for data analysis.

 

Here is how to use Conditional Formatting to highlight blank cells in a worksheet.  

  1.  Highlight the data range you want to check
  2. Click on the Home Tab in the Ribbon
  3. Select Conditional Formatting
  4. Select New Rule
  5. Select Format Only Cells That Contain
  6. Click on the Down Arrow beside the Cell Value box
  7. Click on Blanks
  8. Click on the Format button
  9. Select the color of choice and click OK.
  10. Click OK to complete the process

Note that all the blank cells are now highlighted in blue. Thus, the missing cells can quickly be filled in with information and the data integrity of the worksheet maintained.

Back to top 

 

Word Tip

 

WordInserting One File Into Another

           

Summary: There are times when you want to insert another Word file into your document. Here is how to do this: 
   

To add a file to the document you are working on.  

  1. Position the insertion point where you want the file inserted
  2. Click on the Insert Tab on the Ribbon
  3. In the Text group, click the down-arrow to the right of the Object tool
  4. Select Text from the File option.
  5. Browse to the File you want to Insert
  6. Click on OK.

The file will now be inserted below you insertion point 

 

 

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