How to display details in a grouped pivot table 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’s pivot tables turn data into useful information just like other reporting tools. Most reports group data to summarize data and hide group details. Fortunately, it’s easy to expand a group to show details if that’s what you need.

SEE: Google Workspace vs. Microsoft 365: A Side-by-Side Analysis with Checklist (TechRepublic Premium)

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

How to Create a Grouped Pivot Table 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 arises when you need to summarize and display the evaluated data. Fortunately, if you know the correct settings, it’s quick and easy.

To demonstrate how to group data quickly, we’ll base a pivot table on the simple Table object shown in Figure A. The table 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 pivot table to group by invoice numbers and display a total for each invoice.

Figure A

Now, let’s create the pivot table 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 from the drop-down menu.
4. In the resulting dialog box, click Existing Worksheet. Excel has successfully set the range for the table named TableInvoiceItems.
5. Click on the Existing Sheet option so you can see the data and the pivot table together.
6. Click inside the Location control, and then click in a cell on the sheet, such as G3.
7. Click OK and Excel will insert an empty pivot table frame.

Wearing Figure B as a guide, drag the fields into the appropriate lists to build the pivot table. As a result, the pivot table is grouped by the Invoice Number column. By default, the pivot table sums the quantity values ​​after adding that column to the list of values. Figure C displays the pivot table showing a total for each invoice.

Figure B

Figure C

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

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

Figure D

Once you have a pivot table grouped by invoice, you can display the details of each invoice.

How to display the details of a grouped pivot table in Excel

The grouped Excel Pivot Table is useful enough as is, but you may want to display the items that are invoiced. To do so, do the following:

1. In the pivot table, select the grouped values. In this case, that’s the Invoice Number 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, select Amount (Figure F) and click OK. Although we group the pivot table by invoice number, we want to see each item in each group.

Figure E

Figure F

Figure G

as you can see in Figure G, the pivot table now shows each invoiced item. The total of each invoice is above the group, which you may want to change, as follows:

1. Click anywhere inside the pivot table, if necessary.
2. Click the Contextual Design tab.
3. On the far left, click the Subtotals dropdown menu and choose Show all subtotals at bottom of group.

Figure H displays the resulting pivot table with all items for each invoice and subtotals at the bottom of each group of invoices. At this point, you may decide to remove the count column. To do this, right-click the Quantity2 Count header cell and choose Delete Quantity2 Count Header. It’s up to you if you keep it or remove it. The subtotal for that column can be useful information.

Figure H

You can easily create the pivot table using the field list if you know what you need in terms of structure, but using the interface options is useful when you don’t.