Excel: Summarize (subtotal) data

Quite often, we work with long lists of data, like transactions, that are more easily read in a summarized and subtotaled manner. For example, let’s say we have a list of investment market values with many duplicate CUSIPs (unique identifiers).

If we want the total number of shares and total market value for each CUSIP, we can insert subtotals. Here’s how:

1. In the Data tab of the ribbon, click the “Subtotal” button.
   
2. In the dialog box, select the basis for the subtotal by selecting a field in “At each change in” drop-down menu. It’s best that the data has headings immediately at the top of the list.
3. Choose a function to perform (usually sum, but you may also count, average, etc.).
4. Select the fields you would like to subtotal.
   
5. Click OK when finished.

You should now see a new segment to the left. This allows you to collapse or expand the data as desired.

Fully-expanded:

Collapsed to subtotals (click on the “2”):

HINT: To select and copy/paste only the visible cells:

1. Select a region of data.
2. On the Home tab of the ribbon, click “Find and Select,” and select “Go To Special…”
3. Select “Visible cells only” and click OK.