**Sort**,

**Filter**, and

**Unique**. In this post we will have a look at

**Sort**and

**Filter**.

The first thing to keep in mind is that the results from these Dynamic Array formulas will “spill” into adjacent cells; therefore, we need to plan for the possible spill range by allowing ample space in adjacent cells. This spilling is by design; and, in the event the spill range is not large enough for the results, Excel returns a

**#SPILL!**error.

The second item to consider is that references used in Dynamic Arrays between workbooks need both workbooks to be open; Excel returns a

**#REF!**if they are not.

**Spill**

**Payments**. In cell

**G5**, enter the formula

**=payments**and press

**Enter**. The data is now in G5:J22 using this single formula. While in the data range, you will notice an outline around the data indicating that it is part of a dynamic array. The formula is only in cell G5 and, therefore, all other cells in the array will show the formula as a light gray in the formula bar as shown below.

**L5**highlighted, Excel will show where the data will spill, providing clear guidance on which data is causing the error. To resolve the problem, either move the dynamic array formula cell or clear the data in the spill area. Additionally, as shown below, Excel provides an error-checking tool and a quick way to

**Select**

**Obstructing**

**Cells**if you want to move them outside the spill range. Once you clear the obstructing cells from the spill range, the dynamic array formula should complete, unless one of the following four issues arises.

- The spill range includes merged cells;
- The dynamic array formula is inside of a table;
- The system is out of memory; and
- The sequence is volatile, such as with a
**RANDBETWEEN**sequence.

Spill Range Reference

**#**symbol. In the previous example, to refer to the entire spill range, the reference would be

**=G5#**. This does have a limitation in that it is not supported when linking to an external workbook.

**Sort**

**SORT,**the process is simply to use the formula below.

**=SORT(array,[sort_index], [sort_order],[by_col**

**=SORT(sortdata,3,1)**. The data is in a table named

**sortdata**; the number 3 is the column to use as the sort, and the number 1 is for ascending order.

If you want to sort by more than one index, you can use an array constant. For example, to sort the data by

**Account**ascending and

**Amount**descending, the formula would be

**=SORT(SortData,{3;4},{1;-1})**. The results of these formula-based sorts are shown below.

**SortBy**

**SORTBY**function allows you to sort the contents of an array based on the values in a corresponding array. The syntax for the function is

**=SORTBY(array, by_array1, sort_order1,….)**. For example, to do the same sort as we did with the array constant, the formula would be as follows.

**=SORTBY(SortData,SortData[Account],1,SortData[Amount],-1)**

**Filter**

**FILTER**, which allows filtering of data into a new array. The syntax for the function is

**=FILTER(array, include, [if_empty])**. To have more than one condition, use the plus sign between them for either and the multiply sign between them for both.

As an example, in

**Figure 18**, we want to retrieve all rows from the table named

**FilterData**that have the item stamps. The formula would be as shown below.

**=FILTER(FilterData,FilterData[Item]=I1,"Item not found")**

**=FILTER(FilterData,(FilterData[Item]=I1)*(FilterData[Department]=I2),""**

dynamic_arrays.xlsx |