K2 Office Tips Header
 Volume 6, Number 7  July 1, 2011
ExceltopIn This Issue 
Excel Tips

Quick Link
 Technology Tips Video
WordtopIn This Issue 
Word Tips


OutlooktopIn This Issue
 Outlook Tip

Quick Links


Previous versions of Office Tips



Follow me on Twitter





Summer has arrived, but I am still working for another couple of weeks. Then Nancy and I will enjoy some time at our cottage north of Toronto. We had a great time on the Rocky Mountaineer train from Calgary to Vancouver through the Rocky Mountains in late June. It was a wonderful experience.


My new eBook project launched in June and has been a great success. It launched with 5 eBooks. Four of the books cover my favorite Excel tips, with each book covering 50 tips. My 5th book focuses on Excel Pivot Tables and the 90 pages will provide you with a detailed road map on how to use this great tool for analyzing data. In August I hope to do a sequel to the PivotTable book.


Each eBook sells for $34.95 and in addition to the eBook and the teaching files, you get 2 CPD credits.


 Click here to go to our new web store.


As usual I would welcome your comments at [email protected].


Alan Salmon

Excel Tips


Summary: Excel 2007 added a number of new graphical formatting effects, such as shadows, glow, and reflection, for example. Sometimes it is useful to use one of these new features to add a frame around a group of cells .


In the example below, the screen shot shows a range of cells with a transparent rectangular shape superimposed on top of the range.  


Adding a Frame 

To insert the frame, highlight the framing area and click on Insert | Shape and select the shape you want. Then use the handles at each corner of the selected area to position the frame correctly.


Summary:  When you have a list of names with titles, it is often useful to be able to strip out the titles and just leave the names in the list. Here is how to do this: 

 You can use the following formula to remove three common titles (Mr., Mrs. and Ms.) from a list of names. For example, if cell A1 contains Mr. Dan Uitti, the formula below will return Dan Uitti.     


=IF(OR(LEFT(A1,2)="Mr",LEFT(A1,3)="Mrs",LEFT(A1,2)="Ms"),RIGHT(A1,LEN(A1)-FIND(" ",A1)),A1) 

This formula appears to be complicated and it is. It is using an IF, OR, and FIND statement to eliminate the title. I will not attempt, in this tip, to explain the logic behind the functions. The good news is it works. 

Summary:  Excel 2007 introduced the Mini Toolbar. This popup toolbar appears whenever you right click on an object, such as a range of cells, a chart legend, or a shape. The icons in the Mini Toolbar make it easier and faster to apply formatting to the selected area.
The example below shows the Mini Toolbar in action. 
The Mini Toolbar
The Mini Toolbar
The icons displayed on the Mini Toolbar vary, depending on the element that is selected. Using the Mini Toolbar is particularly useful if you need to apply formatting and the Home tab is not selected.

Back to top 


Word Tips

Word1Controlling Paragraph Spacing 


Summary: Word adds extra space below every paragraph. You can control the spacing to suit your particular needs. 


For example, if you are typing a report, you might want normal spacing between the paragraphs. To do this, you can specify how much spacing to include before and after each paragraph. Here is how to do this:

  1. Select the paragraph or paragraphs you want to edit.
  2. Click the Page Layout tab.
  3. Click the Spacing After spinner arrows to select the measurement you want to assign.
  4. To set spacing before a paragraph, click the Spacing Before spinner arrows to set a spacing measurement.
  5. Word applies the new spacing to the paragraph.
Note that Word 2010 adds extra spacing between lines when you press the Enter key.By default the spacing is 1.5. In previous versions this setting was 1.0. To assign single line spacing in Word 2010, click the Home tab, click the Line and Paragraph Spacing butting and click 1.0.


 Back to top


Word2Applying Styles


Summary: Styles are a great way to apply a number of formatting attributes to a document. Using styles will save time and apply a uniform look and feel to a document.

A great place to use Styles is in your headings. Rather than reapplying formatting each time a heading is used in your document, you can assign a style with a single click. Styles can be used to format body text, captions and more. The steps to assign a style are as follows
  1. Select the text to which you want to apply a style.
  2. Click the Home tab of the ribbon.
  3. Use the scroll arrows to scroll through the list of available styles.
  4. Click a style from the Styles group.
  5. Word applies the style to the text.
Applying Styles

To remove a style, select text and click the More button and then the click Clear Formatting button in the Font group on the Home tab.

Back to top


Outlook Tip

Outlook2Arranging Messages in Different Ways  


Summary: As the number of messages in your inbox increases, it can be a real challenge to keep track of them. Outlook lets you arrange, group, and sort messages in a number of different ways to help you quickly decide which are the most important, which can be deleted, and find e-mails that need an immediate response. 

You can view and enter separate addresses in Outlook with another character, other than a semi-colon; Outlook treats the address as one address and displays an error message.  

To change the arrangement message click on the Arrange By bar at the top of the message list and then click an option in the Arrange By list.

Arranging Messages in Different Ways
Arranging Messages in Different Ways

 By default, Outlook displays messages arranged by date, from newest to older. Other options for arranging messages include the following: 

  • Account  Messages are grouped by the e-mail account to which they were sent.
  • Attachments  Messages are grouped by whether they have attachments and then by date received.
  • Categories  Messages are arranged by the category you assign them to. Messages without a category appear first. Messages assigned to multiple categories appear in each category group.
  • Flag: Start Date or Due Date  Unflagged messages appear first. Messages that have been added to your task list with specific start or due dates are grouped by date.
  • From  Messages appear in alphabetical order by the message sender's display name.
  • Importance  Messages are grouped by priority.
  • To  Messages are grouped alphabetically by the primary recipient's e-mail address.
  • Size  Messages are grouped by the size of the message, including any attachments. 
  • Subject  Messages are arranged alphabetically by their subjects and then by date.
  • Type Items in your inbox, or other folder, are grouped by the type of item. (messages, encrypted messages, message receipts, meeting requests, and meeting request responses.
After arranging the items in your message list, you can change the sort order of the arrangement by clicking the sort order indicator that appears on the right side of the message header.  

Back to top


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

[email protected]  with "Subscribe to newsletter" in the Subject. 
Your information is always kept secure and never shared
Alan Salmon
President & CEO
K2 Enterprises Canada