EXCEL PRO TIP: Pivot-Style Reports with Formulas

[@wayneedmondson1065]

Hi Chris.. thanks for the video. One thing worth mentioning is that the Pivot Table method will adjust based on the applicable Row Labels for Country per the Genre selection vs. the formulas which are fixed against a static list of Country labels. In this example, you would want to have the full universe of unique Country possibilities in your formula table and then trap for #DIV/0! errors on AVERAGEIFS for those with no match. A small matter to solve and I realized that you were just using this as an example to illustrate the point. Pivot tables are pretty innately clever.. just need to carry that over when using formulas. When dynamic array formulas are finally introduced, this will be even easier to accomplish USING FILTER, SORT, UNIQUE, etc. to set up the various criterion categories as variable according to selection.. so.. the Country list would change depending on the Genre selection and spill vertically accordingly.. just like a Pivot Table. Many thanks for all the great videos, blog posts, courses, etc. in 2019.. I leaned a lot.. and had fun doing it! Looking forward to more in 2020. Thumbs up and Happy Holidays!!

[@ExcelMaven]

From a pure flexibility standpoint PivotTables are hard to beat, and certainly the way to go for quick ad hoc analysis or data exploration. The conditional stats function approach tends to work well for pre-defined reports and dashboards where you need a bit more flexibility from a formatting and layout standpoint. Agree that the new dynamic array formulas will make this approach much more dynamic -- excited to see those go to GA (hopefully soon!). Happy holidays Wayne!