ungrouping Excel data saved as an Outline as a pivot table

Question

I do not know if it is possible to do what I want to achieve in Excel, but I have the data that is generated in the Outline (let's assume: on the left side of the screen I have a "plus", if I press it, I have another "+" and I get to the next detailed data. The problem is, when I ungroup it all, I have every item in column "A" and I can't make it into a PivotTable.

What I mean?

Example (you can see it in the photo).

1 "plus" - country:
[![enter image description here][1]][1]2 "plus" - city:
3 "plus" - brand of items sold
4 "plus" - exact equipment from this brand

When I ungroup it, everything is in one column and it should be - respectively - 1 "plus" - column A, 2 "plus" - column B, etc.

Is there any possibility to ungroup it according to the "pluses". I hope there is any sollution... Thanks

Answer 1

See https://www.mrexcel.com/board/threads/calculating-outline-levels-in-excel.227279/#post-1112028 copied here:

Select cell A1 of the sheet,

Do menu command Insert/Name/Define, and create a name called DetailLevel, with a formula ( in the Refers To box ) of:

=GET.CELL(28,Sheet1!A1)

( alter the sheet name Sheet1 to whatever your sheet is called )

Now in any blank column of the sheet type the formula =DetailLevel and copy down.

That will give you 1 for no indent, 2 for level 2, 3 for level 3

bring that along as data column to create pivot table