In our recent newsletter, we unraveled the enchanting world of Office 365's dynamic array formulas, introducing three magical functions:
- UNIQUE Function: This function conjures a list of unique entries from a spreadsheet range based on one or more columns.
- SORT Function: Bypassing the conventional "sort" option, this function allows you to sort data dynamically using a formula.
- FILTER Function: Say goodbye to the "filter" option on the data tab; this function lets you filter data with a formula.
As we delve into the realms of dynamic arrays, we discover their inherent magic, especially when accompanied by the mystical # sign. If you transform the data into an Excel table, these functions seamlessly adapt to changes or additions in the original data. To embark on this magical journey, select the data and invoke the keystroke combination CONTROL + T.
Let's illustrate this with an example. In Figure 1, we've transformed the range B4:B9 into a table named "people." If we add a new name like "Rebecca" to cell B10, the formula =UNIQUE(people[Names]) in cell E4 will enchantingly populate cell B9 with the newly added name, Rebecca.
Dynamic Arrays and the # Sign: A Magical Symphony
Dynamic arrays, with the # sign as their guide, seamlessly incorporate new data when an Excel table undergoes changes. Now, let's harness this magic to craft the equivalent of a Pivot Table that dynamically updates without the need for a manual refresh.
Creating Pivot Tables Without the Hassle of Refreshing
The "Pivot Table" worksheet in our Excel workbook (see Figure 2) harbors data on 208 transactions, including month, product, country, and revenue. Our aim is to compute total sales for each product in every country, with automatic updates as fresh data emerges.
After transforming the cell range A2:D210 into a table named "sales," three wondrous formulas orchestrate the calculations:
- In cell F16, the formula =UNIQUE(sales[Product]) manifests a list of all products.
- Moving to cell G15, the formula =TRANSPOSE(UNIQUE(sales[Country])) unfolds a list of all countries.
- Finally, entering the (AMAZING) formula =SUMIFS(sales[Revenue],sales[Product],F16#,sales[Country],G15#) in cell G16 computes the indispensable totals. No need to copy the formula! The use of F16# and G15# ensures that total revenue is computed for each product-country combination.
Remarkably, as demonstrated in Figure 3, if new data enters the scene, the revenue for any novel product-country duo seamlessly integrates into our calculations. For instance, in row 211 of the "Pivot Table (2)" worksheet, revenue of 2000 for "Chip 12" in "Mexico" effortlessly becomes part of our dynamic calculations.
A Sneak Peek into the Future: Filled Maps and Power Maps
Our magical journey through the realms of Office 365's dynamic arrays doesn't end here. In the next installment, we will unravel the artistry of creating exquisite maps using Filled Maps and Power Maps.
Let the # sign be your mystical guide as you explore the wonders of dynamic arrays in Office 365. The ability to seamlessly adapt to changes, coupled with the enchanting # sign, transforms data manipulation into a truly magical experience.
The content shared in this article serves informational purposes only and does not constitute tax advice. For advice tailored to your specific situation, consult with your tax advisor.