Insert subtotals in a list of data in a worksheet

Excel 2007

You can automatically calculate subtotals and grand totals in a list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) for a column by using the Subtotal command in the Outline group on the Data tab.

• Subtotals   are calculated with a summary function (summary function: A type of calculation that combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.), such as Sum or Average, by using the SUBTOTAL function. You can display more than one type of summary function for each column.
• Grand totals   are derived from detail data (detail data: For automatic subtotals and worksheet outlines, the subtotal rows or columns that are totaled by summary data. Detail data is typically adjacent to and either above or to the left of the summary data.), not from the values in the subtotals. For example, if you use the Average summary function, the grand total row displays an average of all detail rows in the list, not an average of the values in the subtotal rows.

If the workbook is set to automatically calculate formulas, the Subtotal command recalculates subtotal and grand total values automatically as you edit the detail data. The Subtotal command also outlines (outline: Worksheet data in which rows or columns of detail data are grouped so that you can create summary reports. The outline can summarize either an entire worksheet or a selected portion of it.) the list so that you can display and hide the detail rows for each subtotal.

1. Make sure that each column has a label in the first row, contains similar facts in each column, and that the range has no blank rows or columns.
2. Select a cell in the range.
3. Do one of the following:

Insert one level of subtotals

1. 1. Sort the column that forms the group.

For more information on sorting, see Sort data in a range or table.

1. 2. On the Data tab, in the Outline group, click Subtotal.

The Subtotal dialog box is displayed.

1. 3. In the At each change in box, click the column to subtotal. In the example above, you would select Sport.
   4. In the Use function box, click the summary function that you want to use to calculate the subtotals. In the example above, you would select Sum.
   5. In the Add subtotal to box, select the check box for each column that contains values that you want to subtotal. In the example above, you would select Sales.
   6. If you want an automatic page break following each subtotal, select the Page break between groups check box.
   7. To specify a summary row above the details row, clear the Summary below data check box. To specify a summary row below the details row, select the Summary below data check box. In the example above, you would clear the check box.
   8. Optionally, you can use the Subtotals command again by repeating steps one through seven to add more subtotals with different summary functions. To avoid overwriting the existing subtotals, clear the Replace current subtotals check box.

Insert nested levels of subtotals

1. 9. Sort the columns that form the groups.

For more information on sorting, see Sort data.

1. 10. Insert the outer subtotals.

How to insert the outer subtotals

1. 1. 1. On the Data tab, in the Outline group, click Subtotal.

The Subtotal dialog box is displayed.

1. 1. 2. In the At each change in box, click the column for the outer subtotals. In the example above, you would click Region.
      3. In the Use function box, click the summary function that you want to use to calculate the subtotals. In the example above, you would select Sum.
      4. In the Add subtotal to box, select the check box for each column that contains values that you want to subtotal. In the example above, you would select Sales.
      5. If you want an automatic page break following each subtotal, select the Page break between groups check box.
      6. To specify a summary row above the details row, clear the Summary below data check box. To specify a summary row below the details row, select the Summary below data check box. In the example above, you would clear the check box.
      7. Optionally, you can use the Subtotals command again by repeating steps one through six to add more subtotals with different summary functions. To avoid overwriting the existing subtotals, clear the Replace current subtotals check box.
   11. Insert the nested subtotals.

How to insert the nested subtotals

1. 1. 1. On the Data tab, in the Outline group, click Subtotal.

The Subtotal dialog box is displayed.

1. 1. 2. In the At each change in box, click the nested subtotal column. In the example above, you would select Sport.
      3. In the Use function box, click the summary function that you want to use to calculate the subtotals. In the example above, you would select Sum.

Select any other options that you want.

1. 1. 4. Clear the Replace current subtotals check box.
   12. Repeat the previous step for more nested subtotals, working from the outermost subtotals in.

Tip  To display a summary of just the subtotals and grand totals, click the outline symbols next to the row numbers. Use the and symbols to display or hide the detail rows for individual subtotals.

 

 

Excel > Summarizing, consolidating, and outlining data

Remove subtotals

Excel 2007

When you remove subtotals, Microsoft Office Excel also removes the outline (outline: Worksheet data in which rows or columns of detail data are grouped so that you can create summary reports. The outline can summarize either an entire worksheet or a selected portion of it.) and any page breaks that you inserted into the list along with the subtotals.

1. Click a cell in the list that contains a subtotal.
2. On the Data tab, in the Outline group, click Subtotal.

The Subtotal dialog box is displayed.

3. Click Remove All.

 

 

Excel > Summarizing, consolidating, and outlining data

Consolidate data in multiple worksheets

Excel 2007

To summarize and report results from separate worksheets, you can consolidate data from each separate worksheet into a master worksheet. The worksheets can be in the same workbook as the master worksheet or in other workbooks. When you consolidate data, you are assembling data so that you can more easily update and aggregate it on a regular or ad hoc basis.

For example, if you have a worksheet of expense figures for each of your regional offices, you might use a consolidation to roll up these figures into a corporate expense worksheet. This master worksheet might contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise.

To consolidate data, use the Consolidate command in the Data Tools group on the Data tab.

 

What do you want to do?

Consolidate by position

1. Set up the data to be consolidated on each separate worksheet.

How to set up the data

1. • Make sure that each range of data is in list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list.
   • Put each range on a separate worksheet. Don't put any of the ranges on the worksheet where you plan to put the consolidation.
   • Make sure that each range has the same layout.
   • Name each range: Select the entire range, and then on the Formulas tab in the Named Cells group, click the arrow next to Name a Range, and type a name for the range in the Name box.
2. Click the upper-left cell of the area where you want the consolidated data to appear in the master worksheet.

 Note     Make sure that you leave enough cells to the right and below this cell for the consolidated data. The Consolidate command populates the area as needed.

3. On the Data tab, in the Data Tools group, click Consolidate.

4. In the Function box, click the summary function (summary function: A type of calculation that combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.) that you want Microsoft Office Excel to use to consolidate the data.
5. If the worksheet is in another workbook, click Browse to locate the file, and then click OK to close the Browse dialog box.

The file path is entered in the Reference box followed by an exclamation point.

6. Type the name that you gave the range, and then click Add. Repeat this step for each range.
7. Decide how you want to update the consolidation. Do one of the following:
   • To set up the consolidation so that it updates automatically when the source data changes, select the Create links to source data check box.

Important  You can only select this check box if the worksheet is in another workbook. Once you select this check box, you won't be able to change which cells and ranges are included in the consolidation.

1. • To set up the consolidation so that you can update the consolidation manually by changing the included cells and ranges, clear the Create links to source data check box.
8. Leave the boxes under Use labels in blank. Excel does not copy the row or column labels in the source ranges to the consolidation. If you want labels for the consolidated data, copy them from one of the source ranges or enter them manually.

 Top of Page

Consolidate by category

1. Set up the data to be consolidated on each separate worksheet.

How to set up the data

1. • Make sure that each range of data is in list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list.
   • Put each range on a separate worksheet. Don't put any of the ranges on the worksheet where you plan to put the consolidation.
   • Make sure that the labels for columns or rows that you want to combine have identical spelling and capitalization; for example, labels Annual Avg. and Annual Average are different and will not consolidate.
   • Name each range: Select the entire range, and then on the Formulas tab in the Named Cells group, click the arrow next to Name a Range, and type a name for the range in the Name box.
2. Click the upper-left cell of the area where you want the consolidated data to appear in the master worksheet.

 Note     Make sure that you leave enough cells to the right and below this cell for the consolidated data. The Consolidate command populates the area as needed.

3. On the Data tab, in the Data Tools group, click Consolidate.

4. In the Function box, click the summary function (summary function: A type of calculation that combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.) that you want Excel to use to consolidate the data.
5. If the worksheet is in another workbook, click Browse to locate the file, and then click OK to close the Browse dialog box.

The file path is entered in the Reference box followed by an exclamation point.

6. Type the name you gave the range, and then click Add. Repeat this step for each range.
7. Decide how you want to update the consolidation. Do one of the following:
   • To set up the consolidation so that it updates automatically when the source data changes, select the Create links to source data check box.

Important  You can only select this check box if the worksheet is in another workbook. Once you select this check box, you won't be able to change which cells and ranges are included in the consolidation.

1. • To set up the consolidation so that you can update the consolidation manually by changing the included cells and ranges, clear the Create links to source data check box.
8. Select the check boxes under Use labels in that indicate where the labels are located in the source ranges: either the Top row, the Left column, or both.

 Notes 

1. • Any labels that don't match up with labels in the other source areas result in separate rows or columns in the consolidation.
   • Make sure that any categories that you don't want to consolidate have unique labels that appear in only one source range.

 Top of Page

Consolidate by formula

1. On the master worksheet, copy or enter the column or row labels that you want for the consolidated data.
2. Click a cell that you want to contain consolidated data.
3. Type a formula that includes a cell reference to the source cells on each worksheet or a 3-D reference that contains data that you want to consolidate. Regarding cell references, do one of the following:

If the data to consolidate is in different cells on different worksheets  

1. • Enter a formula with cell references to the other worksheets, one for each separate worksheet. For example, to consolidate data from worksheets named Sales (in cell B4), HR (in cell F5), and Marketing (in cell B9), in cell A2 of the master worksheet, you would enter the following:

Tip  To enter a cell reference, such as Sales!B4, in a formula without typing, type the formula up to the point where you need the reference, click the worksheet tab, and then click the cell.

If the data to consolidate is in the same cells on different worksheets  

1. • Enter a formula with a 3-D reference that uses a reference to a range of worksheet names. For example, to consolidate data in cells A2 from Sales through Marketing inclusive, in cell A2 of the master worksheet you would enter the following:

 Note    If the workbook is set to automatically calculate formulas, a consolidation by formula always updates automatically when the data in the separate worksheets change.

 Top of Page

Use a PivotTable report to consolidate data

You can create a PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources including ones external to Excel.) from multiple consolidation ranges. This method is similar to consolidating by category, but it offers more flexibility to reorganize the categories. For more information see Consolidate multiple worksheets into one PivotTable report.

 Top of Page

 

 

Excel > Summarizing, consolidating, and outlining data

Change a data consolidation of multiple worksheets

Excel 2007

After you have consolidated data from multiple worksheets, you may want to change the way that the data is consolidated. For example, you may want to add worksheets from new regional offices, delete worksheets from departments that no longer exist, or change formulas with 3-D references (3-D reference: A reference to a range that spans two or more worksheets in a workbook.).

What do you want to do?

Change a consolidation made by position or category

Change a consolidation by formula

Change a consolidation made by position or category

 Note    You can change the consolidation only if you did not previously select the Create links to source data check box in the Consolidate dialog box. If the check box is selected, click Close, and then re-create the consolidation.

1. Click the upper-left cell in the consolidated data.
2. On the Data tab, in the Data Tools group, click Consolidate.
3. Do one or more of the following:

Add another source range to the consolidation

1. 1. If the worksheet is in another workbook, click Browse to locate the file, and then click OK to close the Browse dialog box.

The file path is entered in the Reference box followed by an exclamation point.

1. 2. Type the name that you gave the range, and then click Add.

Adjust the size or shape of a source range

1. 3. Under All references, click the source range that you want to change.
   4. In the Reference box, edit the selected reference.
   5. Click Add.

Delete a source range from the consolidation

1. 6. Under All references, click the source range that you want to delete.
   7. Click Delete.

Make the consolidation update automatically

4. To update the consolidation with the changes, click OK.

 Top of Page

Change a consolidation by formula

You change a consolidation by formula by editing the formulas, such as changing the function or expression. Regarding cell references, you can do one of the following:

If the data to consolidate is in different cells on different worksheets  

• Add, change, or delete the cell references to other worksheets. For example, to add a reference to cell G3 in a Facilities worksheet that you have inserted following the Marketing worksheet, you would edit the formula as shown in the following example.

Before:

After:

If the data to consolidate is in the same cells on different worksheets  

• To add another worksheet to the consolidation, move the sheet into the range that your formula refers to. For example, to add a reference to cell B3 in the Facilities worksheet, move the Facilities worksheet between the Sale and HR sheets as shown in the following example.

Because your formula contains a 3-D reference to a range of worksheet names, Sales:Marketing!B3, all worksheets in the range are included in the new calculation.

 Top of Page

 

 

Excel > Summarizing, consolidating, and outlining data

Outline a list of data in a worksheet

Excel 2007

If you have a list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) of data that you want to group and summarize, you can create an outline of up to eight levels, one for each group. Each inner level, represented by a higher number in the outline symbols (outline symbols: Symbols that you use to change the view of an outlined worksheet. You can show or hide detailed data by pressing the plus sign, minus sign, and the numbers 1, 2, 3, or 4, indicating the outline level.) displays detail data (detail data: For automatic subtotals and worksheet outlines, the subtotal rows or columns that are totaled by summary data. Detail data is typically adjacent to and either above or to the left of the summary data.) for the preceding outer level, represented by a lower number in the outline symbols. Use an outline to quickly display summary rows or columns, or to reveal the detail data for each group. You can create an outline of rows (as shown in the example below), an outline of columns, or an outline of both rows and columns.

Important  Do not include the summary row 11 in the selection.

1. • 2. On the Data tab, in the Outline group, click Group.

The outline symbols appear beside the group on the screen.

1. • Optionally, outline an inner, nested group.

How to outline the inner, nested group

1. • 1. For each inner, nested group, select the detail rows adjacent to the row that contains the summary row.

In the example below, to group rows 2 through 5, which has a summary row 6, select rows 2 through 5. To group rows 7 through 9, which has a summary row 10, select rows 7 through 9.

Important  Do not include the summary row for that group in the selection.

1. • 2. On the Data tab, in the Outline group, click Group.

The outline symbols appear beside the group on the screen.

1. • Continue selecting and grouping inner rows until you have created all of the levels that you want in the outline.
   • If you want to ungroup rows, select the rows, and then on the Data tab, in the Outline group, click Ungroup.

 Top of Page

Create an outline of columns

1. Make sure that each row has a label in the first column, contains similar facts in each row, and that the range has no blank rows or columns.
2. Select a cell in the range.
3. Sort the rows that form the groups.

For more information on sorting, see Sort data in a range or table.

4. Insert your own summary columns with formulas immediately to the right or left of each group of detail columns.

 Note    To outline data by columns, you must have summary columns that contain formulas that reference cells in each of the detail columns for that group.

5. Specify whether the location of the summary column is to the right or left of the detail columns.

How to specify the summary column location

1. 1. On the Data tab, in the Outline group, click the Outline Dialog Box Launcher.

1. 2. To specify a summary column to the left of the details column, clear the Summary columns to right of detail check box. To specify a summary column to the right of the details column, select the Summary columns to right of detail check box.
6. Outline the data. Do one of the following:

Outline the data automatically

1. 1. If necessary, select a cell in the range.
   2. On the Data tab, in the Outline group, click the arrow next to Group, and then click Auto Outline.

Outline the data manually

1. 3. Outline the outer group.

How to outline the outer group

1. 1. 1. Select all of the subordinate summary columns, as well as their related detail data.

In the example below, column E contains the subtotals for columns B through D, and column I contains the subtotals for columns F through H, and column J contains the grand totals. To group all of the detail data for column J, select columns B through I.

Important  Do not include the summary column J in the selection.

1. 1. 2. On the Data tab, in the Outline group, click Group.

The outline symbols appear beside the group on the screen.

1. 4. Optionally, outline an inner, nested group.

How to outline inner, nested group

1. 1. 1. For each inner, nested group, select the detail columns adjacent to the column that contains the summary column.

In the example below, to group columns B through D, which has a summary column E, select columns B through D. To group columns F through H, which has a summary row I, select columns F through H.

Important  Do not include the summary column for that group in the selection.

1. 1. 2. On the Data tab, in the Outline group, click Group.

The outline symbols appear beside the group on the screen.

1. 5. Continue selecting and grouping inner columns until you have created all of the levels that you want in the outline.
   6. If you want to ungroup columns, select the columns, and then on the Data tab, in the Outline group, click Ungroup.

1. 7.  Note    You can also ungroup sections of the outline without removing the entire outline. Hold down SHIFT while you click the or for the group, and then on the Data tab, in the Outline group, click Ungroup.

Important  If you ungroup an outline while the detail data is hidden, the detail columns may remain hidden. To display the data, drag across the visible column letters adjacent to the hidden columns. On the Home tab, in the Cells group, click Format, point to Hide & Unhide, and then click Unhide Columns.

 Top of Page

Show or hide outlined data

1. If you don't see the outline symbols , , and , click the Microsoft Office Button , click Excel Options, click the Advanced category, and then under the Display for this worksheet section, select the worksheet, and select the Show outline symbols if an outline is applied check box.
2. Do one or more of the following:

Show or hide the detail data for a group  

1. • To display the detail data within a group, click the for the group.
   • To hide the detail data for a group, click the for the group.

Expand or collapse the entire outline to a particular level  

1. • In the outline symbols, click the number of the level that you want. Detail data at lower levels is then hidden.

For example, if an outline has four levels, you can hide the fourth level while displaying the rest of the levels by clicking .

Show or hide all of the outlined detail data  

1. • To show all detail data, click the lowest level in the outline symbols. For example, if there are three levels, click .
   • To hide all detail data, click .

 Top of Page

Customize an outline with styles

For outlined rows, Microsoft Office Excel uses styles (style: A combination of formatting characteristics, such as font, font size, and indentation, that you name and store as a set. When you apply a style, all of the formatting instructions in that style are applied at one time.) such as RowLevel_1 and RowLevel_2 . For outlined columns, Excel uses styles such as ColLevel_1 and ColLevel_2. These styles use bold, italic, and other text formats to differentiate the summary rows or columns in your data. By changing the way each of these styles is defined, you can apply different text and cell formats to customize the appearance of your outline. You can apply a style to an outline either when you create the outline or after you create it.

Do one or more of the following:

Automatically apply a style to a summary row or column  

1. On the Data tab, in the Outline group, click the Outline Dialog Box Launcher.

2. Select the Automatic styles check box.

Apply a style to an existing summary row or column  

1. Select the cells that you want to apply outline styles to.
2. On the Data tab, in the Outline group, click the Outline Dialog Box Launcher.

3. Select the Automatic styles check box.
4. Click Apply Styles.

 Note    You can also use autoformats (autoformat: A built-in collection of cell formats (such as font size, patterns, and alignment) that you can apply to a range of data. Excel determines the levels of summary and detail in the selected range and applies the formats accordingly.) to format outlined data.

 Top of Page

Copy outlined data

1. If you don't see the outline symbols , , and , click the Microsoft Office Button , click Excel Options, click the Advanced category, and then under the Display options for this worksheet section, select the worksheet, and select the Show outline symbols if an outline is applied check box.
2. Use the outline symbols , , and to hide the detail data that you don't want copied.

For more information, see the section, Show or hide outlined data.

3. Select the range of summary rows.
4. On the Home tab, in the Editing group, click Find & Select, and then click Go To.

5. Click Go To Special.
6. Click Visible cells only.
7. Click OK, and then copy the data.

 Top of Page

Hide or remove an outline

 Note    No data is deleted when you hide or remove an outline.

Hide an outline

1. If you don't see the outline symbols , , and , click the Microsoft Office Button , click Excel Options, click the Advanced category, and then under the Display options for this worksheet section, select the worksheet, and select the Show outline symbols if an outline is applied check box.
2. Display all of the data by clicking the highest number in the outline symbols.
3. Click the Microsoft Office Button , click Excel Options, click the Advanced category, and then under the Display options for this worksheet section, select the worksheet, and clear the Show outline symbols if an outline is applied check box.

Remove an outline

1. Click the worksheet.
2. On the Data tab, in the Outline group, click the arrow next to Ungroup, and then click Clear Outline.

3. If rows or columns are still hidden, drag across the visible row or column headings on both sides of the hidden rows and columns, point to Hide & Unhide on the Format command, in the Cells group on the Home tab, and then click Unhide Rows or Unhide Columns.

Important  If you remove an outline while the detail data is hidden, the detail rows or columns may remain hidden. To display the data, drag across the visible row numbers or column letters adjacent to the hidden rows and columns. On the Home tab, in the Cells group, click Format, point to Hide & Unhide, and then click Unhide Rows or Unhide Columns.

 Top of Page

Create a summary report with a chart

Let's say that you want to create a summary report of your data that only displays totals accompanied by a chart of those totals. In general, you can do the following:

1. Create a summary report.
   1. Outline your data.

For more information, see the sections Create an outline of rows or Create an outline of columns.

1. 2. Hide the detail by clicking the outline symbols , , and to show only the totals as shown in the following example of a row outline:

2. For more information, see the section, Show or hide outlined data.
3. Chart the summary report.
   1. Select the summary data that you want to chart.

For example, to only chart the Buchanan and Davolio totals, but not the grand totals, select cells A1 through C11 as shown in the above example.

1. 2. Create the chart.

For example, if you create the chart by using the Chart Wizard, it would look like the following example.

 Note    If you show or hide details in the outlined list of data, the chart is also updated to show or hide the data.

For more information, see Create a chart and Display hidden worksheet data in a chart.

 Top of Page