K2 Office Tips Header
Volume 8, Number 7  

July, 2013

From a weird spring we move to a weird summer. Hot sunny days, followed by colossal rain and thunder storms. Hopefully the weather will get back to normal for remainder of July and August.


Last month I launched the first of a series of Technology Webinars. The first one, Alan's Excel Tips got great reviews. They will be offered once a month and for 90 minutes I will share with you my favorite Excel Tips, new programs of interest to accountants and other useful technology information.


These webinars will be highly affordable at $29.95, including HST and will qualify for 1.5 CPDs. The July seminar will focus on the little known Excel Tables tool and in August I will introduce you to PivotTables, a powerful analysis and reporting tool in Excel. For more information and to register please


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




Alan Salmon

Excel Tips

Excel2AHandling Large Blocks of Text 

Summary: Excel is great with numbers but it can be challenging when you are dealing with a large block of text.  Here is how to use a text box to solve this problem:   


If you are dealing with long  passages of text, you can insert them into a text box in Excel

  1. Click on the Insert Tab.
  2. Click on the Text Box icon.
  3. Draw a rectangle in your worksheet about the size and shape that you want the text to fill.
  4. Paste or type the text into the text box.
  5. Select the text in the text box and use the Mini Toolbar to format the font size to fit the text box.
  6. Right click on the text box and choose Format Shape. On the Line Colour category, select No Line.
  7. In the Text Box category you can also define the number of columns that you would like in the text box.
  8. Click on OK


Excel2AUsing Show/Hide Formulas               

Summary: It is useful, at times, to identify the cells in a worksheet that contain formulas. Here is how to do this:


When you are auditing a worksheet it helps to identify the cells that contain formulas. There is an easy way to do this.

  1. Click on the Formula Tab.
  2. Select the Show Formulas icon.

All of the columns are made a little wider, and you see the formula instead of the result. 



Excel3Sorting Data on a Protected Worksheet
Summary: When you protect a worksheet you are prevented from doing many basic operations in Excel such as sorting. If you need to sort a protected sheet you need to change the protection options.   

When you protect a worksheet, Excel stops users from performing a wide variety of tasks on the data in the worksheet. One of the things that the user can no longer do is to sort data. If you want a user to be able to sort data, but still have the sheet protected, here is how to do this:

  1. Click on the Review Tab in the ribbon.
  2. Click on Protect Sheet in the Changes Group
  3. Scroll down the dialog box and select Sort.

If you select this option, then users can sort protected data.




Word Tip


Word1Backing up the Quick Access Toolbar            

Summary: If you have spent time customizing the Quick Access toolbar you should back up the QAT file that contains your customization. Here's how to do this:  

If you've spent a great deal of time customising the Quick Access Toolbar (QAT), it's all too easy to forget about it if you ever need to reinstall Windows. It is easy to create a backup of the toolbar, which will allow you to reinstall it or copy it to other machines that you work with.

In Windows XP, use Explorer to navigate to 'C:\Documents and Settings\[username]\Local Settings\Application Data\ Microsoft\Office', while in Windows Vista or 7or 8, you should head to 'C:\Users\[username]\ AppData\Local\Microsoft\ Office'.

In this folder you will find a file called 'Word.qat' - this can be duplicated for backup purposes, or copied to another computer.


Back to top 

 PowerPoint Tips


Summary: Presentations where the presenter reads slide after slide of text is hugely annoying to the audience. Here is how to stay out of "Text Jail". 

One of the most annoying feature of many presentations is when the presenter reads the text off of the slides. In this tip I want to show you how to get out of "Text Jail".


As soon as a text slide is shown on the screen, the eyes of the audience goes to the screen and they start to read all the text on the slide. During that time they are not listening to you. When they do return their attention to you, they expectt that you will cover everything on the slide in exactly the same order and to the same level of detail as they just read. As a presenter, you are thus trapped in "Text Jail" and you do only thing that will satisfy the audience's expectation - you read the slide.


If all you are doing is reading the slide, what you have is a report, not a presentation. It should just be e-mailed to everyone since people can read about twice as fast as we can speak. One good way to identify a report on the slide is that the points have periods at the end, indicating that this is really a series of sentences or paragraphs instead of key points a presenter will expand on. So what should you do instead?


Dave Paradi in his book Present It So They Get It talks about using the 3Rs to reduce the text and allow the presenter to explain the points to the audience one by one. Here is a brief summary of how you can apply the 3R's to an overloaded text slide. The first R stands for Rank. Rank the words or phrases in the text by importance to the audience. There are usually a few words or phrases that capture the essence of the point. Second, Reduce the text down to just the most important words or phrases, dramatically reducing the length of each point. Finally, Rephrase the selected words and phrases so that they make sense to the audience. Reducing the amount of text also allows you to increase the font size, making the text easier to read.


By having shorter, more meaningful points on the slide, you make it easier for the audience to understand the key point and then listen to you as you expand on it. They are doing less reading of the slide and paying more attention to you. It is also easier as a presenter because you can expand as much or as little as you want on each point, depending on the timing and the reaction of the prospect. 




 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 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