Microsoft

Office Q&A: Add custom AutoComplete items; evaluate groups in an Excel data set

Susan Harkins helps readers with simple Office solutions. This month, she shows how to reclaim custom AutoComplete items and how to evaluate groups using aggregate functions.

Office Q&A

This month, we'll review two simple solutions: how to add custom AutoComplete items and how to evaluate grouped data with multiple aggregate functions. The AutoComplete solution is simple, but you have to know it exists and how to update it to get the most out of it. Then, to evaluate grouped data using aggregates, we'll explore Excel's Subtotal and PivotTable features. I rely on these tools frequently, and there's more to both than some users realize.

I'll be using Office 2013 on Windows 7. You can download the example .xlsx or .xls file for your convenience. There's no example file for the Outlook solution.

Lost AutoComplete

Mike sends out a weekly email with daily availability items in the body of the message. Outlook was smart enough to replace mon with Monday, tues with Tuesday, and so on. After purchasing a new system with Windows 8, Outlook no longer replaced these shorter strings with the days of the week. It's not a huge problem, but Mike finds the feature helpful.

Mike's been using AutoComplete and it's in all of the Office apps. Mike's right—it's a convenient tool! Using this feature allows you to enter frequently used text or values by entering a shorter string. AutoCorrect has many items out of the box, but some are unique to the user. Fortunately for Mike, it's easy to add an item to AutoComplete. Here's how:

  1. Click the File tab, choose Options, and then click Mail in the left pane.
  2. Click Spelling and AutoCorrect.
  3. In the next window, click the AutoCorrect tab (if necessary).
  4. In the Replace control, enter the text you want to replace, such as mon. In the Replace with control, enter the full text string, Monday (Figure A).
    Figure A
    Figure A
  5. Click Add.

Mike needs to repeat this process to add each day to the AutoCorrect feature. If you're using Outlook 2007, choose Options from the tools menu, and then click the Mail Format tab. Next, click Editor Options, Proofing, and then select AutoCorrect Options to access this feature.

You can learn more about this useful feature by reading the following articles:

Multiple aggregates with groups

Andrew wants to use multiple aggregates in the same data set. An aggregate is a function that evaluates a group of values. For instance, you might want the average value of a grade column or the maximum value of a commission column. You probably use aggregates all the time. Andrew wants to combine aggregates with data groups. Excel's Subtotal feature can evaluate groups and groups within groups if you sort the data appropriately beforehand.

Let's use the data set in Figure B to demonstrate using Subtotal to evaluate different columns in the Transaction Date group. Then, we'll evaluate the Personnel group within the Transaction Date group. If that's clear as mud, don't worry—it'll make sense soon.

Figure B

Figure B

We'll sort and then use the Subtotal feature to evaluate groups.

The key to implementing this double group scenario is the initial sort. In this case, we'll sort by the Transaction Date field and then sort by the Personnel field within the Transaction Date field. To do so, click any cell in the Transaction Date column and do the following:

  1. Click Sort & Filter in the Editing group on the Home tab.
  2. Choose Custom Sort from the drop-down list.
  3. The resulting dialog will probably choose all of the right settings: Column is Transaction Date; Sort On is Values; and Order is Oldest to Newest.
  4. Now, set the second group by clicking Add Level.
  5. Choose Personnel from the Column drop-down. Excel will default to Values and A to Z.
  6. Make sure the My data has headers option is checked (Figure C), and click OK.
    Figure C
    Figure C

With the data grouped by the Transaction Date and the Personnel columns, you're ready to implement the Subtotal feature. First, let's evaluate the columns for the Transaction Date column as follows:

  1. Click any cell in the data set.
  2. On the Data tab, click Subtotal in the Outline group. In the resulting dialog, you'll define the different aggregate functions. In Excel 2003, choose Subtotals from the Data menu.
  3. To count the number of transactions per date, choose Transaction Date from the At each change in drop-down, choose Count from the Use function drop-down, check the Transaction Date field in the Add subtotal to list, and clear any others that might be checked (Figure D).
    Figure D
    Figure D
  4. Click OK to add the first subtotal row (Figure E), which counts the number of transactions per day.
    Figure E
    Figure E
  5. To add the total amount per day, click Subtotal, choose Transaction Date from the At each change in drop-down, choose Sum from the Use function drop-down, check the Amount field in the Add subtotal to the list, clear the Replace current subtotals option (Figure F), and click OK. Clearing the Replace current subtotals option allows Excel to add the aggregate rather than replacing the current aggregate.
    Figure F
    Figure F
  6. To add the average commission per day, click Subtotal, choose Transaction Date from the At each change in drop-down, choose Average from the Use function drop-down, check the Commission field in the Add subtotal to the list, clear the Replace current subtotals option, if necessary (Figure G), and click OK.
    Figure G
    Figure G

You probably noticed that each aggregate relies on the Transaction Date field—that's the group evaluated by each function. With the first group's subtotals in place, you can evaluate the Personnel group within each date group. For example, let's count the number of transactions for each employee per date, as follows:

  1. Click inside the Personnel column.
  2. Click Subtotal in the Outline group on the Data tab.
  3. In the resulting dialog, choose Personnel from the At each change drop-down (if necessary).
  4. Choose Count from the Use function drop-down.
  5. Check Personnel in the Add subtotal to list; clear all other fields.
  6. Uncheck the Replace current subtotals option (Figure H).
    Figure H
    Figure H
  7. Click OK to see the results (Figure I).
    Figure I
    Figure I
Use Subtotal to evaluate several different groups.

You can continue to use Subtotal to evaluate groups within groups, as long as the initial sort supports your choices. In addition, you can use the pane to the left to collapse and expand different views of the subtotaling and detail rows.

Another way to get similar results is to generate a PivotTable. Figure J shows the configuration and the resulting table. Remember that you must refresh the table if you modify the data set—this behavior makes the PivotTable less than ideal for some.

Figure J

Figure J

Use a PivotTable to get similar results.

To generate the PivotTable, click anywhere in the data set and then click PivotTable in the Tables group on the Insert tab. Use Figure J as a guide to drag fields to the appropriate areas in the PivotTable Fields pane. To change the aggregate for a field in the Values area, choose Value Field Settings from that field's drop-down list. To learn more about Excel's PivotTable feature, read "Make summarizing and reporting easy with Excel PivotTables."

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

Also see

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox