K2 Office Tips Header
Volume 12, Number 4 
May , 2017
ExceltopIn This Issue 
Excel Tips   
Maintaining Data Integrity in Your Worksheet By Identifying Blank Cells

Rapid AutoFill
WordtopIn This Issue 
Word Tip  
Quick Link
 Technology Tips Video  
Using Excel's Flash Fill to Convert Numbers to Text
Quick Links

   

  

  

      

 Like us on Facebook 

  

  

  

Follow us on Twitter  

  

  

  

View our profile on LinkedIn 

  

  

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

 

 

  

  

   

  

  

  

  

  

  

  

  

  

  

  

   

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

    

  

 

 

   

  

  

Spring has finally arrived here in Brampton, ON. Nancy and I had our first coffee break on our deck last weekend. 

Registration for the 2017 Accounting Technology seminar is now open. This is the 25th year that we have traveled from coast to 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 our quickly so CLICK HERE to register and hold your place.

Our monthly Excel Magic mini-webinars have been a great success. The next two sessions will be in May in conjunction with CCH and Doc-It. The live webinars are sold out but you can still sign up to receive the recordings. 
 
 
  
 
As usual, I would welcome your comments at   alan@k2e.ca

Sincerely,  

Alan Salmo n

Excel Tips
ExcelTopDec Maintaining 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  t o 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. Click on the Fill tab
  10. Select the color of choice and click OK.
  11. 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.
Summary:  The AutoFill function is a great tool to use when you are doing certain types of editing. Here is an example to illustrate how useful it can be. 
 
Let's assume we have a column full of values in column A running from A3 through A417. We want to fill the adjacent cells in column B with a numerical sequence of values running from 1 through 417. Use the following steps to accomplish this task:
  1. In cell B3, place the value 1. 
  2. In cell B4, place the value 2.
  3. Select the range B3:B4.
Double-click on the AutoFill handle. (The black square in the bottom right corner of cell B3)
 
  Back to top  
 
Summary: Instead of using the mouse to select a range of cells, here is a quicker way of getting the job done:    
 
You can use the mouse to select a range of cells by holding down the left mouse button and dragging the mouse to make a selection. There is an even quicker way to select cells using the mouse. Here is how to do this:
  1. Select the cell that marks one corner of the range of cells you want to select.
  2. Move the mouse pointer so it points to the cell at the opposite diagonal corner of the range you want selected.
  3. Hold down the Shift key as you click once on the left mouse button.
That will select all the cells in the range.  
  

 

Word Tip

 
WordDecDeleting Tab Stops
Summary: There is no keyboard shortcut to remove tab stops. You must use the Tabs dialog box to remove them. Here is how to do this: 
  • Press Ctrl+A. This action selects the entire current document.
  • Click on the Home tab of the Ribbon.
  • Click on the small icon at the bottom-right of the Paragraph group and Word will display the Paragraph dialog box.
  • Click on the Tabs button at the bottom-left of the dialog box and Word will display the Tabs dialog box. 
  • Click the Clear All button.
  • Click OK.
All of your tabs in the document will be deleted.

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
Managing Director
K2E Canada Inc.
647-722-4741