Excel 365’s data sources now include dynamic arrays

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Excel 365 for Windows is getting another option for importing data into PowerQuery, from a dynamic array.

In many cases the original data source could be imported, sorted and filtered in many different ways.  Importing a existing dynamic array is a useful extra trick for us Excel nerds. We can think of three uses right away:

  • Manipulating and transforming an array using the many options in PowerQuery.
  • Exposing the dynamic array as a data source for other workbooks, more on that below.
  • Converting a dynamic array into an Excel Table (a workaround but could be useful if a Table feature is desperately needed).

PowerQuery can already import a worksheet table or named range which can be a useful workaround to use the wonderful PowerQuery options. See Solved! The problem with Excel Tables and Transpose for just one example.

Microsoft announced this new option in a typically terse and somewhat unhelpful single sentence .

“You can now import, shape and refresh data from dynamic arrays in the current workbook.”

No mention of what’s doing the importing or where the feature is.  Even just adding the word ‘PowerQuery’ would be helpful.

We dug around the current Insiders release and found it hiding under Get & Transform.  The current ‘From Table/Range’ is renamed ‘From Sheet’ and includes a mention of array.

Data | Get Data | From Sheet tooltip in current Excel 365 (left) and Insiders Preview (right)

Once you’ve solved Microsoft’s little game of ‘hide the feature’, the rest is simple.

Select a cell inside the dynamic array, it doesn’t have to be the top-left starting cell.

Then choose Data | Get and Transform Data | From Sheet. 

PowerQuery will open and import the array.  Then you can do all the usual things with PowerQuery before either saving the connection or loading the results back into the workbook.

Using dynamic array in another workbook

As we mentioned earlier, importing an array into PowerQuery makes the array available to other workbooks. All Excel data connections can be linked to by another workbook.

Open another workbook and choose Data | Get & Transform | From File | From workbook, select an Excel workbook. PowerQuery will display all the available connections, tables and sheets including the connection we made from dynamic array ‘Passing Grade students only’.

A query sourced from a dynamic array, visible as a data connection from another Excel workbook.

It’s a neat trick. Instead of importing an entire table then filtering and sorting (possibly duplicating work done in the source workbook), now you can just import the finished list.

Updating isn’t automatic

Remember that Excel queries do not automatically update like normal recalculations or dynamic arrays.  The query must be refreshed after the array has changed, otherwise the two will be out of sync.  See Excel data connections auto refresh tricks.

Or use this little VBA code to force an update to all data connections whenever there’s a change in the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Application.CutCopyMode = False Then
      ‘ do something like Workbooks(ThisWorkbook.Name).RefreshAll
   End If
End Sub

.RefreshAll will update all data connections. If that’s too much of a load, there’s an alternative which only updates a single connection.

If you’re using the array sourced data connection in another workbook, you might want to ensure the query is updated before the source workbook is closed. Just to make extra certain that the dynamic array and query have the same, synced, results.

Who gets it?

Importing dynamic arrays into PowerQuery is in Excel 365 for Windows, Insiders Fast release v 2105 build 14014.20002.

Dynamic Arrays now in Excel for iPhone and iPad
Better live sorting in Excel 365
Great and simple ‘live’ sorting with filtering in Excel
Easy and better lists with Excel 365’s Filter()

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address