In past versions of Excel, the program had what were called "array functions." The easiest way to understand what these functions did is to picture them as one input cell that generated output that spanned $($or "spilled into"$)$ multiple cells. While powerful, they were difficult to use and required specialized keystrokes to implement. To make array functions easier to use, Microsoft recently created Dynamic Array Functions in Excel which eliminated the cumbersome implementation of the older array functions.

Dynamic Array Functions can be extremely useful in business applications. Additionally, when combined, they can create flexible dynamic tables and dashboards that allow a user to get insights from data quickly. The most important word to consider is "dynamic" in that the results of these functions can change depending on other data in the spreadsheet. Let's use this graded tutorial and the accompanying spreadsheet to see how they work.

The data has been collected in the Microsoft Excel file. Open the spreadsheet and perform the required analysis to answer the questions below.

The UNIQUE function

You can find more information about the UNIQUE function at Office Support.

Oftentimes we have a long list of repeating data; company names, customer names, states, email addresses, etc. and we would like a simple list of all the unique items in the data. Luckily, Microsoft created a new dynamic array function named UNIQUE that can do that.

Tip: If you want to reference an individual column in a data table the syntax is: TableName$[$ColumnName$].$ For example, to reference the Company column in Table$1$ it would be Table$1[$Company$].$

In column A$,$ you will find a long list of company names in Table$1.$ To obtain a list containing each unique company, navigate to cell E$2$ and enter $=$UNIQUE to pull up the UNIQUE function. This function takes three arguments:

$=$UNIQUE$($Array$,[$By$\_$Column$],[$Occurs$\_$Once$]).$

The first argument, array, is where the data is located, and the last two arguments are TRUE$/$FALSE$.$

In cell E$2,$ use the UNIQUE function to reference the Company column in Table$1$ for the first argument $($array$)$ and then make the last two arguments both FALSE. Which list below matches the results of the UNIQUE function?

Soylent Corp

Nakatomi Corp

Massive Dynamic

Stark Industries

Acme Corporation

Umbrella Corporation

Vehement Capital Partners

Globex Corporation

Initech

Initech

Soylent Corp

Nakatomi Corp

Stark Industries

Massive Dynamic

Acme Corporation

Globex Corporation

Umbrella Corporation

Vehement Capital Partners

Initech

Globex Corporation

Vehement Capital Partners

Umbrella Corporation

Acme Corporation

Stark Industries

Massive Dynamic

Nakatomi Corp

Soylent Corp

You now have a list of all the companies in the data table. Another great feature of dynamic array functions is that they will respond to new data. For example, if a new company is added to the Company column, the list in E$2$ will update to reflect the new company and spill into one additional cell.

THE SORT function

You can find more information about the SORT function at Office Support.

The SORT function can do several things but for this graded tutorial we will keep it simple. It sorts. And by default, it behaves how we would use it most of the time; it sorts data in ascending order. The Company List in column E is not sorted alphabetically and for various reasons we would like it to be$.$ Luckily, we can combine the SORT and UNIQUE functions to achieve this result.

First, return to cell E$2$ and in the formula bar, copy the text $($not the equal sign$)$ of the UNIQUE function you created. In cell F$2,$ begin the SORT function and place the entire UNIQUE function you copied inside the sort FUNCTION: $=$SORT$($UNIQUE$(...)).$

Which list below matches the results of the SORT function?

Initech

Soylent Corp

Nakatomi Corp

Stark Industries

Massive Dynamic

Acme Corporation

Globex Corporation

Umbrella Corporation

Vehement Capital Partners

Vehement Capital Partners

Umbrella Corporation

Stark Industries

Soylent Corp

Nakatomi Corp

Massive Dynamic

Initech

Globex Corporation

Acme Corporation

Acme Corporation

Globex Corporation

Initech

Massive Dynamic

Nakatomi Corp

Soylent Corp

Stark Industries

Umbrella Corporation

Vehement Capital Partners

By nesting UNIQUE inside of SORT, you have created an alphabetical list of unique items that is also dynamic.

Dynamic Array errors

Navigate to cell G$5$ under Customer List Sorted and enter anything: your name, the month you were born, your age, doesn't matter.

Now navigate to cell G$2$ and use everything you learned above about UNIQUE and SORT to create a sorted list of unique customers. What happened? What error did you get?

#NUM!

#REF!

#CALC!

#DIV$/0!$

#SPILL!

The results of dynamic array functions flow down and$/$or across from the cell in which they originate. All those cells must be empty and there must be enough room for the results to flow fully. If not, you will get the error you see above.

Delete the content you placed in cell G$5$ and the SORT$($UNIQUE$)$ fu