Advanced Excel Tips: How to Create a Dynamic PivotTable Style Report with One Formula

Advanced Excel Tips: How to Create a Dynamic PivotTable Style Report with One Formula | Excel University
Historically, we've had two basic ways to create reports in Excel. We could use formulas to compute the report values or we could use a PivotTable. Both options had pros and cons, and we'd have to pick the type of report based on the context of our workbook. In this video, I'll show you a new option that provides the pros of both options and the cons of neither. It uses the new VSTACK and HSTACK functions to create a dynamic PivotTable-style report with a single formula that auto-refreshes values and expands to accommodate new items hands-free (no manual Refresh).

Let’s just back up a sec and recap the pros and cons of the traditional formula-based reports and PivotTables. We need to understand these details in order to fully appreciate the beauty of the new solution that VSTACK/HSTACK enables.

Formula-based reports

One approach to building reports is to enter the report labels into cells, and then write formulas that compute the report values. Since the report values are computed with formulas, they automatically recalculate as the dependent cell values change. But, the problem is that they do not automatically expand to include any new items found in the updated data source. Meaning, if there is a new report label in the underlying data, you would need to manually insert a new worksheet row into your report, enter the new label, and then write or fill the formula to compute the value. The inability to auto-expand to include new items has been a major bummer. Which is why PivotTables have been such an amazing alternative to formula-based reports.

PivotTable Reports

PivotTable reports do automatically adjust their size to accommodate any new items found (ie, automatically create a new report row for any new items found in the underlying data). Which is awesome and one of the many reasons Excel users adore PivotTables. The only little problem is that users need to remember to manually click Refresh after changing the source data in the workbook.

Options

And, these have been our two basic options for literally decades. We would need to make a choice between these two options based on our workbook. One the one hand, we could create a formula-based report that recalcs the values hands-free, but, we would need to manually insert new report rows and fill formulas. On the other hand, we could create a PivotTable that automatically includes new items, but, requires a manual Refresh.

For literally years, I’ve wished for the best of both worlds. A formula-based report that would not only automatically recalculate values, but also dynamically expand to include new report labels (items).

Well friends, this wish has finally come true!

With the introduction of VSTACK and HSTACK, we are able to build such a report. Probably, the most beautiful report of all.

Note: VSTACK/HSTACK are not available in all Excel versions. For more information about availability:

[ladymaeoptimista]

This channel is so underrated. Amazing content!

[Jim-zmfw]

Hi Jeff: Thank you so much. I really appreciate your energy and delivery style (and of course the content). I am just beginning to dip my toe in the LET ocean.

[wayneedmondson]

Nice one Jeff! Thanks for the detailed demonstration and walk through. Thumbs up!!

[dennisd]

Beautiful, these new functions seem to have enormous possibilities once we learn to use them.

[jazzista]

Great demonstration of the new excel functions HSTACK AND VSTACK. At Min 10 50 I said to myself: I am going to try that example using the LET function.. and and seconds later there it was: the LET function which in my opinion using the LET is cleaner and more elegant solution overall specially to troubleshoot the formula later . The drawback of this technique is that those new formulas have not been rolled to all excel users yet. Thanks for posting Jeff

[g.muthukumar]

Very thank you for this video, it is needed for me long time but today you solved my problems

[kyransom]

This is great! How would I sort by the amount column (largest to smallest)???

[George-lpqb]

Great Demo, but the drill down is one of the pros in pivot table that cannot be replaced

[minjon]

Amazing video, thank you for sharing. The solution I have been looking for for some time, but had no clue. 👏💙

[ronaldjeffersonjr]

Awesome video and I learned a ton. My use case is a bit different and I'm having a problem. I have a column that I used to get a sorted number of unique values. I want to count how many time those unique values appear in another column. I do a countif function but I can't get the countif functions to continue down if I insert another unique value. I'm closer after this video, but the use case is slightly different.

[izzatkiswani]

Thanks Jeff for Nice & Clear Explanation !!!

I have question ...

How to create pivot table based on single cell report, keeping the source for PT Dynamic ???

Thanks In advance .

[aarbeemusic]

I loved it, how you explain, please keep this up. You owe me one haha 😂.

Keep rocking.

[SambulatTV]

hi Jeff! Are you still answering comments?

[yvonne]

Amazing share! is there any change I can sort the table from biggest to smallest based on the Amt? Thank you!!

[joboriahmed]

Could we send it by email after we create it?

[iamekd]

Hi jeff, let's say the itemnum has now change to month which contain past and future, I want to add a line where the month is this month to seperate the tense, how can I do that with conditional formatting, what is the formula?

[justTV]

Thank you,
But why your voice like Jimmy Fallon 😅