group and summarize data in a worksheet Excel 2007

Author: mety Labels::

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.

Outlined list

An outlined row of sales data grouped by geographical regions and months with several summary and detail rows displayed

Callout 1 To display rows for a level, click the appropriate one two three outline symbols.
Callout 2 Level 1 contains the total sales for all detail rows.
Callout 3 Level 2 contains total sales for each month in each region.
Callout 4 Level 3 contains detail rows (only detail rows 11 through 13 are currently visible).
Callout 5 To expand or collapse data in your outline, click the plus and minus outline symbols.

Create an outline of rows

  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. Sort the columns that form the groups.

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

  4. Insert summary rows.

    To outline data by rows, you must have summary rows that contain formulas that reference cells in each of the detail rows for that group. Do one of the following:

    Insert summary rows by using the Subtotal command

    Insert your own summary rows

    • Insert your own summary rows with formulas immediately below or above each group of detail rows.

  5. Specify whether the location of the summary row is below or above the detail rows.

    HideHow to specify the summary row location

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

      Outlook Ribbon Image

    2. To specify a summary row above the details row, clear the Summary rows below detail check box. To specify a summary row below the details row, select the Summary rows below detail check box.
  6. Outline the data. Do one of the following:

    HideOutline the data automatically

    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.

      Outlook Ribbon Image

    HideOutline the data manually

    Important When you manually group outline levels, it's best to have all data displayed to avoid grouping the rows incorrectly.

    1. Outline the outer group.

      HideHow to outline the outer group

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

        In the example below, row 6 contains the subtotals for rows 2 through 5, and row 10 contains the subtotals for rows 7 through 9, and row 11 contains the grand totals. To group all of the detail data for row 11, select rows 2 through 10.

        ABC
        1RegionMonthSales
        2EastMarch$9,647
        3EastMarch$4,101
        4EastMarch$7,115
        5EastMarch$2,957
        6EastMar Total$23,820
        7EastApril$4,257
        8EastApril$1,829
        9EastApril$6,550
        10EastApr Total$12,636
        11East Total$36,456

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

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

        Outlook Ribbon Image

        The outline symbols appear beside the group on the screen.

    2. Optionally, outline an inner, nested group.

      HideHow to outline the inner, nested group

      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.

        ABC
        1RegionMonthSales
        2EastMarch$9,647
        3EastMarch$4,101
        4EastMarch$7,115
        5EastMarch$2,957
        6EastMar Total$23,820
        7EastApril$4,257
        8EastApril$1,829
        9EastApril$6,550
        10EastApr Total$12,636
        11East Total$36,456

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

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

        Outlook Ribbon Image

        The outline symbols appear beside the group on the screen.

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

      Note You can also ungroup sections of the outline without removing the entire outline. Hold down SHIFT while you click the Plus box or Minus box 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 rows may remain hidden. To display the data, drag across the visible row numbers adjacent to the hidden rows. On the Home tab, in the Cells group, click Format, point to Hide & UnHide, and then click Unhide Rows.

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.

    HideHow to specify the summary column location

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

      Outlook Ribbon Image

    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:

    HideOutline the data automatically

    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.

      Outlook Ribbon Image

    HideOutline the data manually

    Important When you manually group outline levels, it's best to have all data displayed to avoid grouping columns incorrectly.

    1. Outline the outer group.

      HideHow to outline the outer group

      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.

        ABCDEFGHIJ
        1RegionJanFebMarQ1 AprMayJunQ2 H1
        2East371 504 880 1,755 186 653 229 1,068 2,823
        3West192 185 143 520 773 419 365 1,557 2,077
        4North447 469 429 1,345 579 180 367 1,126 2,471
        5South281 511 410 1,202 124 750 200 1,074 2,276

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

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

        Outlook Ribbon Image

        The outline symbols appear beside the group on the screen.

    2. Optionally, outline an inner, nested group.

      HideHow to outline inner, nested group

      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.

        ABCDEFGHIJ
        1RegionJanFebMarQ1 AprMayJunQ2 H1
        2East371 504 880 1,755 186 653 229 1,068 2,823
        3West192 185 143 520 773 419 365 1,557 2,077
        4North447 469 429 1,345 579 180 367 1,126 2,471
        5South281 511 410 1,202 124 750 200 1,074 2,276

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

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

        Outlook Ribbon Image

        The outline symbols appear beside the group on the screen.

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

      Outlook Ribbon Image

    5. Note You can also ungroup sections of the outline without removing the entire outline. Hold down SHIFT while you click the Plus box or Minus box 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.

Show or hide outlined data

  1. If you don't see the outline symbols one two three, plus, and minus, click the Microsoft Office Button Button image, 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

    • To display the detail data within a group, click the plus box for the group.
    • To hide the detail data for a group, click the minus box for the group.

    Expand or collapse the entire outline to a particular level

    • In the one two three 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 three.

    Show or hide all of the outlined detail data

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

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.

    Outlook Ribbon Image

  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.

    Outlook Ribbon Image

  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.

Copy outlined data

  1. If you don't see the outline symbols one two three, plus, and minus, click the Microsoft Office Button Button image, 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 one two three, minus, and plus 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.

    Excel Ribbon Image

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

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 one two three, plus, and minus, click the Microsoft Office Button Button image, 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 one two three outline symbols.
  3. Click the Microsoft Office Button Button image, 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.

    Outlook Ribbon Image

  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.

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.

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

      Outlined list with only total rows showing

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

  2. 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.

    2. Create the chart.

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

      Chart made from subtotal data

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

0 comments |

Labels

Blog Archive

Powered by Blogger.

I made these pages for me and my friends to help solving the problem we face regarding Computer & internet, if anyone wants me to answer a question or find out about some information please send me email and I will try to reply.*P.S. some of the article I wrote and the other I found on the internet I posted them in sprit of learning and shearing, please forgive me if you found something you don’t want to be in my blog, email me and I will delete them. Thank you for your interest in my pages.امل نجم Amal Nagm

banner 1 banner 2