Use the Group command to show and hide data

Applies to
Microsoft Excel 2000

Instead of constantly updating separate files and worksheets, wouldn't it be great if you could maintain all your data in one worksheet, then show certain details to your boss but hide them from people who may not have a need to know? For example, data about individual sales performance may only be appropriate for supervisors, whereas data about total company sales is important for all employees. By using the Group command, you can maintain one worksheet and hide the granularity or show all your data, depending on your audience.

Scenario: Getting multiple uses from one grouped worksheet

Jim has to prepare sales figures for the company meeting, but his team presentation is scheduled for the same day—and the deadline is fast approaching. He decides to save time and effort by using the Group command in Excel. In this way, he can create one worksheet rather than two, and present the high-level version at the company meeting and the more detailed version at his team meeting. Because he knows data in a shared workbook cannot be grouped, he will not share his workbook.

An added benefit of using the Group command is that it can minimize the risk associated with updating data in multiple files and worksheets. What if Jim forgets to change a figure in one of his files or enters a number incorrectly? Since he only has to maintain one worksheet, he can be confident that the figures will be consistent.

Step 1: Create one worksheet

First, Jim creates a worksheet that shows the comprehensive sales information (with regional information for the first quarter when they sold to the European market). He makes sure that the summary rows and columns are consistently located in the same direction relative to the detail data. For example, he always inserts the rows with the quarter totals per salesperson (summary data) below the monthly figures per salesperson (detail data). This will make it easier when he begins to group the data.

Indent text     Jim wants to indent the months so that the quarters and total stand out. To do this, he selects each cell with the text he wants to indent (in this case, the individual months), and on the Formatting toolbar, he clicks the Increase Indent button. (To save time, he can hold down CTRL while selecting certain cells, and then click the Increase Indent button to indent them all at once.) If he changes his mind and wants to decrease or remove the indentation, he can select the appropriate cells and click the Decrease Indent button.

Jim's worksheet looks like this:

Worksheet with all the information

Step 2: Group data

Jim is now ready to select the first items that he would like to group. Jim is very careful to select only the rows with the detail data. In this example, he selects Rows 2 through 10, since Row 11 contains the quarter totals (or summary data) for those rows:

Worksheet with items selected for grouping

On the Data menu, Jim points to Group and Outline, and then clicks Group. When he is asked if he wants to group rows or columns, in this case he selects Rows and clicks OK. To group the columns with the region and the names of his sales staff, he follows the same procedure, but selects Columns rather than Rows. He continues this procedure until he has all the appropriate groups for his data.

The outline symbols appear to the left of the grouped rows and above the grouped columns on the screen. The outline symbols include the level symbols (small boxes with numbers in them), level bars (), and show (+) or hide (-) detail symbols. (If Jim couldn't see the outline symbols, he would just have to go to the Tools menu, click Options, click the View tab, and then select the Outline symbols check box.) The grouped worksheet with the outline symbols looks like this:

Worksheet with grouping symbols called out

Group and outline automatically     If he had summarized all his data by using formulas and did not need special groups, Jim could have pointed to Group and Outline on the Data menu, and then clicked Auto Outline to group and outline his entire worksheet all at once. (Jim could not use this command because the Region column with its text values does not get calculated into any summary, so it would not get grouped.)

 Note   When the Auto Outline command is used, Excel assumes by default that summary rows are below detail rows and summary columns are to the right of detail columns. If the positions of summary rows or columns are consistently reversed, on the Data menu, point to Group and Outline, click Settings, and then make sure the check boxes in the Direction group are not selected.

Step 3: Hide details

Jim is now ready to prepare the version for the company meeting. He clicks the appropriate hide detail symbols (-) to hide certain items he has grouped. (He could also have clicked level symbol 2 for the rows and level symbol 1 for the columns to show just the quarter totals.) With this streamlined version, he eliminates certain details—and saves individual salespeople a lot of stress. Here is the quarterly summary that he will present to the company.

Worksheet with details hidden

Make sure details remain hidden in electronic versions     Jim wants to send an electronic copy of this version of his worksheet to all company meeting attendees, but he does not want them to be able to see specific details about his team's individual performances. To ensure that hidden details remain hidden in a soft copy of his worksheet, Jim just has to follow a few quick steps. On the Tools menu, he points to Protection, and then clicks Protect Sheet. He selects the Contents check box, enters a case-sensitive password, clicks OK, and carefully reenters the password. (If he did not enter a password, someone could simply unprotect the worksheet to see the details.) After he saves the file, he sends out this protected electronic version.

Step 4: Show details

To encourage some friendly competition at the team meeting, Jim wants another version to show more details per salesperson. Because he had protected the company version, he must first unprotect the worksheet. On the Tools menu, he points to Protection, then clicks Unprotect Sheet, and enters his password. He now clicks the appropriate show detail symbol (+) above the columns. (He could have also clicked level symbol 2 for the columns.) In this way, he quickly expands the data to show how each salesperson did per quarter, along with their totals for the year. Here is the version he will use for his team:

Worksheet with selected details showing

By using the Group command, Jim saves himself a lot of redundant effort and maintenance, as well as easily customizes the worksheet for different audiences.

More information

For more information, type how do I group and outline or outline a worksheet manually in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search. If you would like information on inserting subtotals automatically, type insert subtotals into a list in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.