Dynamic arrays webinar - Your questions answered

This morning we held our latest webinar, Dynamic Arrays in Google Sheets and Excel.  You can now watch the recording of this webinar here.

We didn't have time to get to all of your questions during the live webinar, so I'll be going over all the questions - including those we did answer - in this blog post.

The webinar covered both Google Sheets and Excel, so the questions will be ranging across both.  Because dynamic arrays aren't yet on general release for Office 365, but instead require you to opt in to the Insider Program, do remember that you might not be able to check out the Excel parts of these answers yourself.

I get this message when clicking the Google Sheets link: about:blank#blocked

This is likely a corporate policy that's blocking you from accessing Google.  The file is available here.

Will a recording be available?

Yup - the webinar recording and all the resources are available here.

Does Transpose() require the argument area to be a range or will it work on ordinary values in an area?

Any range - whether it's a dynamic array or not - can be flipped using TRANSPOSE.  In fact, this can be used in regular Excel already - although you have to highlight the entire output region first and have to use the Ctrl Shift Enter shortcut to confirm it.  There's also a Transpose option under Paste Special.

Other than only having to type one formula rather than a range of formulas, what are the advantages of using the dynamic array?

The other key advantage is that you don't have to worry about going back to copy and paste more values when things change - so for example a UNIQUE will automatically expand when a novel value is introduced.

Can you still reference the cells within an array? (ie, can I reference eg. the 5th item down in a column?)

Yes, normal cell references still work as well.

In the formulae you are referencing your import array by cell reference. Will this automatically update if the import array expands?

This is related to the Sheets IMPORTRANGE and other functions.  These do not automatically expand - you would have to either use an open reference like C2:C or C:C, or use Excel and a Table.

The "QUERY" concept isn't entirely unique to Google Sheets. It's possible to use SQL to query an Excel range from within Excel (albeit it's a bit fiddly).

This isn't a question, but it's a fair point - you can import database or Azure data, which you could be making from Excel data.  But it isn't as direct and intuitive as QUERY.

In the query function we are writing quite long formula which depend on remembering what is in Column C, D, E etc. Is there any way to link in namming of ranges so rather than selecting C D E and havimg to remember what each is as the formula become more complex we could type CUSTOMER, DATE etc?

For QUERY, how can one use the header of the column rather than the A, B, C.. ?

Sadly, no.  The only way is to use an ADDRESS / MATCH combo to transform the column name back into the column letter, which is more complicated than just using the letter.  Hopefully this will get improved!

Hi David, do these array formula's slow the excel sheet down much? Always found using ctrl shift arrays slowed our sheets down which generally made them unusable?

No, the new calculation engine doesn't seem to add time for simple dynamic arrays.

When I download the "Excel Workbook", most tabs contain "#Name?" errors. Is there a way to easily correct that?

This is due to your version of Excel not having the new dynamic array functions - #NAME! is the error that shows that Excel doesn't recognise a function.  Without being on O365 and opting in to Insider, for now these functions won't work for you.

Are you expecting Excel to introduce QUERY options?

Yes.  This kind of data manipulation and comparison is certainly something I'd expect to see from Microsoft.  Note that many of these kinds of data manipulation are already available via Power Query - so consider looking into that first!

Can you relate this to a real life financial situation?

If I were making a balance sheet or trial balance from a nominal ledger export today, I might make a PivotTable to do it.  I think in the future a dynamic array approach might be easier - for example, it wouldn't need to be manually refreshed the way PivotTables do.

More directly, Sheets has some advantages for analysing data over Excel today - for example the ability to easily and reliably import data from another spreadsheet.

With regard to the overspill error, is there a way to have one or more dynamic arrays (say a combination of unique and importrange) to separate data sources, follow on from another and automatically move down to avoid overspill when the first data source expands?  E.g. importrange formula in cell A1, and another in A10 to different data sources. A1 is ok until it returns more than 9 rows of data. Can you perhaps combine the two formulas in A1 to have the second array follow on automatically from the first regardless of data size? Or perhaps a macro that automatically adds the required new rows as the first array dynamically increases in size?

You can actually do this quite easily so long as the arrays are the same width:

={IMPORTRANGE("1st URL", "1st range"); IMPORTRANGE("2nd URL", "2nd range")}

Can you add formulas into individual cells containing spilled data?

No: In the same way as static data, adding something to a cell that is being spilled into will break the spill and create an error message.

Is is possible to make custom dynamic array formulae via VBA?

No - VBA is no longer being updated and the new dynamic range functionality won't be visible to VBA.  You could write a #-reference formula using VBA for example but you can't make a completely custom dynamic array UDF.

One great feature of pivots is the double click drill down (useful for users that don't 'do' excel). How does Dynamic Array deal with this?

Short one: It doesn't.  Spilled values are just values and aren't tied back in this way.  This includes the QUERY "Pivot" command.

Thanks all for your questions - if you have more, just head over to the Discussions area or make a comment on this post!

Anonymous