This worksheet contains several hundred rows of sample order data in an Excel Table called “data”. I’ll first build a pivot table to summarize this data by Color and Region. I’ll keep the pivot table on the same sheet. Then I’ll add Color as a row label, Region as a column label, and Total as a value field. I’ll also set Total to display currency. Finally, I’ll turn off Grand Totals, and disable Auto-Fit, to keep things from moving around. As always, a pivot table like this is very quick and easy to build. Now let’s make a similar table with dynamic array formulas. First, I’ll use the UNIQUE function to extract a list of colors. To match the pivot table, I’ll also sort these values with the SORT function. Next, I’ll do the same thing with Regions. Here again, we want unique values that are sorted. Now, to get these labels to run horizontally, I need to wrap the whole formula in the TRANSPOSE function. Now we’re ready to calculate totals and for this, we’ll use the SUMIFS function. The sum range is Total. Criteria range 1 is the color column, and the criteria is the dynamic array that contains unique colors. Criteria range 2 is region, and the criteria is the dynamic array that contains regions. Notice the hash characters here tell us that we are referencing the entire spill range for both colors and regions. When I enter the formula, SUMIFS calculates the subtotals and delivers all results in a single dynamic array. I’ll format these values as currency, to match the pivot table. I’ll also add just a bit of formatting to the table Now you can see we’ve got a summary table that’s very similar to the pivot table. And one advantage of the dynamic array version is that it responds instantly to any changes in the data. In contrast, the Pivot Table must be manually refreshed. On the other hand, pivot tables still have significant advantages. Pivot tables maintain formatting, unlike dynamic arrays. Also, pivot tables let you easily add and remove Grand Totals, and they make it easy to experiment with different options, all without writing any formulas. However, in cases where you want instantly updated information, dynamic array formulas are a great new option.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
