How to show details in a grouped PivotTable in Microsoft Excel

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

Microsoft Excel Pivot tables turn data into useful information, just like other reporting tools. Most reports group data to summarize and hide group details. Fortunately, it’s easy to expand a group to show details if that’s what you need.

TO SEE: Google Workspace versus Microsoft 365: a side-by-side analysis with checklist (Tech Republic Premium)

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

Create a grouped pivot table in Excel

Grouping is what Excel’s PivotTable objects do, and it’s easy to summarize groups of data with any number of functions. Sometimes a problem arises 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, let’s base a pivot table on the simple table object shown in Image A. The name of the table is TableInvoiceItems and it tracks invoice items by invoice number. Each invoice can contain 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.

Image A

Now let’s create the pivot table like this:

1. Click anywhere in the table.
2. Click the Insert tab.
3. In the Tables group, click PivotTable and choose From Table/Range from the drop-down list.
4. In the resulting dialog box, click Existing Worksheet. Excel has correctly set the range on the TableInvoiceItems table.
5. Click the Existing Sheet option so you can see the data and the pivot table together.
6. Click in the location control, then click a cell in the sheet, such as G3.
7. Click OK and Excel inserts an empty pivot table frame.

Using Figure B as a guideline, drag fields to the appropriate lists to create the pivot table. As a result, the pivot table is grouped by the Invoice # column. By default, the pivot table sums the Amount values ​​after you add that column to the Values ​​list. Figure C shows the pivot table with a total for each invoice.

Figure B

Figure C

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

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

Figure D

Once you have a pivot table grouped by invoice, you can view the invoice information for each invoice.

How to display the details for a grouped pivot table in Excel?

The Grouped Excel PivotTable is handy enough as it is, but you may want to display billed items. To do this, proceed as follows:

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

Digits E

Figure F

Figure G

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

1. If necessary, click anywhere in the pivot table.
2. Click the Contextual Design tab.
3. On the far left, click the Subtotals drop-down list and choose Show all subtotals at the bottom of group.

Figure H shows the resulting pivot table with all items for each invoice and subtotals at the bottom of each invoice group. At this point you can decide to delete the counting column. To do this, right click on the cell Count or Amount2 header and choose Remove Count or Amount2 header. It is up to you whether you keep it or delete it. The subtotal for that column can be useful information.

Figure H

You can just as easily create the pivot table using the list of fields if you know what you need structurally, but using the interface choices is helpful if you don’t.