How to use Excel’s pivot capability to summarize grouped information

Posted on January 26, 2009 at 5:12 am

As Excel users we always have the need to summarize information but that if overall summaries and filtering are not enough for our needs?  The case for grouped summaries comes in whenever you are asked for summaries for each group of information coming from a single datasheet.  Let’s get started!

Step 1: Open up your datasheet in Excel.  As an example I will use the one below where I will group information based on Column A – Machine Type:

image

Step 2:    Click PivotTable and PivotChart Report under the Data menu.  This should open the Pivot wizard window:

image

Step 3: Follow the Wizard steps, step 1 of 3 lets you set the datasource and type of report.  Choose the default values since we will use data from the same Excel file and we want to generate a PivotTable report:

image

Step 4: Enter the range of data to be sourced. In this case, columns from A to C:

image

Step 5:  Choose New Sheet as output.  Otherwise it will show up on the current existing sheet.

Step 6:  Drag and drop the columns that you want to group to the left where it says “drop row fields here”.  In the example below I will group the summaries by MACHINE TYPE so I will drag and drop MACHINE TYPE column there:

image

Step 7:  Next drop the numeric data on the center field:

image

Step 8: Set the aggregation type by clicking the header button of the data field.  In the example below I changed the Count of EFFICIENCY to Average of EFFICIENCY:

image

You can choose from several aggregation types like sum, count, max, min, product, etc… You can also put multiple types of summaries on the same pivot table by dragging the same column next to the data column of the existing ones.

That’s it! You should have a basic pivoted data report. Here is what we have accomplished:

image

With pivoted summaries you can quickly create summaries grouped by certain columns (yes, you can make summaries grouped by not just one column).  This way data analysis becomes quicker.  You can use the steps above on your own datasheet to quickly generate reports.

Ben Carigtan shows you how it’s done!

» Filed Under MS Office Tips

Related Posts

Comments

2 Responses to “How to use Excel’s pivot capability to summarize grouped information”

  1. Learn Excel said on : January 26th, 2009 at 7:14 pm

    I never knew how to the pivot capabilities in Excel. Thanks!


  2. John C said on : January 27th, 2009 at 4:19 pm

    I wish You had a printer friendly feature. I would like to print the article and exibits without carrying the ad’s, etc with it.

    Thanks, John C


Please post your comments/suggestions!