K2E Canada Office Tips June 2019


Excel Tips

Using AutoFilter to Analyze Your Data

Summary:  Analyzing data is easy with Excel's AutoFilter Here is how to use it:

  1. If AutoFiltering is not already turned on, display the Data tab of the Ribbon and, with a cell selected in your data, click the Filter tool. 
  2. 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.)
  3. 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 and click OK.

You can use the Custom AutoFilter dialog box to set any combination of criteria that you need. For example, you can tell Excel 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 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 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 when you are analyzing large sets of data.

Automatically Sum a Column or Row from the Keyboard

Summary: There are many different ways to sum a column or a row. Here is how to quickly do it from the keyboard:
 
  1. Select the first empty cell at the bottom or to the right of the numbers you want to add.
  2. Select ALT + "=".
  3. Press the Enter key.

Word Tip

Inserting Captions

Summary: You can add captions to screenshots, pictures, charts, text boxes and other graphic objects. Word's captioning features include preset captions for Figures, Equations, or Tables and also handles the caption numbering for you, such as Figure 1, Figure 2, etc. Here is how to do it:
  1. Right-click on the figure that you want to add a caption for.
  2. Click Insert Caption.
  3. The Caption dialog box will open.
  4. Click on the Label drop-down arrow and select a Caption label.
  5. Click on the Position drop-down arrow and select a position.
  6. Click OK and Word will insert the Caption.
Vertical Divider
We hope that you are enjoying our monthly newsletter featuring tips that will help you to be more effective using Microsoft Office products.

More Ways to
Earn Verified CPD


Access the many courses from the leader in providing CPD to professionals throughout North America.
Catalogue of upcoming webinars

Video Tip

Using Filters in Tables

Summary:  Struggling with displaying financial information by quarters with a non-calendar year end?

Here’s the video tip from the webinar K2’s Road to Excellence with Excel – Part 9 Excel PivotTables, Part 2 and Sage that shows how to do this.