Microsoft Excel: Become an Excel groupie

Q: I use Excel’s Group tool to expand and collapse our employees’ weekly timesheet data, but it takes forever to group each section individually for each of my 300-plus employees. Can you tell me how to write a macro that groups the weekly data for me or provide a better suggestion for grouping data?

A: A macro solution would probably be difficult to construct because the number of time data entries for each employee differs from one week to the next. Fortunately, I can offer two alternative methods that I think will meet your needs by using Excel’s Subtotal or PivotTable tools, as follows.

Subtotal approach. To summarize and group your weekly employee time data using the Subtotal approach, you can create groups (or outline levels), as follows.

1. Make sure your data have unique column headings for each column of data.

2. Make sure your data range contains no blank rows or columns (but blank cells are OK).

3. Select any single cell in your data range, or select the entire data range by selecting a single cell in the data range and pressing Ctrl+A.

4. From the Data tab’s Outline group, select Subtotal. In the resulting Subtotal dialog box, select options to Sum data for each employee for each weekday and the total column, then click OK.

5. As a result, Excel not only subtotals all data by employee, but it also creates groups and inserts outline levels automatically for your entire data range, as pictured below.

6. Once your data are subtotaled and grouped in this manner, you can expand and collapse (show or hide) detailed line items by clicking the corresponding plus and minus signs in the left margin. (In the example pictured above, I collapsed the data to display subtotals for all the example employees except Becky Hendrix. I have also applied the Classic 3 format using Excel 2003’s older AutoFormat tool, which I accessed by adding the AutoFormat tool to my Quick Access Toolbar.)

PivotTable approach. To summarize and group your weekly employee time data using the PivotTable approach, you can create groups (or outline levels), as follows.

1. Make sure your data have unique column headings for each column of data.

2. Make sure your data range contains no blank rows or columns (but blank cells are OK).

3. Select any single cell in your data range, or select the entire data range by selecting a single cell in the data range and pressing Ctrl+A.

4. From the Insert tab, select PivotTable, and from the resulting Create PivotTable dialog box select OK. This action will create a new blank PivotTable canvas and PivotTable Fields dialog box on a new worksheet.

5. In the resulting PivotTable Fields dialog box, check the boxes next to each field name (as pictured below) to populate the PivotTable with employee time data.

6. The PivotTable will automatically include groups with Collapse buttons (depicted as minus sign icons) next to each employee’s name. To use the PivotTable’s group feature as pictured below, I right-clicked on one of the Collapse buttons and selected Expand/Collapse, Collapse Entire Field. I then clicked the Expand button next to Becky Hendrix’s name.

Both of the approaches described above result in collapsible data groups without the need to create each group individually. Download this example workbook at carltoncollins.com/group.xlsx.

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a CPE instructor, and a JofA contributing editor.

Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.