How to show details in a grouped PivotTable in Microsoft Excel

It’s easy to display details in a PivotTable group if you know how to expand the group in Microsoft Excel.

Microsoft Excel PivotTables turn data into useful information the same as other reporting tools. Most reports group data to summarize them and  hide group details. Fortunately, it’s easy to expand a group to display details if that’s what you need.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

In this tutorial, you’ll learn how to group a PivotTable and then unhide details that you might otherwise lose. I’m using Microsoft 365 on a Windows 10 64-bit system. Excel for the web supports PivotTables.

How to create a grouped PivotTable in Excel

Grouping is what Excel’s PivotTable objects do, and it’s easy to summarize groups of data using any number of functions. Sometimes, a problem crops up when you need to summarize and display the evaluated data. Fortunately, if you know the right settings, it’s quick and easy.

To demonstrate how to quickly group data, we’ll base a PivotTable on the simple Table object shown in Figure A. The Table’s name is TableInvoiceItems, and it tracks invoice items by invoice number. Each invoice can have one or more items. Our goal for now is to use a PivotTable to group by invoice numbers and display a total for each invoice.

Figure A

Now, let’s create the PivotTable as follows:

1. Click anywhere inside the Table.
2. Click the Insert tab.
3. In the Tables group, click PivotTable and choose From Table/Range in the dropdown.
4. In the resulting dialog, click Existing Worksheet. Excel has correctly set the range to the Table named TableInvoiceItems.
5. Click the Existing sheet option, so you can see the data and the PivotTable together.
6. Click inside the Location control and then click a cell in the sheet, such as G3.
7. Click OK and Excel inserts an empty PivotTable frame.

Using Figure B as a guide, drag fields to the appropriate lists to build the PivotTable. As a result, the PivotTable groups by the Invoice # column. By default, the PivotTable sums the Amount values after you add that column to the Values list. Figure C shows the PivotTable displaying a total for each invoice.

Figure B

Figure C

With a quick glance, you might not realize that the sum for each invoice comprises multiple items. That’s not good or bad, but if you want to drop in a hint, you can add a count for each invoice as shown in Figure D.

To do so, add the Amount column to the Values list a second time. Click its dropdown and choose Value Field Settings from the resulting submenu. In the resulting dialog, choose Count and click OK. If this value is over 1, you know that the invoice has more than one item. It might not be important to share this information, but you can easily do so.

Figure D

Once you have a PivotTable grouped by the invoice, you can display the invoice details for each invoice.

How to display the details for a grouped PivotTable in Excel

The grouped Excel PivotTable is useful enough as is, but you might want to display invoiced items. To do so, do the following:

1. In the PivotTable, select the grouped values. In this case, that’s the Invoice # column so select G4:F13.
2. Right-click the selection, choose Expand/Collapse and then choose Expand from the submenus (Figure E).
3. In the resulting dialog, choose Amount (Figure F) and click OK. Although we grouped the PivotTable by the Invoice #, we want to see each item in each group.

Figure E

Figure F

Figure G

As you can see in Figure G, the PivotTable now displays each invoiced item. The total for each invoice is above the group, which you might want to change, as follows:

1. Click anywhere inside the PivotTable, if necessary.
2. Click the contextual Design tab.
3. To the far left, click the Subtotals dropdown and choose Show All Subtotals At Bottom Of Group.

Figure H shows the resulting PivotTable with all items for each invoice and subtotals at the bottom of each invoice group. At this point, you might decide to delete the count column. To do so, right-click the Count of Amount2 header cell and choose Remove Count of Amount2 header. It’s up to you whether you keep it or remove it. The subtotal for that column may be useful information.

Figure H

You could just as easily create the PivotTable using the Field List if you know what you need structure-wise but using the interface choices is helpful when you don’t.