How to Display 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 just like other reporting tools. Most reports group data to summarize them and hide group details. Luckily, it’s easy to expand a group to show insights if that’s what you need.

UNDERSTAND: Google Workspace vs Microsoft 365: Side-by-side Analysis with Checklist (TechRepublic Premium)

In this tutorial, you’ll learn how to group a PivotTable and then reveal details that you might otherwise lose. I’m using Microsoft 365 on Windows 10 64-bit systems. 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 arises when you need to summarize and display the evaluated data. Luckily, if you know the right settings, it’s quick and easy.

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

Picture 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 select From Table/Range in the drop-down menu.
4. In the resulting dialog box, click Existing Sheet. Excel has set the correct range for the Table named TableInvoiceItems.
5. Click the Existing Sheet option, so you can view the data and the PivotTable together.
6. Click inside the Position control, and then click a cell in the worksheet, such as G3.
7. Click OK and Excel inserts a blank PivotTable frame.

Use Figure BUG As a guide, drag the fields to the appropriate list to create the PivotTable. Therefore, the PivotTable groups by the Invoice # column. By default, the PivotTable sums the Amount values ​​after you add that column to the Values ​​list. SIZE displays a PivotTable showing the totals for each invoice.

Figure BUG

SIZE

At a glance, you may not realize that the total for each invoice includes multiple items. That’s not good or bad, but if you want to give a hint you can add the quantity per bill as shown in Visualization.

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

Visualization

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

How to show details for a grouped PivotTable in Excel

Grouped Excel PivotTables are also useful enough, but you may want to show invoiced items. To do so, do the following:

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

Figure E

Figure F

WOOD Figure

As you can see in WOOD Figure, the PivotTable now shows each invoiced item. The totals for each invoice that are on the bucket you might want to change are as follows:

1. Click anywhere inside the PivotTable, if necessary.
2. Click the Contextual Design tab.
3. At the far left, click the Subtotal drop-down menu and select Show All Subtotals at the bottom of the Group.

Family picture displays the resulting PivotTable with all the entries for each invoice and the subtotals at the bottom of each invoice group. At this point you can decide to delete the count column. To do so, right-click the Count Amount2 header box and select Remove the Count Amount2 header. It’s up to you whether you keep it or get rid of it. That column’s subtotal can be useful information.

Family picture

You can also easily create a PivotTable using the Field List if you know what structure you need, but using interface selections is helpful when you don’t.