Summarize / Pivot

This command allows you to summarize your data on up to 3 levels by specifying Group by columns.

Group by columns are used to order your data and then to roll it up into one row containing sub-totals for one or more columns in the worksheet.

Columns To Analyze

Here you specify up to 3 columns by which to create summary sub-totals for.

Columns To Summarize

Here you specify which numeric columns you would like to produce summarized sub-totals for.

Create Crosstab/Pivot Table

If this column is checked then a pivot table will be created.

Pivot Column

This is the column that is displayed across the top of the pivot table.

The Group By Columns are displayed down the left hand side of the pivot table.

If you select a date column as the column to summarize then the dropdown list next to the pivot field dropdown allows you to pivot on the following component parts of the selected date:

  • As is
  • Financial Quarter
  • Year
  • Month
  • Day Of Month
  • Day Of Week
  • Day Of Year
  • Week Of Year

Crosstab Calculation

By default, sub-totals are displayed in the pivot table. Use this dropdown to choose a different calculation. Calculations include:

  • Total
  • Count
  • Average
  • Count Numbers
  • Maximum
  • Minimum
  • Product
  • Standard Deviation
  • Population Standard Deviation
  • Variance
  • Population Variance

Include Statistics

When this option is checked, the statistical items selected (see Select Fields below) are displayed in addition to the sub-totals for each group.

Select Fields

If the Include Statistics option is checked then this button allows you to select which descriptive statistics to include in the summarization.

Include Grand Totals

If this option is checked then the last row of the resulting summary worksheet will contain the grand totals for each summarization column selected.

Columnwise Output

If this option is checked then the output summary worksheet is transposed from rows to columns.

Example 1

  • Open Easy-XL's Sample Workbook
  • Select the Invoices Sheet
  • Select Group Summary from the Easy-XL Menu
  • Select SalesPerson as the Group By Column
  • Select Amount in the Columns To Summarize list
  • Check the Include Statistics option
  • Click the Select Fields button and then check all of the statistical calculations you would like to include and click Finished. This will return you to the Group Summary dialog.
  • Click Finished
  • Easy-XL creates a result sheet called "Summary of Invoices".

Example 2

  • Open Easy-XL's Sample Workbook
  • Select the Invoices Sheet
  • Select Group Summary from the Easy-XL Menu
  • Select SalesPerson as the Group By Column
  • Select Amount in the Columns To Summarize list
  • Check the Create Crosstab/Pivot Table option.
  • Select Quarter as the pivot column
  • Click Finished
  • Easy-XL creates a pivot table sheet called "Summary of Invoices(1)".