Director SumProduct
Share this content

Death of data tables and PivotTables?

There will be some significant ramifications for Excel once the Dynamic Arrays functions and features roll out and become generally available.

16th Oct 2019
Director SumProduct
Share this content

If the “final” versions of Dynamic Arrays highlighted here do not change drastically once they become generally available, there will be some consequences for Excel.

Let me explain.

I begin with a two-dimensional Data Table (ALT + D + T) with an old favourite for this sort of thing, calculating monthly payments on various loan amounts over various durations.

Data table

I have no plans to go through Data Tables here, suffice to say they are a great tool for “what-if?” analysis, albeit they can consume vast quantities of memory. This summary table shows how the monthly instalments would vary for different terms (in years) and different amounts borrowed.

Now, take a look at using three Dynamic Array formulae:

Dynamic Array

Can you spot the difference? In the second table, I have highlighted three cells:

  • G38 contains the formula =SEQUENCE(1,6,10000,10000)
  • F39 contains the formula =SEQUENCE(6)
  • G39 contains the formula =-PMT(Loan_Rate/Months_in_Year,F39#*Months_in_Year,G38#). See how using the Spilled Range Operator (‘#’) makes all the difference?

That’s it! Now I am not saying all Data Tables may be replaced by Dynamic Array formulae, but can you see the future? And guess what, it doesn’t stop there. Let me replicate one feature in Excel many of us are familiar with: the PivotTable…

In this illustration, I have created a 1,200-record Table (CTRL + T):

PivotTable

It’s all made up randomly generated data, and you will just have to guess who I support. The important thing to note is I have created a Table, called Football_Data, so I may add records and the Table will extend automatically.

Next, I created a “Pseudo PivotTable”:

Pseudo PivotTable

This was created using three Dynamic Array formulae (again, highlighted):

  • M12 contains the formula =TRANSPOSE(UNIQUE(SORTBY(Football_Data[Month],Football_Data[Month No]))), which sorts the months into the required order
  • L13 contains the formula =SORT(UNIQUE(Football_Data[Football Club])), which simply sorts the clubs into alphabetical order
  • M13 contains the formula =SUMIFS(Football_Data[Pts Achieved],Football_Data[Football Club],L13#,Football_Data[Month],M12#), which spills out the points earned each month using a standard SUMIFS formula and the Spilled Range Operator (‘#’).

Think about it. I have created a formulaic PivotTable which calculates no discernibly slower than the real thing. However, the source data may be extended, values may change and I don’t need to hit ‘Refresh’. Is this the end for PivotTables?

It’s easy to get carried away. Dynamic Array formulae make league tables a breeze:

PivotTable

However, rather than get side tracked, I’d rather stay “on track” with PivotTables and finish this section unpivoting the PivotTable we have just created (the references have changed as they are on a different worksheet in my example):

Data

Unpivoting can be a nightmare, but it is possible. You don’t need to use Dynamic Arrays to do it, but I will to showcase them:

Data

There is a hidden formula in cell E45. You can see why it is hidden – for those of you with a nervous disposition, please look away now:

=INDEX(SORT(G12#&" - "&F14:F37),ROUNDUP(SEQUENCE(COUNTA(F14:F37)
*COUNT(G12#))/COUNT(G12#),0),MOD(SEQUENCE(COUNTA(F14:F37)*COUNT(G12#))
-1,COUNT(G12#))+1)
.

Oh dear. That’s a horror. Rather than write 1,000 words trying to explain this, let me detail the concept instead. SORT(G12#&" - "&F14:F37) provides every combination of Month Number concatenated with a Football Club, separated by a “ – “ delimiter, e.g.

1 – Aston Villa, 2 – Aston Villa, …, 10 – Aston Villa, 1 – Birmingham City, 2 – Birmingham City, …

The problem is SORT(G12#&" - "&F14:F37) spills this into a 10-column by 24-row array. I want it as a list, so the entire rest of the formula simply forces the array down a column of 240 rows instead. INDEX is used to locate the next record in the array, with contrived formulae to determine the row and column numbers of the virtual grid.

SUMIFS is used to create the points total for each row, and to be honest, simpler formulae could have been used elsewhere too. But that’s my point. As I have written this article, it’s hard not to get carried away with all this and try and do everything in Dynamic Arrays. I have worked for years with Excel and been a keen advocate for keeping everything simple. Dynamic Arrays scare me that we may not help ourselves and write monsters like the formula above.

Maybe Excel’s simpler functions and features will live on after all.

Calculation order concern

If it feels like you have aged a year since you started reading this, you probably have. There’s a lot to get excited about and I have highlighted some of the issues too – many of which I am sure will be ironed out by the time everything becomes Generally Available. However, I am not sure the following concern will be going away any time soon.

When I calculate something in Excel, if I use the same formula, I must get the same answer, right? Well – not necessarily. Consider the following:

Calculations

In the example above, Calculations 1 and 2 are identical but deliver different results (i.e. different #SPILL! errors). Why?

  • In Calculations 1 and 2, both values for Formula 1 and Formula 2 were originally set to 1. This causes no #SPILL! errors
  • In Calculation 1, the value for Formula 2 (cell H13) was then changed to 4 with no error
  • Then, in Calculation 1, the value for Formula 1 (cell H12) was changed to 3. This caused the resultant #SPILL! error in cell K16
  • Next, in Calculation 2, the value for Formula 1 (cell H27) was changed to 3 with no error
  • Then, in Calculation 2, the value for Formula 2 (cell H28) was changed to 4. This caused the resultant #SPILL! error in cell I33.

I am not sure what the solution is for this problem. Technically, #SPILL! is working correctly, but it doesn’t seem right that two results may be generated in this instance depending upon what input I change first. The jury is out on this one.

Word to the wise

As at the time of writing, all the features, functions and error messages are beta features only. They are available to a portion of Office Insiders at this time, but don’t let that put you off. Start getting excited now! Microsoft will continue to optimise these features over the next several months. This means they might change. When they're fully cooked, Microsoft will release them out into the wild, first to all Office Insiders and then finally Office 365 subscribers (this is when a feature is known as “Generally Available”).

The future’s looking bright though.

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.