Inserting nested levels of subtotals

Hello,

You can achieve the same thing with more flexibility using Pivot tables.

To do this, just give names to your columns like Level1, Level2, etc.

and choose 

Insert -> PivotTable

On Row labels, drag all of your columns and on Values field drag any one of the levels, example Level1 

Now, go to Design -> Report Layout -> Show in Outline Form

That's it!

Let me know if you find this helpful!

To my June 26 post requesting advice on using Excel's subtotaling function, I got two excellent responses, overnight. If this is typical of this Community, I'm certainly glad I joined!

Both responses recommended using pivot tables instead of subtotaling, and provided what looks like more than enough guidance/references to get me to a good place with that.

Meanwhile I found a work-around using subtotaling, and I'm short of time, so I'm going to go with that for now and later on investigate the undoubtedly better, more sophisticated option offered by pivot tables. I'll feel free to ask further questions then.

Thanks, Bek_from_Uzbekistan and Rohn007, for your most valuable help and encouragement!

I was going to suggest Pivot Tables also. There are different styles of pivot table layouts that you can apply to make them look closer to your example.

If you want a more specific demonstration, provide us with some simple sample data, 2 or 3 rows for each category, subscategory and we can show you how to do it in PivotTables.

.

If your data looks like the example table, we will have to start by "unpivotting it"

Switch Data in Columns to Rows in Excel (Unpivot in Power Query)

https://www.xelplus.com/excel-powerquery-transpose-unpivot/

This will be another tutorial on transposing horizontal data into vertical data.

But this technique has a twist. No, wait!  TWO twists!

Twist 1: we don’t want to change ALL the columns in our data; only certain columns.

Twist 2: we don’t want to lose the empty cells after the transposition; we want to see them as zeroes in the final version.

We will solve this problem using the “Get and Transform” tools located on Excel’s Data tab.

.

@ The Definitive Guide to Unpivot with Power Query in Excel

#1 Transform Already-Pivoted Tables To PivotTable         2016 01 03   Gil Raviv

https://datachant.com/2016/01/03/transform-already-pivoted-tables-to-pivottable-the-definitive-guide-to-unpivot-with-power-query-in-excel/

This post is the first in a series of posts that will walk you through one of the coolest data transformation features in Excel. If you are not familiar with Power Query Add-in for Excel, or never had the time or energy to start learning it, this series of posts will convince you to start.

But let’s start with the big and common challenge. As an advanced Excel user, you prefer to analyze or create your reports with PivotTables and PivotCharts. Unfortunately, many of your colleagues are not so familiar with it, and prefer to share with you their static tabular data, or summaries. You get their static tables, but cannot convert it into PivotTable and shape it to meet your analysis needs.

.

Here are couple of introductory articles to give you an idea of what can be done with pivot tables

.

! Create a Pivot Table in Excel (Intro)https://www.contextures.com/CreatePivotTable.html

Follow these easy steps to create an Excel pivot table, so you can quickly summarize Excel data. Watch the short video to see the steps, or follow the written steps. Get the free workbook, to follow along. There's also an interactive pivot table below, that you can try, before you build your own!

.  *  Video: Create a Pivot Table

.  *  Preparing Your Pivot Table Data

.  *  Creating a Pivot Table

.  *  Modifying the Pivot Table

.  *  Try This Pivot Table

.  *  Download the Sample File

.  *  Pivot Table Tools

.  *  More Pivot Table Tutorials

NOTE: This tutorial gives you a quick overview of creating a pivot table. For detailed tutorial, go to the How to Plan and Set Up a Pivot Table page.

.

How to Set Up an Excel Pivot Table (Advanced)

https://www.contextures.com/excelpivottablesetup.html

It’s easy to create a pivot table, with a few mouse clicks. However, the hard part, especially at first, is deciding what goes where. If you aren’t sure where to begin, these steps will help you set up an Excel pivot table.

.  *  Before You Build a Pivot Table

.     1. Check the Source Data

.     2. Set a Goal

.     3. Think About the Layout

.  *  Create a Quick Pivot Table

.  *  Open the PivotTable Field List

.  *  Add Field to Pivot Table Layout

.  *  Remove Field From Layout

.  *  Add More Fields

.  *  Move the Fields

.  *  Add a Filter Field

.  *  Keep Experimenting

.  *  Download the Sample File

.

!Create a PivotTable to analyze worksheet datahttps://support.office.com/en-us/article/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576

A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data..Use the Field List to arrange fields in a PivotTablehttps://support.office.com/en-us/article/use-the-field-list-to-arrange-fields-in-a-pivottable-43980e05-a585-4fcd-bd91-80160adfebec

After you create a PivotTable, you'll see the Field List. You can change the design of the PivotTable by adding and arranging its fields. If you want to sort or filter the columns of data shown in the PivotTable, see Sort data in a PivotTable and Filter data in a PivotTable.

.

Group or ungroup data in a PivotTablehttps://support.office.com/en-us/article/group-or-ungroup-data-in-a-pivottable-c9d1ddd0-6580-47d1-82bc-c84a5a340725

Grouping data in a PivotTable can help you show a subset of data to analyze. For example, you may want to group an unwieldy list of dates or times (date and time fields in the PivotTable) into quarters and months, like this image.

.  *  Group data

.  *  Group selected items

.  *  Name a group

.  *  Ungroup grouped data

.