K2 Office Tips Header
Volume 10, Number 11  

November, 2015

We are enjoying some amazing late fall weather here in Brampton, Ontario. No snow yet and warmer than usual. The last leaves fell from our trees this week. 


November and December is professional development time for the CPA's across Canada. K2E Canada Inc. will be doing 57 technology seminars for the Canadian CPA Associations in those months. Managing all of these events is always a challenge and it certainly keeps me busy.


In December, Nancy and I will be making a short trip to London, England to participate in the Institute of Certified Bookkeepers awards night, where I will receive a Luca award for my lifetime contribution to the bookkeeping profession in Canada.


Looking ahead to the new year, I will be launching a new series of e-books on advanced Excel. The first one will be titled "Excel's Best Unknown Features for Accountants and Bookkeepers". That will be be followed by a book on "Array Formulas".  My goal is to publish one a month.

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




Alan Salmon

Excel Tips

Summary: Analyzing data is easy with Excel's AutoFilter. Here is how to use it:
If AutoFiltering is not already turned on, display the Data tab of the Ribbon and click the Filter tool.
Use the drop-down arrow to the right of a column label to select Number Filters | Custom Filter or Text Filters | Custom Filter. (The names of the options, and thus the choices you make, depend on the composition of your data.) Excel displays the Custom AutoFilter dialog box. 
Use the controls in the dialog box to set the criteria you want used for filtering your list.

Click on OK.

You can use the Custom AutoFilter dialog box to set any combination of criteria that you need. For example, you can tell Excel that you want to see any values below, within, or above any given thresholds. The filtering criteria will also work with text values. For instance, you can tell Excel to display only records that are greater than CE. This means that anything beginning with AA through CE will not be displayed in the filtered list.

Excel also allows you to use wildcard characters to filter text values. These are the same wildcards that you can use in other programs. The question mark matches any single character, and the asterisk matches any number of characters. If you wanted to only display records that have the letter C in the second character position, you would use the equal sign operator (=) and a value of ?C*. This means the first character can be anything, the second character must be a C, and the rest can be anything.

AutoFilter is a highly useful tool for when you are analyzing large sets of data.

Back to top
Excel2AA Fast Way to Delete Blank Rows or Rows with Blank Cells            
Summary: There is a fast way to delete blank rows or rows with blank cells. Here is how to do this:
On the Home tab, click Find & Select, Go To Special
  1. Select Blanks
  2. Click OK.
  3. Excel selects the blank cells.
  4. On the Home tab, click Delete, Delete Sheet Rows
The blank rows and the rows with blank cells will now all be deleted.
Back to top

Summary: There are times when you need to apply a background color to one or more cells in a worksheet. Here is how to do this: 

The Fill Color tool (available on the Home tab of the ribbon) has a small bucket and color sample on it. This tool actually has two parts; if you click on the left part (the part with the small bucket and color sample), then the color shown in the sample is applied to the cells you have selected. Note that the color of the font doesn't change, only the color of the cell background.

If you click on the arrow at the right side of the tool, a color palette appears. To select a color, click your mouse on one of the small color squares. This is applied to the cells you have selected and appears in the color sample on the Fill Color tool.

There is another way to change the cell color: 
  1. Select the cells whose color you want to change.
  2. Press Ctrl+Shift+F.
  3. Excel displays the Format Cells dialog box.
  4. Make sure the Fill tab is selected.
  5. Using the color palette in the dialog box, select the cell color.
  6. Click on OK.
Both methods get the same result. The choice of which one to use is a personal preference.  


Word Tip


WordTip2Editing Headers and Footers          
Summary: You can easily edit a Header or Footer in Excel by using the tools in the Header and Footer group in the Insert tab of the Ribbon. Here is how to do this:
After you have created a header or footer, the easiest and quickest way to edit it is to make sure you are in Print Layout view and double-click in the header or footer area.

To return to the document editing mode when you are done making changes to the header or footer, double-click anywhere in the normal document area, or click the Close Header and Footer button on the Design Tab of the Ribbon.

Note that this tab is visible only when you are working in the header or footer.  
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

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