Group and Outline Excel Data

Excel has some pretty good features for those who know where to find them. Among the easier ones to use are outlining and grouping, which allow you to make large quantities of data minimizable so that complex spreadsheets are easier to navigate. To outline and group data in both newer and older versions of Excel, follow these steps.

Steps

  1. Open the file you wish to work on. Grouping and outlining is extremely useful for making large tables more manageable in size and appearance, but for the sake of simplicity, this demonstration will use a small spreadsheet.
  2. Go to data. In newer versions of Excel, this is a subsection in the ribbon. If you have an older version of Excel that has a toolbar instead of a ribbon, click on the Data drop-down menu.

Outline Automatically

  1. Go to Data > Group > Auto Outline. For older versions of Excel, go to Go to Data > Group and Outline > Auto Outline.
    • When grouping, Excel looks for data sandwiched between logical bookends (i.e. headings at the beginnings and formula totals at the ends), singles it out, and gives you the option of minimizing it. If Excel informed you that it could not create an automatic outline, it’s because your spreadsheet contains no formulas. You will have to group your data manually (skip to Manually Group Data below).
  2. Look at your automatically-outlined spreadsheet. Depending on the complexity and arrangement of your spreadsheet, you will notice that section(s) of your data have become grouped and are now marked by bars along the top and/or left side of the cells. The example spreadsheet is very simple and has only been grouped in one place.
    • In the example outline, that data between the first row (the header) and the final two rows (both of which contain formulas) has been automatically grouped. Cell B7, for example, contains a SUM formula.
    • Click on the [–] button to minimize a group of data.
    • Look at the data that remains. This is usually key information, giving the viewer the option of whether or not to investigate the specifics and making your spreadsheet easier to digest.
  3. Clear the automatic outline if desired. If Excel has misinterpreted your data and you aren’t pleased with the results, you can clear your outline by going to Data > Ungroup (or Group and Outline) and selecting Clear Outline; from here, you manually group and ungroup data (see method below). If it’s close, you may only want to tweak the automatic outline to save yourself time.

Manually Group Data

  1. Select the cells you want to be able to minimize. In this example, we want only the heading and grand total to show after the data is minimized, so we are selecting everything in between.
  2. Go to Data > Group > Group. For older versions of Excel, go to Go to Data > Group and Outline > Group.
  3. When prompted, choose either Rows or Columns. In this example, we want to flatten the data vertically, so we are choosing Rows.
  4. Note the section of data that has been grouped. It is now marked by a bar along the left side or top of the cells.
  5. Click the [-] button to minimize.
  6. Select and group additional areas if desired. Expand the current selection if necessary (by pressing [+]), then repeat Steps 1 through 4 to create new groups. In this example, we want to make a subgroup within the first group that excludes the subtotals, so we are selecting all the cells between the heading and the subtotals.
    • Note the new section of data that has been grouped.
    • Minimize the new group and note the difference in appearance.
  7. To ungroup a section, re-select it and go to Data > Ungroup > Ungroup. For older versions of Excel, go to Go to Data > Group and Outline > Ungroup
  8. To ungroup all sections, go to Data > Ungroup (or Group and Outline) > Clear Outline.

Tips

  • You cannot use this function if the sheet is shared.
  • Often times, the automatic outline feature doesn’t work exactly like you want it to. Manual outlining – that is, creating a series of groups – is sometimes simpler and more customized to the information you have in your spreadsheet.

Warnings

  • Do not use grouping/outlining if you plan to save your excel file to a htm/html page.
  • Do not use grouping/outlining if you plan to protect the worksheet; the user will not be able to expand and collapse the rows!

Related Articles

Retrieved from "https://kipkis.com/index.php?title=Group_and_Outline_Excel_Data&oldid=274229"