![]() It will automatically include new row labels like a PivotTable, and update its values all hands-free (no manual refresh) like a formula-based report. Now, here is a DA report that combines these two pros without inheriting the two cons. This doesn’t take long, but, it is a step I need to remember to do, and if I forget the report will be inaccurate. The con is that I need to remember to manually Refresh the report. ![]() The pro is that all new transactions are reflected in this report when I refresh it, even if a new report label (row item) is found. To create it, I used Insert > PivotTable, and added the items fields to rows and the amount field to values. On the other hand, here is the PivotTable version of that report: The con is that when there is a new item, such as ZZ400, we need to insert a new worksheet row, type the new label, and then write or fill the formula to compute its value. The pro is that any new transactions in my data table are automatically included in the report provided their label is found in the report. To create it, I manually entered the report labels and then used the SUMIFS function to create the values. Here is a screenshot of a traditional formula-based report. It is just another option that can help in some of our reports □ Objective However, I wanted to talk about VSTACK/HSTACK with this set up so that we can see one really cool way to apply them. ![]() I am saying that it avoids two of the cons noted above, mainly, formula reports don’t include new items and you need to manually Refresh a PT after you change table values. I’m also not saying that there are no cons to the approach presented. Let’s go.ĭisclaimer: I am not suggesting that we stop using PivotTables, or that DA functions can replace all PivotTables. And, I’ll walk through all of the details in this post. Probably, the most beautiful report of all. With the introduction of VSTACK and HSTACK, we are able to build such a report. Well, friends, this wish has finally come true! A formula-based report that would automatically recalculate values and dynamically expand to include new report labels (items). On the other hand, we could create a PivotTable that automatically includes new items … but … we would manually Refresh.įor years, I’ve wished for the best of both worlds. On 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. We would need to make a choice between these two options based on our workbook. OptionsĪnd, these have been our two basic options for literally decades. The only little problem is that users need to remember to manually click Refresh after changing the source data in the workbook. That is awesome and one of the many reasons Excel users adore PivotTables. ![]() That is, a PivotTable will automatically insert new report rows for any new items found in the underlying data. PivotTable reports do automatically adjust their size to accommodate any new items found. This is why PivotTables have been such an amazing alternative to formula-based reports. The inability to auto-expand to include new items (new report label rows) has been a major bummer. Meaning, if there is a new report label needed (found 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. But, the problem is that they do not automatically expand to include any new items found in the updated data source. Since the report values are computed with formulas, they automatically recalculate anytime the dependent cell values changed. One approach to building reports is to enter the report labels into cells, and then write formulas that compute the report values. We need to understand these details in order to fully appreciate the beauty of the new solution that VSTACK/HSTACK enables. No manual Refresh needed! Formula-based vs PivotTable Overviewīefore we jump in, let’s just back up a sec and recap the key pros and cons of traditional formula-based reports and PivotTables. Specifically, we’ll write a DA formula with the new VSTACK and HSTACK functions to create a dynamic PivotTable-style report that automatically updates values and expands to include new items. In this post, I’ll show you a new option that use a dynamic array (DA). We’d have to pick the report type based on the context of our workbook. We could enter the report labels and use formulas to compute the report values or we could use a PivotTable. Historically, we’ve had two basic ways to create reports in Excel.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |