Rules For Dynamic Arrays

Question 1

If a formula delivers a single answer what will it return?

Answer 1

It will return a single answer in one cell.

Question 2

If a formula delivers more than one answer, it will return all answers in one cell

Answer 2

Using Dynamic Arrays, the answers “spill” into the next cells.

Question 3

The actual formula lives only in the first cell of the spilled range.

Answer 3

The rest are based off the first one.

Question 4

If something is in the cell below the spill range, you will get the new #spill error (your values below wil never be overwritten).

Answer 4

Once the values are removed from the sill area, the formula will spill automatically.

Question 5

Referencing a spilled range is done by referencing the first cell in the range and then putting the # sign.

Answer 5

The spilled range, will automatically expand or shrink as the formula input changes.

Question 6

There are 7 new Dybamic Array functions?

Answer 6

FILTER
UNIQUE
SORT
SORTBY
SEQUENCE
RANDARRAY & @ Prefix

Question 7

Spilled Array formulas are not supported inside Excel Tables

Answer 7

Place them outside the table

Question 8

Dynamics Array functionality is not fully supported between workbooks.

Answer 8

and workbooks need to be open

Question 9

In legacy Excel, you had to be Excel Expert to be able to use arrays

Answer 9

Now everyone can use arrays.

Question 10

If a formula delivers a single answer what will it return?

Answer 10

It will return a single answer in one cell.

Question 11

If a formula delivers more than one answer, it will return all answers in one cell

Answer 11

Using Dynamic Arrays, the answers “spill” into the next cells.

Question 12

The actual formula lives only in the first cell of the spilled range.

Answer 12

The rest are based off the first one.

Question 13

If something is in the cell below the spill range, you will get the new #spill error (your values below wil never be overwritten).

Answer 13

Once the values are removed from the sill area, the formula will spill automatically.

Question 14

Referencing a spilled range is done by referencing the first cell in the range and then putting the # sign.

Answer 14

The spilled range, will automatically expand or shrink as the formula input changes.

Question 15

There are 7 new Dybamic Array functions?

Answer 15

FILTER
UNIQUE
SORT
SORTBY
SEQUENCE
RANDARRAY & @ Prefix

Question 16

Spilled Array formulas are not supported inside Excel Tables

Answer 16

Place them outside the table

Question 17

Dynamics Array functionality is not fully supported between workbooks.

Answer 17

and workbooks need to be open

Question 18

In legacy Excel, you had to be Excel Expert to be able to use arrays

Answer 18

Now everyone can use arrays.