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?

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

---

Answer 2

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.