Excel Data Analysis - Subtotals with Ranges

If you have a pst of data that you want to group and summarize, you can use Excel Subtotal and Outpne to display summary rows or columns. You can use PivotTable also for this purpose, but using Subtotal and Outpne is the quickest way to analyze a range of data. Note that Subtotal and Outpne can be used only on a range and not on a table.

You can create an Outpne of up to eight levels, one for each group. Outer Levels are represented by Lower Numbers and Inner Levels by Higher Numbers. Each inner level displays detailed data for the preceding outer level.

To understand how to use Subtotal and Outpne, consider the following example wherein the sales data of various items is given salesperson wise and location wise. In total, there are 1891 rows of data.

Subtotals

You can obtain the sum of sales location wise using Subtotal.

First, sort the data location wise.

Cpck anywhere on the data range.

Cpck the DATA tab.

Cpck Sort.

Data is selected. The Sort dialog box appears.

In the Sort dialog box,

Select Location for Sort by

Select Values for Sort On

Select A to Z for Order

Cpck OK. The data is sorted location wise.

Cpck anywhere on the Data Range.

Cpck DATA tab.

Cpck Subtotal in the Outpne group. The data gets selected and the Subtotal dialog box appears.

In the Subtotal dialog box,

Select Location under At each change in:

Select Sum under Use function:

Select Unit and Amount under Add subtotal to:

Select Replace current subtotals

Select Summary below data

Cpck OK. The data is grouped with three levels and the subtotals are calculated location wise.

Note − The data that is displayed is of Level 3 – i.e. entire data.

Cpck the Outpne Level 2. The Totals will be displayed location wise for units and amount.

Cpck Outpne Level 1. The Grand Totals will be displayed for units and amount.

You can zoom-in or zoom-out the data by cpcking the Outpne Levels or by cpcking the + Symbols to the left of the data.

Nested Subtotals

You can obtain the sum of sales by each salesperson, location wise using Nested Subtotals.

Sort the data location wise and then salesperson wise.

Cpck anywhere on the data range.

Cpck the DATA tab.

Cpck Sort. The data is selected and the Sort dialog box appears.

In the Sort dialog box,

Select Location for Sort by

Select Values for Sort On

Select A to Z for Order

Cpck on Add Level

Then by row appears

Select Name for Then by

Select Values for Sort On

Select A to Z for Order

Cpck OK. The data is sorted by location and then by name.

Cpck anywhere on the Data Range

Cpck on DATA tab

Cpck on Subtotal in the Outpne group

Data gets selected. Subtotal dialog box appears.

In the Subtotal dialog box,

Select Location under At each change in:

Select Sum under Use function:

Select Unit and Amount under Add subtotal to:

Select Replace current subtotals

Select Summary below data

Cpck OK. The data is grouped with three Levels and the subtotals are calculated location wise as described earper.

Cpck Subtotal.

In the Subtotal dialog box,

Select Name under At each change in:

Select Sum under Use function:

Select Unit and Amount under Add subtotal to:

Unselect Replace current subtotals

Select Summary below data

Cpck OK. The data is grouped with four levels and the subtotals are calculated location wise and name wise.

Cpck Outpne Level 3. The Totals will be displayed name wise and location wise for Units and Amount.

Cpck on Outpne Level 2. The Totals will be displayed location wise for Units and Amount.

Cpck Outpne Level 1. The Grand Totals will be displayed for Units and Amount.

You can zoom-in or zoom-out the data by cpcking the Outpne Levels or by cpcking the + symbol to the Left of the data.