K2 Office Tips Header
Volume 7, Number 9  

September, 2012
ExceltopIn This Issue 
Excel Tips 

Quick Link
 Technology Tips Video
WordtopIn This Issue 
Word 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 now behind us and it was a very pleasant one this year. Nancy and I had a very enjoyable August at our cottage on the Lake of Bays, north of Toronto. Although I was working on my next eBook, the environment was very pleasant. It is now time to head into our very busy fall season.


I am pleased to announce the release of my latest eBook, entitled Microsoft Word - Part 1. This one hundred and thirty page book is chock full of tips that will help you create great documents in Word. Like my other seven eBook, it sells for $34.95 and gets you 2 CPDs. 


The web store link is http://www.mcssl.com/store/alanswebstore


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



Alan Salmon

Excel Tips

Excel1Sudden Increases in File Size  

Summary: There are times when suddenly the size of an Excel workbook will jump in size. Here are some reasons why this may happen .  


Sometimes the size of an Excel workbook will increase in size for no obvious reason. This of course is highly irritating. Why would this happen? There are a number of reasons why this can happen. You may have added some additional objects to the workbook that were not there before. An example would be adding graphic objects or SmartArt.


It could also be due to adding a PivotTable or changing how the PivotTable is saved within the workbook. You should also check to see where the last cell in each worksheet is located (press Ctrl+End ) on each worksheet). If the lower-right corner of the worksheet seems a way out of range, you should delete whatever columns or rows are required to get the worksheet size back where it should be. You should also check to see if the workbook is saved with sharing turned on. With sharing on the size of the file can sometimes increase dramatically.


Finally, if you changed the format used to save the workbook, then the file size can increase. Check to make sure that you are using the proper file format for the version of Excel you are using. It is also wise to use Save As to save the workbook into a new file, with the proper format. That way Excel will write the workbook file "from scratch" and you will have the best chance of creating the most compact file possible.


Excel2AMaking Sure that Zero Values do not Show up in a PivotTable        

Summary: When There are times when you do not want zeros to show up in a PivotTable. Here is how to do that: 


Here are the steps to prevent zero values from showing up in a PivotTable.

  1. Select the cell to the right of the last heading in the PivotTable.
  2. Select the Data tab.
  3. Select Sort and Filter.
  4. Select Filter
  5. Select the drop down box from the Value heading.
  6. Deselect 0.
  7. Click on OK.
Zero values  will now not be displayed in the PivotTable. 


Back to top


Excel3Joinlng Dates and Text 
 You cannot normally join text and a date in a single cell because the formats are different. Here is how to do this: 
Dates in Excel are stored as serial numbers. When you type in a date such as September 2, 2012  Excel stores that date as the number 41154.  So if you type in "Today is September 2, 2012" you would get "Today is 41154."
To get the actual date you need to wrap the TEXT function around the date. For example to type in today's date with the text you would enter =TEXT(TODAY(), "mm/dd/yyyy"). The TEXT function converts a value to text and then displays it as month-day-year.


Word Tips


Word1Capturing a Picture of Your Screen              

Summary: There are times when it can be useful to insert a picture of your screen into a documents. This is now an easy task if you are running Word 2010. Here is how to do this: 
Word 2010 now has the ability to capture a screen picture of your screen. You can then insert the image into a document,  PowerPoint slide, or an Excel worksheet. The screen clipping feature takes a picture of the currently opened program window. If you have several open at the same time, you can select which one to use. You can also select just part of the window you want to capture
  1. In your document click where you want to insert a screen clipping.
  2. Click on the Insert tab.
  3. Click on Screen shot in the Illustrations group.
  4. Click a screenshot from among the open applications.

Word adds the screen clipping to your document.


 PowerPoint Tip


PowerPointarticleBackground Styles  

Summary: Background styles are used along with themes and effects to make your presentation look more professional. Here is how to change your background styles: 


A background style is created from a combination of theme colors and the currently selected document theme. To add a background style do the following:

  1. Click on the Design tab.
  2. In the Background group click on the Styles button.
  3. From the list of available styles select the one you want to use.
  4. When you have made your selection right click on the background you want to select.
  5. Click Apply to Select Slides if you have selected an individual slide to change or click on Apply to All Slides to have the new background applied to your entire presentation.

You can also customize a background style by clicking on the Format Background button and then changing the options to suit the background you want.



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