3.5 Spill data with dynamic arrays - Video Tutorials & Practice Problems

<v Instructor>Most data analysts working</v> within Excel depend on arrays for complex calculations and problem solving. In fact, most of us have used accelerates without maybe realizing it. An array within Excel is a list of values or cell references. The current slide highlights a one dimensional, vertical array consisting of all row headers from the leftmost column, a one dimensional horizontal array consisting of all column headers from the top row. And then finally, a two dimensional array consisting of all data highlighted in yellow. When writing array constants, a comma is used as a column delimiter and a semi-colon is used for a row delimiter. So in the example shown here, the one dimensional horizontal array can be entered as an array constant with all column headers separated by commas, and the two dimensional data set can be entered as an array constant with all values within a row separated by commas and with a semicolon following the last value for each row. Array data in Excel is processed using array formulas. An array formula performs calculations on data within one or more arrays using Excel functions to generate an output in the form of a single value or possibly a list of values. Typical uses of array formulas include, generating randomized sample data sets which would be covered in a more intermediate or advanced course, conditional aggregation functions on a range of values, for example, COUNTIFS, SUMIFS and so on. Data filtering and lookups and matrix multiplication and manipulations. For Excel products prior to the latest 365 suite, including Excel 2019, an array formula that could process multiple values or return multiple results had to be confirmed with the Control + Shift + Enter key combination. These formulas were thus typically referred to as CSE formulas. For easier identification, such formulas were displayed with curly brackets around the entire expression. Starting with the September 2018 release of Excel for Microsoft 365, array formulas can be confirmed just like other Excel formulas using the Enter, Tab or Arrow keys. Depending upon the result format, the result is either placed in the same formula cell for single values, or it may be output down, across or in both directions from the formula cell, depending upon the resulting array structure and size. This latter behavior is called spilling, and the formulas that return a raise of variable sizes are called dynamic array formulas, which typically include dynamic array functions. These are built-in or user-defined Excel functions that can output one dimensional or two dimensional arrays to adjacent cells. At the time of this recording, the following built-in dynamic array functions are available within Excel. These functions can either be used individually or they can be combined with each other to reduce the number of steps in getting to the desired output. As an example, we can use the Filter function to return the values from the list on the left that are less than 10. And we can also combine the same formula with the Sort function to return the results in a sorted list as shown in the second example. It is important to note that this spilling feature in Microsoft 365 Excel is crucial for the dynamic array capability. It is thus pertinent that we spent some time understanding this spill behavior in Excel and the corresponding errors. Our next topic we'll cover these in further detail. As a data analyst, designing and implementing XLV solutions, it is important that we understand the spilling behavior in Excel, the relevant constraints and any errors that we might encounter. As discussed earlier, depending on the output array structure and size, dynamic array formulas will place the resulting values down and or across from the formula cell which is often referred to as the starting cell. The cell range where the results are placed is called a spill range. And the formulas that successfully spill are called a spilled array formulas. Now let's review some attributes of the spill behavior in more detail. Once a dynamic array formula builds its results, the boundaries of its spill range can be easily identified by clicking on any cell within the range. When clicked, the entire range is highlighted with a colored border. In case of some spillers, which we'll review in more detail later in this lesson that we'll typically click on the starting cell which is a cell with the error to review and clear out the corresponding spill range. Once cleared, the spill data will automatically fill the entire range. When referencing spill ranges and their values and other formulas, the starting cell of spill range can be referenced by either using explicit references or defined names. If you want to reference all data within a spill range, we can use this spill reference notation, which requires a pound symbol after the starting cell reference. This reference notation works for both explicit cell references, (keyboard clicking) as well as defined names for the spill range starting cell. We can also use one of the Lookup Functions within Excel to refer to a specific value within the range. In this case, we use the MATCH function to look for the numeric value three within the range and return its index. The implicit intersection operator which is the add symbol, which we'll review in more detail later in this course, can also be used to reference a value in the same row for spill range as the currently selected formula cell. Apart from these attributes, the spill behavior is also subject to some constraints. It should be noted that this capability is not supported within Excel tables. This might be obvious since Excel tables will typically have other data around the formula cell, which would limit this spill. Additionally, at the time of this recording dynamic arrays and the relevant spill behavior have limited support across workbooks. Formulas using this feature will not work unless both workbooks are open at the same time. There are a few other scenarios where spilling might fail and result in a spill error. Excel from Microsoft 365 thus includes specific editor messaging related to the spill behavior. Now let's review these spill errors and some ways to troubleshoot them.