K2 Office Tips Header
Volume 10, Number 6  

June, 2015

Summer is officially here and Nancy and I are certainly enjoying our deck and garden. We took a two day break over the Father's Day weekend and stayed at Langdon Hall, which is a wonderful hotel in Cambridge, Ontario. Three of my daughters joined us for English tea and we had a great time together.

My focus this month is on the 2015 Benchmark Survey for the Institute of Professional Bookkeepers of Canada which will help to define the status of the bookkeeping industry. The study will measure the current attitudes and concerns of Canadian bookkeepers regarding ongoing education, professional certification and accreditation. Other demographic information such as income, contracting rates and general trends will also be measured.


If you are a bookkeeper or there are bookkeepers in your organization I would invite you/them to participate in the survey by clicking HERE. By the way we will drawing for an iWatch from all of the survey responders who provide a valid email address at the end. The survey closes at midnight July 10, 2015 so don't delay.  


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.

Excel2AVLOOKUP Trouble Shooting      

Summary: VLOOKUP formulas sometimes return an error or an incorrect answer. When that happens here are some trouble shooting tips.

  • The entire cell contents must match 100%.
  • Be careful of extra spaces before and after a word.
  • If you are matching numbers, make sure that both the lookup value and the matching value are formatted with the same type.
  • Sometimes when you are importing data, numbers get formatted as text. This will also return an error message. 
Back to top


Summary: There are times when you need to add just the visible cells in an Excel worksheet. Here is how to do this:  


The Sum function in Excel adds all of the cells in a column or row, even if the columns and rows are hidden. The solution to just adding the visible cells is to use the =SUBTOTAL function.


 Assume that you have a column of numbers in column A from A1:A100 and that some of the cells are hidden.


To get the total for just the visible cells you would use the following formula:




The formula only calculates the sum of the visible cells in the range.


Word Tip


Word1Displaying a Single Page             

Summary: Many times Word displays two pages of a document side by side, but you only want to display one page the full width of the screen. Here is how to do this:    

As you adjust the zoom settings used by Word, Word automatically displays multiple pages of a document at the same time. This is particularly true if you are using a widescreen monitor at a high resolution. As the zoom factor approaches 50%, Word automatically switches to display two pages.


There are different ways to get you back to a single page. A quick fix to display just a single page is to simply "zoom out" a bit by holding down the Ctrl key as you move the scroll wheel on your mouse. As you zoom in and out Word discovers that it can no longer display two pages on the screen, so it switches automatically to show only a single page.


Another approach is to choose Zoom from the View menu in order to display the Zoom dialog box. Select the Page Width button and click OK. Word calculates the proper zoom percentage to show a single page at the width available on your screen. If you prefer, you can click one of the other single-page options-Whole Page or Text Width. 


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.