How to create Excel Pivot Table from a table with hierarchical columns?

Question

In Excel spreadsheet, I have a table with columns: "Epic" and "Stories" and "Tasks". There is another column to enter hours for each task:

| Code | Epic | Stories | Task | Hour |
| 1    | E1   |         |      |      |
| 1.1  |      | St1     |      |      |
| 1.1.1|      |         | Ts1  |10    |
| 1.1.2|      |         | Ts2  |20    |
| 1.2  |      | St2     |      |      |
| 1.2.1|      |         | Ts21 |5     |
| 1.2.3|      |         | Ts22 |2     |
| 2    | E2   |         |      |      |
| 2.1  |      |         |      |      |

Now I want to create a pivot table to show the sum for each epic. For example:

| Code | Epic | Hours | 
| 1    | E1   | 37    | 
| 2    | E2   | xyz   | 
|

How do I achieve this?

Mayukh Bhattacharya · Accepted Answer · 2024-03-14 00:48:42Z

1

There are many of ways of achieving the desired outputs these days, if you are using Modern Versions of Excel, one such way is using Power Query or Newer Excel Functions, which makes it relatively easy.

• Using GROUPBY() if applicable -> works with MS365 Exclusively ^{Beta Version}

• Formula used in cell G1

=VSTACK({"Code","Epic","Hours"},
 GROUPBY(HSTACK(TEXTBEFORE(A2:A10&".","."),
 SCAN(,B2:B10,LAMBDA(x,y,IF(y="",x,y)))),E2:E10,SUM,,0))

Or, If one don't have access to GROUPBY() while writing the formula then needs to enable the Office Insiders, if you are reluctant to enable then could try the following as well:

=LET(
     _Data, A2:E10,
     _Code, TEXTBEFORE(TAKE(_Data,,1)&".","."),
     _Epic, SCAN(,INDEX(_Data,,2),LAMBDA(x,y,IF(y="",x,y))),
     _Uniq, UNIQUE(HSTACK(_Code,_Epic)),
     HSTACK(_Uniq, TAKE(MAP(_Uniq,LAMBDA(x,SUM(--(x=HSTACK(_Code,_Epic))*TAKE(_Data,,-1)))),,1)))

• Or Using POWER QUERY, available in Windows Excel 2010+ and Excel 365 (Windows) --> It is one time approach, hence you dont have to increase range or ranges, only when you add new data to the original source have to refresh the imported table, and it gets updated within few.

To use Power Query follow the steps:

First convert the source range into a table and name it accordingly, for this example I have named it as Table1.

Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query

The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Epic"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Filled Down", {{"Code", each Text.BeforeDelimiter(Text.From(_, "en-US"), "."), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Text Before Delimiter", {"Code", "Epic"}, {{"Hours", each List.Sum([Hour]), type nullable number}})
in
    #"Grouped Rows"

Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

answered Mar 14, 2024 at 0:48

Mayukh Bhattacharya

29.2k9 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

xbmono Over a year ago

This is nice! The power query seems to be what I exactly needed and your query worked on the first attempt... thanks!

xbmono Over a year ago

Just a question, using PQ, does that automatically update the result if we make changes to the table? It doesn't seem to be doing that or am I missing something?

Mayukh Bhattacharya Over a year ago

Yes it does actually you need to right click on the imported table from PQ to refresh I have already mentioned that.

xbmono Over a year ago

Oh sorry... it's a new thing for me and I missed it... thanks

Mayukh Bhattacharya Over a year ago

No worries at all. Sorry for the late reply I am done for the day.

teylyn · Accepted Answer · 2024-03-14 00:00:08Z

0

You need to normalise the source table so that each row has all the values. You could copy the values into the blanks from the rows above with a few clicks.

Select the table
hit F5 or use Ctrl G or Goto
click Special
Tick Blanks and hit OK
type a = sign and hit the up arrow. This creates a formula that references the cell above
hold down the Ctrl key and hit Enter. Now that formula is in all the previously blank cells
Copy the table again and paste over itself as values.

You should delete any rows that don't have values in the hours column, since they don't add any usefull information to the table.

Now you can build a pivot table based on this source table, sum the hours and aggregate by Epic.

answered Mar 14, 2024 at 0:00

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

2 Comments

xbmono Over a year ago

Normalising isn't really an option... the data I shared is a very simplified version, in fact there are nearly 20 other columns with different hours in there and I can't copy everything everywhere. Besides, different team may need to add more rows and more epics and this table is very dynamic. Thanks for the response anyway.

teylyn Over a year ago

Well, then you can't create a pivot table from it. You could try loading into Power Query, clean it up and load it back into another worksheet or the Data Model, then create a pivot table, but if you want to pivot the data, you have to get rid of all the blank cells.

Collectives™ on Stack Overflow

How to create Excel Pivot Table from a table with hierarchical columns?

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related