COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
Find out how dynamic arrays in Excel will change your (working) life!
Part six of a six-part series of blogs

Dynamic arrays are a new feature being rolled out in Excel 365. This blog explains how they work, and why they will make so many things in Excel easier.

  1. Using dynamic arrays in Excel
  2. Can I use dynamic arrays?
  3. Introducing dynamic arrays
  4. Filtering and sorting data
  5. Two more functions: RANDARRAY and SEQUENCE
  6. Implications of dynamic arrays (this blog)

Posted by Andy Brown on 14 February 2020

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Implications of dynamic arrays

What's exciting about dynamic arrays is not the technology, but how you can apply it (actually, I think that's true of any technology).  Below are 3 ways in which dynamic arrays are going to change your Excel working life.

Range names are easier to work with and more flexible

If you make regular use of range names, your life is about to get easier.  Let's first just create a couple of range names.  In the spreadsheet below, imagine that you select the block of cells shown selected and press SHIFT + CTRL + ENTER:

Creating range names

We want to create two range names: Sales and Costs.

You can then choose to apply the names in the right-hand cells to the ranges to their left:

Creating range names

Choose OK to create our two ranges.

 

So here's the thing.  When you want to calculate profits, you used to have to type in =Sales-Costs and copy the formula across:

Calculating profit

For each of the 5 years in the model, the forecast profits will equal forecast sales minus forecast costs.

When you press Enter in the above example, you now get a spill range:

The spill range

The formula automatically enters figures in the Profit row.

 

However, it's even better than that!  Range names no longer have to start in the same column as the formula they reference:

Formula in different column

This formula starting in column C still works, even though it references ranges which start in column D.

You can even use range names containing more than one row in a formula:

Multiple row range names

This formula will give the profit for each year and region (without dynamic arrays, it would have given an error message).

Data validation becomes much easier

Suppose that you want to achieve a cascading drop list like this.  First you choose your Hogwarts house:

Choosing a house

You can use data validation to base the choice in a cell upon a list.

 

You should then be able to choose your character in this house:

Choosing a character

The values in the second drop list should depend on the choices you made in the first.

 

You can do this in pre-dynamic-array Excel; it's just really hard (involving using range names based on the OFFSET function, among other things).

In new Excel this is all now fairly straightforward to create.  First create the range listing the houses:

Listing unique houses

This formula will list out the unique house names in the range in column A, sorted alphabetically.

Next create your data validation:

Setting first data validation

Select the cell shown (B13), and choose the Data Validation option on the Data tab of the ribbon.

Set the range upon which you want to base your list to be the one containing the list of houses:

The source range for the list

The # at the end of the source reference is vital - it tells Excel to use the spill range for this cell, and not just E2 on its own.

 

Now create the second range, listing the characters who belong to the house you've chosen:

The second range

The formula lists out (in alphabetical order) all of the characters whose house name matches the one chosen in cell B13.

You can then repeat the validation setting to choose a character.  First select the cell:

The character input cell

Choose the cell in which a user should be able to choose a character.

 

In data validation, set the list source range to be the spill range beginning at F2:

Second data validation

Set the source to list out the characters matching the house you've chosen (again using a # character to denote the spill range).

 

The magic begins!

The final system

You have to have tried solving this in old Excel to appreciate just how good this is!

 

Pivot tables become ... redundant?

I've now added a column to my table giving the gender of each person:

Table giving character genders

I presume Nymphadora was female? I'm pretty sure about the others.

I want to analyse the data, so I create this pivot table:

Pivot table

A pivot table showing the number of characters for each house and gender.

 

The problem I've got is that whenever I add more characters, I have to refresh the pivot table.  Dynamic arrays allow you to create something which looks like a pivot table, but which is linked directly to the underlying data.

To create this, first get the row and column headings:

Pivot table row and column headings

See below for the formulae used in each case.

 

Here are the formulae I've used:

What  Formula
Row headings =SORT(UNIQUE(House))
Column headings =TRANSPOSE(SORT(UNIQUE(Gender)))

The second formula takes the gender column, picks out the unique values, sorts them and then transposes the result so that they go across instead of down. 

Now for the clever bit - showing this:

The final statistics

The final formula should show the number of characters whose house matches the house in the row label on the left, and whose gender matches the gender at the top.

The formula for the above screen shot is this:

=COUNTIFS(House,A14#,Gender,B13#)

I've just checked, and this is an impressively short 33 characters long.  What it does is to count how many rows there are where:

  • the house name equals the one shown in the spill range which runs down from cell A14; and
  • the gender equals the one shown in the spill range which runs across from cell B14.

Very clever indeed!

This blog has 0 threads Add post