K2 Office Tips Header
Volume 7, Number 6  
June 1
, 2012
ExceltopIn This Issue 
Excel Tips
How to Display a Negative Percentage in Red

Quick Link
 Technology Tips Video
WordtopIn This Issue 
Word Tip
OutlooktopIn This Issue
 Outlook Tip

PPTTopIn This Issue
PowerPoint Tip
Quick Links


Previous versions of Office Tips





Like us on Facebook




Follow us on Twitter



View our profile on LinkedIn 












Summer is just weeks away and it will be nice to get away to our cottage for a few weeks. Before that I have a heavy teaching load, plus a week of work in Busan, Korea at the end of this month.


I am pleased with the response to my latest eBook, entitled Excel Charts - Part 1. This ninety page book is chock full of tips that will help you create great charts in Excel. Each of my seven eBook sells for $34.95 and gets you 2 CPDs.


I am in the final editing process of my next eBook which will be the first of three on Microsoft Word. The first one will be ready by the end of June.


The link to my Web Store is http://www.mcssl.com/store/alanswebstore


New for 2012 are our Facebook and LinkedIn pages. I am now posting a weekly tip and additional relevant accounting technology links on these pages. You can follow me on Facebook at 

www.facebook.com/K2Canada and on LinkedIn at www.linkedin.com/in/k2canada. I am continuing to update readers on the status of Office 15, which will be the next version of Excel, Word, etc.


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



Alan Salmon

Excel Tips

Excel1How to Display a Negative Percentage in Red 

Summary: It is not obvious how to display a negative percentage in red. Here is how to do this: 

  1. Select the cell or cells that may contain negative percentages.
  2. Click on the Home tab.
  3. Click on the Number Group.
  4. Click on the small icon in the lower left hand corner of the Number Group.
  5. Select Custom.
  6. In the Type Box enter the following:

    _(* 0.00%_);[RED]_(* (0.00%);_(* 0.00%_) 
  7.  Click OK.
The format you specified above will display positive percentages with two decimal places and will display negative percentages in red with brackets and two decimal places. Note that this uses the accounting format.


Excel2ACounting the Number of Blank Cells     

Summary: There are times when you want to count the number of blank cells in a range. Here is how to do this: 


  1. Select the range of cells where you want to count the blank cells.
  2. Enter the following formula:


This function returns an integer value showing the number of blank cells in the range. Note that if you have display of zero values turned off for the workbook a cell can appear blank, when it is really not blank and it will not be included in the count of blank cells.


Back to top


Excel3Doing a Case Sensitive Sort   
Summary:  Normally Excel doesn't pay attention to case when you are sorting text. If case is important you need to change a setting in the sort window. Here is how to do this.
  1. Click inside the data set that you want to sort.
  2. Define your sort levels.
  3. Click on the Options Tab in the top right hand corner of the sort window.
  4. Click on the Case Sensitive check box.
  5. Click on OK. 
  6. Click on OK. 
Back to top 


Word Tips


Word1Typing Over Text


Summary: Normally when you press a letter or a number key with Word in its default mode, the insertion point and any text to the right of it is pushed to the right. If you want Word to replace the text you need to change a setting. Here is how to do this: 
  1. Click on File.
  2. Click on Options.
  3. Click on Advanced.
  4. Click on Using the Insert Key to Control Overtype Mode.
  5. Click on Use Overtype mode.
  6. Click on OK.


Outlook Tip



Summary: You can create a rule to control when messages leave your system after you click send. Here is how to do this::

  1. Open Outlook.
  2. Click on the File Tab.
  3. Click on Rules and Alerts.
  4. Click New Rule.
  5. In the Rules Wizard, under Start From A Blank Rule, click Check Messages After Sending.
  6. Click Next.
  7. Click to select any desired conditions that limit which messages the rule applies to, and then click the link in the description panel, which may display a dialogue box to specify the exact criteria.
  8. Click OK.
  9. Click Next.
  10. Click on any exceptions, specify them in the description panel to specify the exact criteria.
  11. Click OK.
  12. Click Next.
  13. Under Select Actions, click Defer Delivery for a Number of Minutes.
  14. Type the total minutes up to 120 that you want messages delayed.
  15. Click OK.
  16. Click Next.
  17. Type a name for the rule.
  18. Click Finish. 

You will now be returned to the Rules and Alerts dialogue box, which will now show your next rule.


 PowerPoint Tip


PowerPointarticleFinding and Replacing Text in a PowerPoint Presentation


Summary: There are times when you may need to replace text in a PowerPoint presentation. Here is how to do this without editing each individual slide:


  1. On the Home Tab of the Ribbon click Find from the Editing Group.
  2. Enter the text in the Find box that you want PowerPoint to find.
  3. Click on the Find Next to find the first matching entry.
  4. If you want to also replace the entry click Replace.
  5. Enter the text in the Replace with Box and click Replace.
  6. Click on Next to move through all of the instances of the text you are looking for in the presentation.
Be careful if you use the Replace All button that you are certain that only the text you have selected will change. You do not want to change part of a word because it closely matches your criteria.


K2 Enterprises Canada 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 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
President & CEO
K2 Enterprises Canada