The purpose of structuring is to split the data contained in a worksheet into specific levels of detail. By using structure, it becomes easier to analyze and compare data.
If there is a strict dependency between the data, MS Excel allows you to automatically create a structure: in this case, MS Excel searches for cells that contain formulas summarizing the information in rows and that are located on the left. The data must be consistent in one direction. To perform automatic structuring, all detailed columns must be on one side of the total columns, and all detailed rows must be positioned relative to the totals either only below or only above them. If this condition is not met, the structure must be created manually.
A worksheet can contain only one structure, although it can be divided into several parts.
Showing and hiding structured data can affect parts of the worksheet that are not included in the hierarchy, since rows collapse and expand across the entire width of the worksheet, and columns — across the entire height of the worksheet.
When a structure is displayed, special symbols appear along the left and top edges of the worksheet. These symbols are used to show and hide levels of detail.
Table. Outline Symbols
| Outline Symbol | Purpose |
| Button to show detailed data | Expands details |
| Button to hide corresponding details | Collapses details |
| Level numbers | Sequential levels for rows and columns |
| Outline level | All detailed rows or columns of one level |
To automatically create a structure, you should:
- Check that the total formulas contain references to detailed data located in one direction relative to the totals;
- For structuring part of a worksheet, select the desired cell range; for structuring the entire worksheet, select a single cell;
- Use the Create Outline command, choosing it from the Group list located in the Outline group on the Data tab of the ribbon.
To manually structure a worksheet, you need to:
- Select the necessary cells of rows and columns to be grouped into a structure, excluding the cell with the total formula;
- Use the Group command from the Group list located in the Outline group on the Data tab of the ribbon;
- In case of errors or to ungroup data, select the Ungroup command from the Ungroup list located in the Outline group on the Data tab of the ribbon;
- To show or hide structured data, use the Show Detail and Hide Detail commands, also located in the Outline group on the Data tab of the ribbon;
- To return the worksheet to its original state, use the Clear Outline command from the Ungroup list located in the Outline group on the Data tab of the ribbon.
For structured data, it is also possible to create charts based on specified outline levels.