How to View Details in a Grouped PivotTable in Microsoft Excel

0

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

Image: wachiwit/Adobe Stock

Microsoft Excel Pivot tables turn data into useful information, just like other reporting tools. Most reports group data to summarize it and hide group details. Fortunately, it’s easy to expand a group to show the 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 PivotTable and then show details that you might otherwise lose. I use 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 arises when you need to summarize and display the evaluated data. Luckily, if you know the right settings, it’s quick and easy.

To show how to quickly group data, we’ll base a PivotTable 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

A pivot table will show an invoice total for each invoice in TableInvoiceItems

Now let’s create the pivot table as follows:

  1. Click anywhere in the table.
  2. Click on 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 successfully set the range for the table named TableInvoiceItems.
  5. Click the Existing Sheet option to see the data and PivotTable together.
  6. Click inside the Location control, then click a cell on the sheet, such as G3.
  7. Click OK and Excel inserts an empty PivotTable frame.

using Figure B as a guide, drag the fields to the appropriate lists to create the pivot table. Therefore, the PivotTable groups by the Invoice # column. By default, the PivotTable sums Amount values ​​after you add this column to the Values ​​list. Figure C shows the pivot table displaying a total for each invoice.

Figure B

Group the pivot table on the Invoice # column.

Figure C

The grouped pivot table returns a total for each invoice.

At a quick glance, you may not realize that the sum of each invoice includes several components. It’s neither good nor bad, but if you want to add 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 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 the invoice has multiple items. It may not be important to share this information, but you can easily do so.

Figure D

Add a number of items for each invoice.

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

How to view details of a grouped PivotTable in Excel

The Grouped Excel PivotTable is useful enough as it is, but you may want to show billed items. For that, proceed as following :

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

Figure E

Expanding the group will display the items in each group.

Figure F

Expand the group by the Amount column.

G-figure

The results display each invoice item.

As you can see in G-figure, the PivotTable now displays each billed item. The total for each invoice is above the group, which you may want to modify, as follows:

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

H-figure displays 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 remove the count column. To do this, right-click the Count of Amount2 header cell and choose Remove Count of Amount2 header. You are free to keep it or delete it. The subtotal of this column can be useful information.

H-figure

Show subtotals at the bottom of the invoice group.

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

Share.

About Author

Comments are closed.