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).

SS-2010.07.07-13.25.08

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.
    SS-2010.07.07-13.26.30
  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.
    SS-2010.07.07-13.30.02
  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:

SS-2010.07.07-13.31.23

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

SS-2010.07.07-13.32.35

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.
    SS-2010.07.07-13.35.16

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: