Summarizing data

Summarize data to group and count the rows for each unique value in a column. When you summarize, you can also subtotal numeric columns for each unique value.

How it works

Sorting

The values in the column you are summarizing on are automatically sorted in ascending order, therefore you do not need to sort data before summarizing.

Order of operations

When you summarize data, Add-In for Excel:

  1. groups rows based on unique values in a column
  2. subtotals the number of rows for each unique value and then calculates the percentage of the total count represented by each group
  3. sorts the unique values
  4. for each numeric subtotal column included in the results, subtotals each unique value and calculates the percentage of the total value represented by each subtotal

Example

You are working with an accounts receivable table and you need to find the total number of transactions for each customer. To find this, you summarize on customer ID and subtotal the transaction amount column.

In your results, you see the total number of transactions per customer and the total transaction amount per customer.

Outputting results to Pivot reports

If you output the results of summarizing to a PivotTable or PivotChart, you can summarize on more than one column.

Example

You are working with an accounts receivable table and you need to find the total number of each transaction type for each customer. To find this, you summarize on both the customer ID and the transaction type columns and then subtotal the transaction amount column.

In your results, summarizing counts the rows for each unique combination of values across the multiple columns.

Summarize data

Select the column to summarize on

  1. With a cell selected in an Add-In for Excel table, click the ACL Add-In tab and select Summarize > Summarize.
  2. Select a column of any data type to summarize on.
  3. Optional To omit the count or percentage for the unique values in the column, clear Include count or Include percentage.

    If you clear Include count, Include percentage is also automatically cleared.

  4. To configure subtotals and pivot output, click Next or click Finish to output the results as is.

Select the numeric subtotal columns

  1. Select one or more numeric columns to subtotal.
  2. Optional To omit the percentage for the subtotal amounts, clear Include percentage.
  3. To configure pivot output, click Next or click Finish to output the results as is.

Define the output format

To define the output format, select one of the following output options and then click Finish:

  • ACL Add-In Table outputs a defined ACL Add-in table
  • PivotTable outputs a standard Excel PivotTable
  • PivotChart outputs a standard Excel PivotChart with the accompanying PivotTable

The output results appear in a new ACL Add-In worksheet. If you output the results to a PivotTable or PivotChart, you can summarize by additional columns (nested summarizing), or add additional subtotal columns:

  1. Click any cell in the PivotTable, or click the PivotChart.
  2. If the Pivot Table Field List does not appear, with a cell in the PivotTable selected, click the Options tab and select Field List.
  3. In the PivotTable Field List, select additional columns to summarize or subtotal on.