Grouping Columns in Excel (Table of Content)

• Excel Grouping Columns
• How to Enable Grouping of Columns in Excel?

Sometimes the worksheet contains complex data, which is very difficult to read & analyze, to Access & read these types of data in an easier way, the grouping of cells will help you out. Grouping of columns or rows is used if you want to visually group of items or to monitor them in a concise & organized manner under one heading or if you want to hide or show data for better display & presentation. Grouping is very useful & most commonly used in accounting & finance spreadsheets. Under the Data tab in the Ribbon, you can find the Group option in the outline section. In this topic, we are going to learn about Grouping Columns in Excel. Shortcut Key to Group Columns or Rows Shift+Alt+Right Arrow is the shortcut key to group columns or rows, whereas Shift+Alt+Left Arrow is the shortcut key to ungroup columns or rows. Definition Grouping of Columns in Excel It’s a process where you visually group the column items or datasets for a better display.

How to Enable Grouping of Columns in Excel?

Let’s check out how to group columns & How to collapse & expand columns after grouping columns. You can download this Grouping Columns Excel Template here – Grouping Columns Excel Template

Example #1 – Grouping of Columns in Excel

Grouping of columns in Excel works out well for structured data where it should contain column headings, and it should not have a blank column or row data. Initially, you need to select the column in which you want to group it (i.e. B, C, D, E columns). Go to the Data tab, then click on the group option under the outline section. Click on the columns and then press OK. Now you can observe in data, the columns are grouped perfectly, and the outline bars you can observe at the top represent different levels of data organization. Grouping also introduces a toggle option, or it will create a hierarchy of groups, which is known as an outline, to help your worksheet appear in an organized manner, where each bar represents a level of organization (Grouping is also referred to as Outlines.)

How to Collapse & Expand Columns After Column Grouping

you can press the “-” buttons in the margin to collapse the columns (B, C, D, E Columns completely disappears) or in case If you want to expand them again, press the “+” buttons in the margin (B, C, D, E Columns appears) Another way to access data is the use of 1 or 2 options on the left side of the worksheet, i.e. it is called a state, 1st option is called a hidden state (if you click on it, it will hide B, C, D, E Columns) whereas 2nd option is called unhidden state, it will expand those hidden columns, I.E. B, C, D, E Columns appears

To Ungroup Columns in Excel

Select the columns you wish to ungroup (i.e. the columns which you have previously grouped). On the Data tab, in the Outline group, click on Ungroup command. Click on the columns and then press OK. Now, you can observe data bars & “+” buttons and “-” buttons disappear in the excel sheet once the ungroup option is selected.

Example #2 – Multiple Grouping of Columns for Sales Data in Excel

In the below-mentioned example, the Table contains product monthly sales data from Jan to Dec month, and it is also represented in quarterly & yearly sales. Here the data is structured and does not contain any blank cells, hidden rows, or columns. I don’t want all the monthly sales data to be displayed; I want only quarterly & yearly sales data to be displayed; it can be done through multiple grouping of column options. Initially, I need to select the column which I want to group it; now, let’s select the months (i.e. Jan, Feb, Mar columns). Go to the Data tab in the Home ribbon, and it will open a toolbar below the ribbon, then click on the group option under the outline section; now you can observe in a data, the columns are grouped perfectly. Click on the columns and then press OK. A similar procedure is applied or followed for the month of Apr, May, Jun & Jul, Aug, Sep & Oct, Nov, Dec columns. Once the grouping of the above-mentioned monthly columns is done, you can observe that the columns are grouped perfectly in a dataset. The four outline bars you can observe at the top represent different data organisation levels.

Collapsing & Expanding Columns After Column Grouping

You can press the “–” symbol or buttons in the outline bar to collapse all the month columns; once you are done, you can observe all the month Columns completely disappears, and Positive or “+” buttons in the outline bar appear. Now, the sales data looks in a concise & compact form, and it looks well-organized & structured financial data. In case if you want to check any specific monthly sales data, you can expand them again by pressing the “+” buttons in in outline bar so that again, all the monthly sales data will appear. Another way to access or hide monthly data is the use of 1 or 2 options on the left side of the worksheet, i.e. it is called a state, 1st option is called a hidden state (On a single click on it, it will hide all the month Columns) whereas 2nd option is called unhidden state, it will expand those hidden columns, I.E. all the month Columns appears.

Things to Remember

Grouping columns or rows in Excel is useful to create and maintain well-organized and well-structured financial sales data. It is a better & superior alternative for hiding & unhiding cells; sometimes, it is not clear to the other user of the excel spreadsheet if you use the hide option. He needs to track which columns or rows you have hidden & where you have hidden. Prior to applying grouping of columns or rows in excel, You have to ensure that your structured data should not contain any hidden or blank rows & columns; otherwise, your data will be grouped incorrectly. Apart from grouping, you can also do summarization of datasets in different groups with the help of the Subtotal command.

Recommended Articles

This has been a guide to Grouping Columns in Excel. Here we discussed How to Enable Grouping of Columns in Excel along with Examples and a downloadable excel template. You may also look at these useful functions in excel –

1. Excel Move Columns
2. VBA Columns
3. Freeze Columns in Excel
4. Switching Columns in Excel

If you have a list of data you want to group and summarize, you can create an outline of up to eight levels. Each inner level, represented by a higher number in the outline symbols, displays detail 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.

1. To display rows for a level, click the appropriate outline symbols. 2. Level 1 contains the total sales for all detail rows. 3. Level 2 contains total sales for each month in each region. 4. Level 3 contains detail rows — in this case, rows 17 through 20. 5. To expand or collapse data in your outline, click the and outline symbols, or press ALT+SHIFT+= to expand and ALT+SHIFT+- to collapse.

1. Make sure that each column of the data that you want to outline has a label in the first row (e.g., Region), contains similar facts in each column, and that the range you want to outline has no blank rows or columns.
2. If you want, your grouped detail rows can have a corresponding summary row—a subtotal. To create these, do one of the following:
   • Insert summary rows by using the Subtotal commandUse the Subtotal command, which inserts the SUBTOTAL function immediately below or above each group of detail rows and automatically creates the outline for you. For more information about using the Subtotal function, see SUBTOTAL function.
   • Insert your own summary rowsInsert your own summary rows, with formulas, immediately below or above each group of detail rows. For example, under (or above) the rows of sales data for March and April, use the SUM function to subtotal the sales for those months. The table later in this topic shows you an example of this.
3. By default, Excel looks for summary rows below the details they summarize, but it’s possible to create them above the detail rows. If you created the summary rows below the details, skip to the next step (step 4). If you created your summary rows above your detail rows, on the Data tab, in the Outline group, click the dialog box launcher.The Settings dialog box opens. Then in the Settings dialog box, clear the Summary rows below detail checkbox, and then click OK.
4. Outline your data. Do one of the following:Outline the data automatically
   1. Select a cell in the range of cells you want to outline.
   2. On the Data tab, in the Outline group, click the arrow under Group, and then click Auto Outline.

   Outline 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. To outline the outer group (level 1), select all of the rows the outer group will contain (i.e., the detail rows and if you added them, their summary rows).
      
      

      1. The first row contains labels, and is not selected. 2. Since this is the outer group, select all the rows with subtotals and details. 3. Don’t select the grand total.

   2. On the Data tab, in the Outline group, click Group. Then in the Group dialog box, click Rows, and then click OK.Tip: If you select entire rows instead of just the cells, Excel automatically groups by row — the Group dialog box doesn’t even open. The outline symbols appear beside the group on the screen.
   3. Optionally, outline an inner, nested group — the detail rows for a given section of your data.Note: If you don’t need to create any inner groups, skip to step f, below.For each inner, nested group, select the detail rows adjacent to the row that contains the summary row.
      
      

      1. You can create multiple groups at each inner level. Here, two sections are already grouped at level 2. 2. This section is selected and ready to group. 3. Don’t select the summary row for the data you are grouping.

   4. On the Data tab, in the Outline group, click Group.Then in the Group dialog box, click Rows, and then click OK. The outline symbols appear beside the group on the screen. Tip: If you select entire rows instead of just the cells, Excel automatically groups by row — the Group dialog box doesn’t even open.
   5. Continue selecting and grouping inner rows until you have created all of the levels that you want in the outline.
   6. If you want to ungroup rows, select the rows, and then on the Data tab, in the Outline group, click Ungroup.You can also ungroup sections of the outline without removing the entire level. 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 rows may remain hidden. To display the data, drag across the visible row numbers adjacent to the hidden rows. Then on the Home tab, in the Cells group, click Format, point to Hide & Unhide, and then click Unhide Rows.

1. Make sure that each row of the data that you want to outline has a label in the first column, contains similar facts in each row, and the range has no blank rows or columns.
2. Insert your own summary columns with formulas immediately to the right or left of each group of detail columns. The table listed in step 4 below shows you an example.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.
3. If your summary column is to the left of the detail columns, on the Data tab, in the Outline group, click the dialog box launcher.The Settings dialog box opens. Then in the Settings dialog box, clear the Summary columns to right of detail check box, and click OK.
4. To outline the data, do one of the following:Outline the data automatically
   1. Select a cell in the range.
   2. On the Data tab, in the Outline group, click the arrow below Group and click Auto Outline.

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

   1. To outline the outer group (level 1), select all of the subordinate summary columns, as well as their related detail data.
      
      

      1. Column A contains labels. 2. Select all the detail and subtotal columns. Note that if you don’t select entire columns, when you click Group (on the Data tab in the Outline group) the Group dialog box will open and ask you to choose Rows or Columns. 3. Don’t select the grand total column.

   2. On the Data tab, in the Outline group, click Group.The outline symbol appears above the group.
   3. To outline an inner, nested group of detail columns (level 2 or higher), select the detail columns adjacent to the column that contains the summary column.
      
      

      1. You can create multiple groups at each inner level. Here, two sections are already grouped at level 2. 2. These columns are selected and ready to group. Note that if you don’t select entire columns, when you click Group (on the Data tab in the Outline group) the Group dialog box will open and ask you to choose Rows or Columns. 3. Don’t select the summary column for the data you are grouping.

   4. On the Data tab, in the Outline group, click Group.The outline symbols appear beside the group on the screen.
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.

You can also ungroup sections of the outline without removing the entire level. Hold down SHIFT while you click the or for the group, and then on the Data tab, in the Outline group, click Ungroup. 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

1. If you don’t see the outline symbols , , and , go to File > Options > Advanced, and then under the Display options for this worksheet section, select the Show outline symbols if an outline is applied check box, and then click OK.
2. Do one or more of the following:
   • Show or hide the detail data for a groupTo display the detail data within a group, click the button for the group, or press ALT+SHIFT+=.
   • To hide the detail data for a group, click the button for the group, or press ALT+SHIFT+-.
   • Expand or collapse the entire outline to a particular levelIn 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 dataTo 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 .

For outlined rows, Microsoft Excel uses styles 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 new summary rows or columns

1. On the Data tab, in the Outline group, click the dialog box launcher.The Settings dialog box opens.
2. Select the Automatic styles check box.

Apply a style to an existing summary row or column

1. Select the cells to which you want to apply a style.
2. On the Data tab, in the Outline group, click the dialog box launcher.The Settings dialog box opens.
3. Select the Automatic styles check box, and then click Apply Styles.

You can also use autoformats to format outlined data.

1. If you don’t see the outline symbols , , and , go to File > Options > Advanced, and then under the Display options for this worksheet section, 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.

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

• Go to File > Options > Advanced, and then under the Display options for this worksheet section, uncheck the Show outline symbols if an outline is applied check box.

Remove an outline

1. Click the worksheet.
2. One the Data tab, in the Outline group, click Ungroup and click Clear Outline.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.

Imagine 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 , , and to show only the totals as shown in the following example of a row outline:
   3. 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 chart only the Buchanan and Davolio totals, but not the grand totals, select cells A1 through C19 as shown in the above example.
   2. Click Insert > Charts > Recommended Charts, then click the All Charts tab and choose your chart type.For example, if you chose the Clustered Column option, your chart would look like this: If you show or hide details in the outlined list of data, the chart is also updated to show or hide the data.

You can group (or outline) rows and columns in Excel for the web. Note: Although you can add summary rows or columns to your data (by using functions such as SUM or SUBTOTAL), you cannot apply styles or set a position for summary rows and columns in Excel for the web.

Create an outline of rows or columns

Outline of rows in Excel Online The first column contains labels. The detail rows and summary rows are grouped in the outline. The grand total is not grouped in the outline.	Outline of columns in Excel Online The first row contains labels. The detail columns and summary columns are grouped in the outline. The grand total is not grouped in the outline.

1. Make sure that each column (or row) of the data that you want to outline has a label in the first row (or column), contains similar facts in each column (or row), and that the range has no blank rows or columns.
2. Select the data (including any summary rows or columns).
3. On the Data tab, in the Outline group, click Group > Group Rows or Group Columns.
4. Optionally, if you want to outline an inner, nested group — select the rows or columns within the outlined data range, and repeat step 3.
5. Continue selecting and grouping inner rows or columns until you have created all of the levels that you want in the outline.

Ungroup rows or columns

• To ungroup, select the rows or columns, and then on the Data tab, in the Outline group, click Ungroup and select Ungroup Rows or Ungroup Columns.

Show or hide outlined data

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 for the group, or press ALT+SHIFT+=.
• To hide the detail data for a group, click the for the group, or press ALT+SHIFT+-.

Expand or collapse the entire outline to a particular level

• 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

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

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

Group or ungroup data in a PivotTable

• How to make the most of your learning style
• How to remote control your home computer from anywhere with vnc
• How to price your etsy products
• How to get into law school
• How to get the most out of your ring doorbell