How to Group Rows and Columns With Outlines and Subtotals in Excel

If you are a professional data analyst, you are likely to handle multiple worksheets in Excel workbooks. There are thousands of columns and rows in an Excel file, making it difficult to extract meaningful data.

However, Excel features like Outline, Group data, and Subtotals in a list of data are essential to analyze business data and present impressing reports to the audience or client.

How to Format Your Data to Group Columns in Excel

Before going further to create stunning data visualization using the Excel subtotal function and Excel auto group, you need to restructure raw data.

To add a subtotal in Excel data, first, you’ll need to sort the data list. Then, you can use the Excel Sort function as follows:

1. Click on any column header that you want to sort.
2. On the Ribbon of the Home tab, look for Editing.
3. Now, click on Sort & Filter and then choose Sort A to Z, Sort Z to A, or Custom Sort, as you see fit.

Related: How to Sort by Date in Excel

When you want to group columns in Excel, ensure that the worksheet contains datasets with unique column headings, no empty columns or rows, and subtotals for each row subsets. After restructuring, your data should look like as shown on this image:

You can download this free Excel worksheet to practice the Outlines and Subtotals function before working on real-life data.

How to Add Subtotal in Excel Datasets

Excel subtotal function allows you to calculate grand totals and subtotals for a column in a list of data. You don’t need to format thousands of columns and rows as Excel will restructure the data list automatically.

Excel's Subtotal function automatically groups data, and you'll be able to use functions such as COUNT, SUM, AVERAGE, Max, Min, Product, Count Numbers, StdDev, etc. To add subtotals to your Excel worksheet, follow these steps:

Related: Excel Formulas That Will Help You Solve Real Life Problems

1. Sort the worksheet data according to the column heading for which you want to add subtotals.
2. In the current example worksheet, the Category column is the basis for data sorting.
3. Now, select any cell within the data range.
4. On the Ribbon, click on the Data tab.
5. On the Outline section within the Data tab, click on Subtotal.
6. Excel will select the entire dataset within the worksheet, and you’ll see the Subtotal dialog box.

7. On the Subtotal dialog box, choose the desired column by clicking on the drop-down menu under At each change in.

8. Now, you need to set the Use function field by selecting one option from the drop-down field. You can choose anything between SUM, AVERAGE, Max, Min, etc.

9. Excel will put the calculated subtotal in the cell which you choose under Add subtotal to.

10. Click on Ok, and Excel will automatically add the Subtotals along with the Outlines.

The automated outlined groups will show subtotals under each group, as per your column heading selection. For example, in this tutorial, food items categories like Bars, Cookies, Crackers, and Snacks are separate groups. Each group’s sum price is available on the Total Price (USD) column.

How to Group in Excel to Visualize Nested Data

Excel allows you to create an outline that consists of up to eight levels. It is a frequently used tool when you need to summarize a long list of data as a group of individual headers. The leftmost or the outer outline starts with the Number 1 (One), and as you go to the right, it can go up to Number 8 (eight).

You can quickly visualize nested data as a group or summary data or even reveal each group’s details using the Excel group rows or the outline tool. Excel enables you to create the Outline of columns, the Outline of rows, and the Outline of both rows and columns.

Excel Auto Group to Create an Outline

When you need to organize smaller nested data lists, you can use the Outline tool to create automatic groups. You can try these steps:

Related: How to Use IF Function With Nested Formulas in Excel

1. Click on any cell within the row data that you need to group.
2. Now, go to the Data tab on the Excel Ribbon.
3. Within the Outline section, you’ll find Group drop-down menu.
4. Click on Group and then select Auto Outline.

You’ll notice that the group function organized the rows perfectly. You’ll also see the Outline bars on the left-hand side representing multiple data level structures.

You can explore multiple visualization options by interacting with the Levels button like Number 1 (One), Number 2 (Two), Number 3 (Three), etc.

1. If you select the lowest level, Number 1 (One), you’ll see minimum data within the worksheet. In the current example, it’s the Grand Total cost of all food items.

2. When you click the next level, you’ll only see the Subtotal rows along with the Grand Total row. You won’t see other detailed data lists within the Excel worksheet.

3. You can now select the highest level or Number 3 (Three) in current example data to expand all rows and columns along with the outlined view.

You can also use the above steps to group columns in Excel. Format the datasets accordingly so that the Excel auto Outline command can recognize the subtotals.

Excel Group Command to Group Rows Manually

When the worksheet contains complex datasets like more than one level of information, you need to use the Group command for manual grouping.

1. Select one subset of data as you desire to include in a group and then click on the Group command in the Data tab of Excel Ribbon.
2. To create another group with another subset of data, select the rows and then either click on the Group command or use this shortcut key Shift + Alt + Right Arrow.

3. If you want to create a nested group, choose all detail rows inside the related summary row and then use the shortcut Shift + Alt + Right Arrow.

You can hide detailed rows using the Excel collapse rows option: the Minus (-) sign on the outlines to simplify complex nested data. When you collapse one outline bar, the Minus (-) sign changes to the Plus (+) sign to unhide rows later.

You can additionally use the Show Detail and Hide Detail buttons on the Outline section of the Data tab on Excel Ribbon.

There are in-built styles for groups and outlines within the Excel worksheet. To apply Styles, click on the dialog box launcher icon (a small drop-down arrow) in the Outlines section of the Data tab.

You’ll see a Settings dialog box. Click on the check box for Automatic styles and then click on the Apply Styles button.

Apply a Professional Touch to Your Excel Reports

With this Excel tutorial on Excel Subtotals and Outlines (groups), you will be able to organize complex data into an easy-to-understand table. Using Excel worksheets, you can view nested data from multiple perspectives to extract valuable data intelligence.

About The Author