Microsoft Excel Shortcuts for Mac and Windows. So, here is the Microsoft Excel shortcuts key, but before moving to our main title, let’s know about Microsoft Excel. Moreover, Excel is a spreadsheet developed by Microsoft for Windows, macOS, iOS, and Android devices. However, the main features of Excel are Calculation, Pivot table, Graphic.
Excel With Wayne is an online Microsoft Excel Training Course taught by Dr. Winston is the world’s foremost Microsoft Excel Expert that teaches at some of the world’s largest organizations including Microsoft and government organizations such as the Navy and Army. Array formulas can return either multiple results, or a single result. Beginning with the September 2018 update for Microsoft 365, any formula that can return multiple results will automatically spill them either down, or across into neighboring cells. This change in behavior is also accompanied by several new dynamic array functions.
This Dynamic Arrays in Excel tutorial is suitable for users of Excel for Microsoft 365.
Objective
Explore Microsoft’s NEW Dynamic Array functions in Excel and use them effectively to solve problems.
Dynamic Arrays in Excel Explained
Dynamic Array Formulas were introduced by Microsoft in 2018 and are the most significant change to Excel formulas in years. Dynamic Arrays allow you to work with multiple values at the same time in a formula. This feature enables formulas to return numerous results based on a single formula entered in one cell. The results are also dynamic, meaning that if the source data changes, the results will dynamically update.
The process of returning multiple values from one formula is referred to as ‘spilling’ because the results appear to spill across the worksheet.
Excel now contains 8 new functions that use dynamic arrays to solve problems.
In this post, we are going to look at an example of each.
Video Tutorial
FILTER
The Excel FILTER function filters data based on criteria that you specify. It returns all matching records, and if it cannot find any records, it will return a value that you specify in the formula.
=FILTER (array, include, [if_empty])
In the example below, Excel uses the FILTER function to look in the array B4:E23 for the ‘Block’ cell range and pull out the value located in cell G4 (East). This is our first criteria. It then looks in the ‘Exam’ cell range and pulls out the value located in G7 (English). The two quotation marks tell Excel to display nothing if it cannot find any matches.
This is the result:
RANDARRAY
The RANDARRAY function generates random numbers. The size of the array is specified by the rows and columns argument. You can also select the range in which the number will fall and if you want them to be decimals or integers (whole numbers). It is like a more powerful version of the RANDBETWEEN function.
=RANDARRAY ([rows], [columns], [min], [max], [integer])
In the example below, Excel has used the RANDARRAY function to output a table of random decimal numbers, eight rows high, and four columns wide.
Now let us look at an example that utilizes the optional arguments.
In this example, Excel has used the RANDARRAY function to output a table of random numbers six rows high, six columns wide, with values between 10 and 50. The TRUE argument tells Excel to output integer (whole) numbers as opposed to decimals.
SEQUENCE
The SEQUENCE function generates a list of sequential numbers. The array can be one-dimensional or two-dimensional, and you can specify the start value and the step value. If only the rows argument is defined, Excel will output a list of values starting at one by default.
=SEQUENCE (rows, [columns], [start], [step])
In this example, Excel has used the SEQUENCE function with just the rows argument to output a list that starts at one.
Excel Mac Array Formula
In this example, we are adding a value for the optional column’s argument. The result is a list of sequential values seven rows high and two columns wide, starting at one.
In this example, Excel has used the SEQUENCE function to output a list of sequential numbers ten rows high, eight columns wide, starting at ten and increasing in steps of five.
SORT
The SORT function in Excel sorts the contents of a range using one or more columns.
=SORT (array, [sort_index], [sort_order], [by_col])
By default, the SORT function will sort values in ascending order using the first column. You can utilize the optional arguments to control which column to sort by and what order.
In this example, Excel has used the SORT function to sort Marks in the left-hand table in descending order and output the results to the table on the right.
The SORT looks in the array B4:E23 for the 4th column and sorts the results in descending order.
If no [sort_order] argument is specified, the default sort is in ascending order.
SORTBY
The Excel SORTBY function sorts the contents of a range based on the values from another range. You can do multiple sorts on the same data.
=SORTBY (array, by_array, [sort_order], [array/order], …)
In this example, Excel uses the SORTBY function to sort the information in the table on the left by the Mark in descending order. It outputs the results to the table on the right.
In this example, Excel uses the SORTBY function to sort the information in the table on the left-hand side by the Mark in descending order and then by the Student Name in ascending order.
UNIQUE
The UNIQUE function returns a list of unique values from a range.
=UNIQUE (array, [by_col], [exactly_once])
In this example, Excel uses the UNIQUE function to output a list of unique values in a specified range.
/excel-multi-cell-array-formula-cb0087940d50495480a4a914599fbb43.jpg "Microsoft")
In this example, Excel uses the UNIQUE function to return a list of all unique items in a horizontal range.
XLOOKUP
Excel Apply Formula To Array
The XLOOKUP function is a modern replacement for functions like VLOOKUP, INDEX, and MATCH. It can lookup in vertical and horizontal ranges and supports approximate and exact matches.
=XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])
In the example below, Excel has used the XLOOKUP function to lookup the App listed in cell H4 in the App cell range and return the corresponding result from the Revenue column.
XMATCH
The XMATCH function performs a lookup and returns a position in vertical or horizontal ranges. It is the latest iteration of the MATCH function and supports approximate and exact matching.
=XMATCH (lookup_value, lookup_array, [match_mode], [search_mode])
In this example, Excel uses the XMATCH function to look up the value in cell F3, in the App named range. It is doing an exact match for the word ‘Slack,’ and it starts the lookup from the first item in the list.
Dynamic Arrays in Excel are a great addition to the Excel catalog for formulas and functions.
For more Free Excel tutorials from Simon Sez IT. Take a look at our Excel Resource Center.
Other Excel classes you might like:
To learn Excel with Simon Sez IT. Take a look at the Excel courses available.