Arrays vs. Dynamic Arrays

This example demonstrates nicely that although the use of arrays is not new in Excel, array formulas that output multiple results were awkward. You would need to know the cells that the formula would output to, which was a massive constraint to their use. They were static arrays.

So, dynamic arrays really are a combination of two key developments – dynamic + arrays.

These formulas are entered in the same way as any other formula and will handle arrays without the need to press Ctrl + Shift + Enter. And they are dynamic. You do not need to know the range of cells to output to. The spill range will dynamically grow and shrink depending on the number of values being returned by the formula.

Now, not all array formulas return multiple values, so single cell array formulas were easier to apply in the Excel of the past. You just needed to recall pressing Ctrl + Shift + Enter, and not just Enter.

However, did your colleagues know to press Ctrl + Shift + Enter, and what were the curly braces around the formula? Did you need to type them?

These limitations meant that, in the past, writing array formulas ended up being for advanced uses only, and the SUMPRODUCT function gained a lot of love as it could handle the arrays natively.

With dynamic array formulas, this is all a thing of the past.

Dynamic Arrays with Tables

In Figure 10-4, the following formula returns all the values from the table in range A1:B6 named [tblData]. If the [#All] was omitted, the formula would only return the table data excluding the headers.

=tblData[#All]

Using dynamic array formulas with table data is a dream combination. The first example that referenced range A1:B6 works great. However, it is not a fully dynamic solution as using the range as the source is not dynamic. So, you are losing 50% of the brilliant dynamic + array capabilities.

Tables are dynamic and grow and shrink with data automatically. So, using table data as the source for a dynamic array formula ensures a completely dynamic solution.

Figure 10-5 shows a new row added to the table. The table expands, and the dynamic array formula expands in sync.

Dynamic Array Formula Example

Let’s look at a more practical case for a dynamic array formula. Figure 10-6 shows a table named [tblScores]. We will use an AVERAGEIFS function to calculate the mean average score for each region.

The formula references the scalar value in cell E3 for the criteria. The formula is then filled down to cells F4 and F5, which reference the values in cells E4 and E5 consecutively. This is classic Excel.

With dynamic array formulas, we could reference the range of criteria values, instead of a single criteria value at a time.

Instead of producing three formulas – one formula per cell – the dynamic array formula is one formula that returns three results.

Once again, this example is not dynamically complete as the criteria is not based on table data or a DA spill range. It, therefore, is not maximizing the full potential of dynamic arrays. We will soon be constructing our dynamic array formulas on table data and spill ranges only.

With dynamic arrays, none of this needs to be considered. The formula is written just like any other formula and wherever you want.

Welcome the # Operator

You may be wondering how one would reference the spill range of a dynamic array formula. You may want to use the results of a dynamic array formula in another formula or maybe in a chart.

If the spill range is dynamic, you wouldn’t necessarily know the final cell of the range.

Well, the answer, as I’m sure you have concluded from the title of this section, is the # symbol (typically referred to as the hash or pound sign).

To refer to all the cells in the spill range, you reference the origin cell that contains the dynamic array formula followed by the # symbol, for example, A2#.

The #SPILL! Error

The #SPILL! error is created when the results of a dynamic array formula cannot be returned to the grid. This is typically caused by other content blocking the spill range.

In Figure 10-10, some text is causing the #SPILL! error to be returned. The DA formula needs blank cells in the spill range to return the results to. To remove the error, this text needs to be moved or deleted.

The #SPILL! error is also returned if there are merged cells in the range that the formula needs to spill to or if a dynamic array formula is used within a table.

Figure 10-11 shows the #SPILL! error caused by merged cells in the spill range. The cells in range F5:G5 have been merged in this example.

You would need to unmerge the cells to remove the #SPILL! error.

Merged cells should only be applied in very specific cases in Excel. Very few actions can attract the ire of an Excel user more than the sight of merged cells.

DA Formulas Cannot Be Used Within Tables

The #SPILL! error is also caused by a dynamic array formula being used within a table. An example of this is shown in Figure 10-12. Note the blue corner in the bottom right indicating that the range is formatted as a table.

Using tables as the source data for dynamic arrays is an ideal relationship as it provides a fully dynamic solution. However, dynamic arrays are their own range and cannot be used within tables.

Let’s now look at the dynamic array functions that were released to take advantage of this new formula behavior.

Returning Distinct and Unique Values

The UNIQUE function, despite its name, actually returns the distinct values by default. A list of the distinct values is a list with the duplicate values removed. This is the most common use of this function.

A list of the unique values is a list of values that occur only once in the list. This is specified by entering TRUE for the final argument of the UNIQUE function.

There is a lot of confusion over the terms distinct and unique. Users often refer to a list of distinct values as a list of unique values. So be prepared for that when conversing with other Excel users.

It does not help that Microsoft named the function UNIQUE, yet it returns a distinct list by default. I believe it was named UNIQUE as that is the more common term, and Excel users would be more familiar with it. The final argument does however coincide with the function name. Enter TRUE for a unique list, or enter FALSE or omit the response for a distinct list.

In Figure 10-13, the following formula returns the distinct values from the [Customer] column. Five names are returned as there are three duplicates in the column.

=UNIQUE(tblAttendances[Customer])

In Figure 10-14, the following formula returns the unique names from the [Customer] column. The second argument is ignored, and TRUE is entered for the exactly once argument. There are three names that occur exactly once.

=UNIQUE(tblAttendances[Customer],,TRUE)

UNIQUE with Multiple Columns

The examples so far have demonstrated the UNIQUE function with a single column. However, UNIQUE can handle multicolumn arrays or even entire tables as the array from which to return the distinct or unique results.

The results show that an “Elizabeth Brown” has been returned twice. This is because the offices are different (London and York), so they must be two distinct Elizabeth Browns. However, there is a duplicate of “Maria Larsson” in [tblPoints], so only the first of those records was returned.

In this multiple column array example, the columns were adjacent. In fact, the formula used the range tblPoints[[Name]:[Office]].

Let’s see how the UNIQUE function can be used with a multiple column array where the columns are non-adjacent.

This is a technique that can be used to choose which columns you want to extract from any table or array. The CHOOSE function is given the array of constants {1,2}. It is then told to return the [Name] column as column 1 and the [Office] column as column 2. This is passed to UNIQUE to work with.

UNIQUE to Compare Columns

The UNIQUE function compares the values in rows by default. This is the more common practice as we handle columnar data usually.

It is no problem for UNIQUE to work with data stored in rows though, if required. The second argument of the UNIQUE function allows us to switch to comparing values in columns. This is done by entering TRUE for the second argument named By col.

In Figure 10-17, the following formula returns the distinct values from range B4:G4:

=UNIQUE(B4:G4,TRUE)

The results are returned along a row. The TRANSPOSE function could be used to convert the results into a column. This example is shown when we cover TRANSPOSE later in the chapter.

Distinct Labels for SUMIFS

We covered an example earlier in the chapter that used the AVERAGEIFS function on a range of criteria values. This generated a spill range with the results for each criterion in that range (Figure 10-8).

That example was a static report as the criteria values were simply typed into the cells. Let’s explore a dynamic solution where the UNIQUE function will return the distinct values from a table. A SUMIFS function will then use the spill range generated by UNIQUE for its criteria values.

Figure 10-18 shows the first few rows of a table named [tblSales] that we will use for this example. Our goal is to create a small report showing the sum of the [Total] column for each product in the [Product Name] column.

This report is completely dynamic. If new rows are added to the [tblSales] table, the report will update. And if new product names are added, the report will automatically expand with the new product and its total.

Distinct Count Formula

The examples so far have all returned values to the grid. The UNIQUE function can, of course, also be nested within other functions. A natural example of this would be to count the number of distinct values returned.

In Chapter 9, we created a distinct count formula using SUMPRODUCT and COUNTIFS. With dynamic arrays and the UNIQUE function, this is much simpler.

Simple SORT Example

Let’s start with a simple example of the SORT function being used to sort a single column of data.

In Figure 10-21, the following formula sorts the list of country names in ascending order.

Sort the Distinct Values

When discussing the UNIQUE function previously, we created a small report showing the total sales by product from data in [tblSales] (Figure 10-18). This report is shown in Figure 10-22.

The results in this report are in no particular order. They actually appear in the order that UNIQUE finds them in [tblSales]. This is not useful. Let’s use the SORT function to sort the distinct list of product names in ascending order.

Once again, no other arguments are required because we are sorting a single column, and we require the argument defaults of ascending order and sort by row (vertical sort).

Sort by Specified Column

Let’s move on and see examples of the SORT function returning an array with multiple columns. For these examples, we will use the table named [tblProductSales] (Figure 10-24).

So, in this example, the results are in ascending order by the product name.

Let’s specify a different column for the results to be sorted by. We want the results to be in a descending order by the [Total] column.

We need to provide the SORT function with the sort index. So, for the [Total] column, this will be column 4.

In Figure 10-26, the following SORT formula has been used. –1 has been entered for the sort order to sort the values in a descending order.

=SORT(tblProductSales,4,-1)

Sort by Multiple Columns

It is possible to request the SORT function to sort using multiple columns. To do this, we will enter the multiple sort indexes and sort orders within arrays.

For this example, we will sort the [tblProductSales] data by two levels: first by the [Category] column in ascending order and then by the [Total] column in descending order.

The array {2,4} is used in the sort index argument to specify that the data should be sorted by column 2 and then by column 4.

The array {1,-1} is used in the sort order argument to state that column 2 should be sorted in an ascending order and column 4 should be sorted in a descending order.

This formula could be extended to sort by more columns if required.

SORT Function Returning Specific Columns from a Table

For the final SORT function example, we will return specific columns from [tblProductSales].

Dynamic array functions in isolation can only return columns that are adjacent to each other. In this example, we will return the [Product Name], [Units], and [Total] columns only.

These columns are split by the [Category] column in the table, so SORT will need some assistance to work with these columns as they are non-adjacent.

To do this, we will nest the CHOOSE function within the SORT function to specify the columns to work with. We saw the CHOOSE function assuming this role previously in this chapter within the UNIQUE function.

In Figure 10-28, the following SORT formula returns the three required columns and sorts the data in descending order by the [Total] column. Column 3 is specified as the sort index as the [Total] column is the third column of the array returned by CHOOSE.

=SORT(

CHOOSE({1,2,3},

tblProductSales[Product Name],tblProductSales[Units],tblProductSales[Total]),

3,-1)

When returning only three columns, this works quite well and is simple to deploy. In more complex examples, this CHOOSE technique can be bulky, and a more advanced technique would involve the INDEX function. We will see examples of this technique in Chapters 11 and 13 of the book.

Simple SORTBY Example

Let’s begin with a simple example that returns all columns from the [tblProductSales] table sorted in descending order by the [Total] column.

In Figure 10-30, the following formula is entered in cell B3. This SORTBY formula is more meaningful than its SORT equivalent (Figure 10-26) as it uses the structured reference of tblProductSales[Total] instead of the sort index of 4. The –1 specifies the descending order.

=SORTBY(tblProductSales,tblProductSales[Total],-1)

Sort by Multiple Columns with SORTBY

The SORTBY function can sort an array by multiple columns, and, once again, it is an easier task than the equivalent formula with the SORT function (Figure 10-27).

SORTBY has arguments for us to keep specifying the additional columns and sort orders that we want to apply.

A 1 is entered in the sort order 1 argument to specify ascending order for the [Category] column. This could have been omitted, and the formula would have worked fine, as ascending order is the default. However, it is a more meaningful formula, I believe, when this sort order is stated.

Sort by Column Outside of the Returned Array

A great advantage that the SORTBY function has over SORT is the ability to sort an array using a column, or row, that is not included in the returned array.

In Figure 10-32, the [Product Name] and [Total] columns are returned with some help from the CHOOSE function. This array has been sorted in a descending order by the [Units] column, which is not included in the returned array. Pretty cool!

=SORTBY(

CHOOSE({1,2},tblProductSales[Product Name],tblProductSales[Total]),

tblProductSales[Units],-1)

This can be a very useful technique. Let’s see another example.

Sort Product Name by Sales Totals

Let’s return to the sales by product report that we have used in a few examples of this chapter so far. Figure 10-33 shows this report with the total sales by product and the results in an ascending order by the product name.

We will change this to sort the results by the total sales in a descending order. It will be more practical to order the products by the sales total rather than their name. And it’s a great example of how useful SORTBY is.

We will start by using the SORTBY function to sort the product names by the sales totals. The SUMIFS function will be used to sum the [Total] column for each product. With the product names ordered correctly, the SUMIFS function will then be used again to create the column of total sales values.

In Figure 10-34, the following formula sorts the distinct list of product names returned by UNIQUE in a descending order by the totals returned by SUMIFS:

=SORTBY(UNIQUE(tblSales[Product Name]),

SUMIFS(tblSales[Total],tblSales[Product Name],UNIQUE(tblSales[Product Name])),

-1)

This is another nice example of using SORTBY to sort by an array outside of the one being ordered. We now need to return the totals.

We will use these spill ranges in a bar chart later in this chapter. This dynamic sorting by sales totals will ensure that our bar chart orders the bars from largest to smallest.

Stepping into the SEQUENCE Function

Let’s start with some basic examples to get a feel for how to operate the SEQUENCE function. This will prepare us for the more practical examples to come in this chapter and the next.

In Figure 10-36, a simple SEQUENCE function is entered in cell B2 to generate a series of numbers from one to eight. Only the rows argument is entered. A start and step value of one is used as these arguments were omitted.

=SEQUENCE(8)

The order of the number sequence goes row by row. Shortly, we will see how this order can be changed to column by column.

The formula returns a series of numbers that is six rows high and four columns wide. It begins from number five and steps every fifth number.

A negative step can be used to reverse the sequence of values returned. In Figure 10-39, the following SEQUENCE function returns a series that is five rows high, starts from five, and steps negatively:

=SEQUENCE(5,,5,-1)

In this example, the columns argument is skipped. The default one column wide is used.

Changing the Row/Column Order

When returning a two-dimensional array, we saw that the numbering of the series goes across columns, before going to the next row (Figure 10-38).

To switch the ordering of the numbers in the series, the TRANSPOSE function could be wrapped around SEQUENCE.

The TRANSPOSE function has been added, and the number of rows and columns has been reversed. SEQUENCE returns an array of four rows and six columns, and this is transposed. Awesome!

Let’s see some examples now that really showcase the flexibility and potential of the SEQUENCE function.

Sum the Top N Values

Performing a calculation on the top N values is a common task for reporting in Excel. For this example, we will sum the top N values, but a calculation such as average could also be applied.

We saw a method to sum the top five values in Chapter 9 with the SUMPRODUCT function. This was great as it works for all versions of Excel.

If you have a dynamic array–enabled version of Excel though, there is a better way. It is simpler and more dynamic. And of course, SEQUENCE plays an important role.

The LARGE function will also be used in our formula. This function has been covered already in this book, but let’s briefly remind ourselves of the purpose and syntax of the LARGE function.

The SEQUENCE function is used for the K argument of the LARGE function. It returns an array of numbers determined by the value in cell D3. In this example, cell D3 contains the number 3, so the SEQUENCE function returns {1,2,3}.

This replaces the need to enter an array of constants like we did in the SUMPRODUCT example in Chapter 9. This also keeps it dynamic.

The LARGE function returns the first, second, and third largest values from the [Total] column, and these are summed by SUM.

Sum the Last N Days

The SEQUENCE function is great for creating a series of dates that occur at specific intervals. The activity on these dates can then be analyzed with other formulas or charted.

For this example, we will use the SEQUENCE function to return the last N days. We will find the last date in a range and then return each date for the last N days. The number of days will be specified by a cell value.

Figure 10-42 shows the first few rows of a table named [tblSales]. This table will be used as the data source for the next two examples. It contains sales data with daily transactions. The table has been sorted by [Sales Rep] to show the different dates in the [Date] column.

This is a fully dynamic solution.

If new dates are added to [tblSales], the MAX function will pick it up, and SEQUENCE will adjust to the new data. And if a different value is entered into cell C2, the SEQUENCE function will return that number of rows. The SUMIFS function references the spill range, so this will also update.

Sum the Last N Months

Let’s expand on this example and get the SEQUENCE function to return the last N months from the last month in the [tblSales] table.

The DATE function is used to construct a date value for the results. These values could be formatted to show month names using the custom number formatting feature of Excel. I have not bothered with this in the example, as I’m happy to show the first date of each month.

The MAX function is used again to return the latest date in the [Date] column of [tblSales]. The MONTH function extracts the month number of the latest date.

The spill range is accessed using E5# for the first date of the month.

The EOMONTH function is used to return the last date of a month a specified number of months in the future or past. A zero is entered for the months argument to return the last date of the current month.

You may notice the + sign before the spill reference in the EOMONTH function. The EOMONTH function is one of a few functions in Excel that does not spill. An error is returned if the spill reference E5# is used. A trick to work around this issue is simply to add the + before the spill reference.

Last N Months by Country – Pivot Style Report

The last two examples have used the SEQUENCE function to spill the date values across rows. But, of course, SEQUENCE can spill across columns also. This feature can be used to create awesome pivot style reports with time-based analysis.

There are some differences in this formula compared to the previous one that spilled across rows.

Notably, the rows argument is omitted. The other key difference is that the dates are ordered smallest to largest. In the previous example, they were largest to smallest. To do this, the value in cell D2 was subtracted from the month of the maximum date. This takes us a month too far, so one is added. The SEQUENCE function steps one month at a time from that date .

Sum Values from Alternate Rows/Columns

There are many scenarios in Excel where we can take advantage of the SEQUENCE function and its ability to return a sequence of values. Another of these scenarios is to perform a calculation on the values in every Nth cell. In the first of these examples, we will sum the values in alternate rows.

Figure 10-47 shows the following formula being used to sum the “Income” values only:

=SUM(

INDEX(tblAccounts[Amount],

SEQUENCE(ROUNDUP(ROWS(tblAccounts[Amount])/2,0),,,2))

)

Four numbers are returned, as the ROWS function returns the total number of rows in the table (eight), and this is divided by two as we want every other row, that is, half the rows.

The ROUNDUP function is included in the scenario that we have an odd number of rows. In this example, it is not relevant, as we are dealing with income and expenses, so the total rows will be an even number. However, it has been added for completeness. If there were 9 rows in total, half would produce 4.5 and ROUNDUP would make this 5. The rows {1;3;5;7;9} would be returned in that scenario.

The columns and start arguments are omitted, and the SEQUENCE function is asked to step by two.

With the SEQUENCE function, the values that we step through can represent rows or columns, and we can specify any Nth value. For a second example, we will return to a task that we completed with the SUMPRODUCT function along with others in Chapter 9.

In Figure 10-48, the following formula is entered in column B and sums the values in every fourth column (the two quarter totals):

=SUM(

INDEX(C2:J2,,

SEQUENCE(,COLUMNS(C2:J2)/4,4,4))

)

This time, the rows argument is omitted, and the COLUMNS function is used to return the total columns in the range C2:J2 (eight columns). This value is then divided by four (the number of columns we want to step) to return the result of two columns.

The INDEX function then returns the values from columns 4 and 8 in the range C2:J2 to be summed. Notice that the rows argument of INDEX is omitted.

RAND and RANDBETWEEN

Availability: All versions

Let’s begin with a quick look at the RAND and RANDBETWEEN functions. They are still great to use, and understanding them will help us appreciate the role that RANDARRAY plays.

Figure 10-49 shows the RAND function entered in cell B2 and filled down to cell B6 to return five random numbers between 0 and 1. Five formulas are used here as we are using a non-DA formula. RANDARRAY will be able to achieve this in one formula.

It has been entered in cell B2 and filled down five rows and across three columns to cell D6. There are 15 formulas entered to produce these random values. It is probably no surprise that RANDARRAY can handle this task also with just one formula that spills.

So, the two key differences between RAND and RANDBETWEEN are that RAND returns decimal values while RANDBETWEEN returns integers and that RANDBETWEEN allows us to specify the range of values from which to return.

Both functions generate a new random number every time the worksheet calculates.

Introduction to RANDARRAY

The RANDARRAY function returns an array of random numbers. All the finest qualities of RAND and RANDBETWEEN are included, with the added ability to return a dynamic array that spills.

You can specify the number of rows and columns to spill, whether you want decimal values or integers, and you can specify the bottom and top values from which to return the random number(s).

Let’s look at a few examples that show different applications of these arguments. Then we will see two practical examples of RANDARRAY.

Figure 10-51 shows the RANDARRAY function being used with no arguments. It behaves like the RAND function and returns a random number between 0 and 1 to a single cell. Remember, the default of RANDARRAY is to return a decimal value.

And in Figure 10-52, the RANDARRAY function returns a random integer number between 5 and 250. It is behaving like RANDBETWEEN in this example.

Now, these examples show us nothing that we could not have achieved with the existing RAND and RANDBETWEEN functions. They are only shown to provide an extensive understanding of using RANDARRAY and to show that the characteristics of both existing random number–generating functions exist within RANDARRAY.

A key advantage of RANDARRAY is clearly its ability to return an array and therefore be used within our dynamic array formulas.

In Figure 10-53, the formula returns five rows of values that are between 1 and 10. The columns argument is omitted, so a single column of values is returned. The integer argument is also omitted, so RANDARRAY returns decimal values.

=RANDARRAY(5,,1,10)

This example not only demonstrates an array being returned but also RANDARRAY returning decimal values larger than 1. This is something that the RAND and RANDBETWEEN functions alone do not offer. RANDBETWEEN returns integers only, and RAND returns decimal values, but only between 0 and 1.

Finally, in Figure 10-54, all arguments of RANDARRAY are completed. An array of five rows and five columns is returned with integer numbers between 1 and 500.

=RANDARRAY(5,5,1,500,TRUE)

Pick a Name from a List at Random

A practical example of using the RANDARRAY function is to return a value at random from a list. In this example, we want to return a name at random from a table named [tblNames].

The random value returned by RANDARRAY is used as the row number to return the name from. This is the row of the table, not the row of the spreadsheet. The INDEX function is used to return the name from that row of the [tblNames] table.

A different name is returned every time the worksheet calculates. The F9 key is pressed to run calculations on the worksheet.

Shuffle a List of Names

In this example, we will shuffle a list of names, changing the order every time the sheet is calculated. This is a solid example of dynamic arrays in action, as we are now returning an array that is dynamic.

The RANDARRAY function has been inserted into the by array argument of the SORTBY function. So, the names are sorted by randomly generated numbers.

The number of rows to return has been calculated by using the ROWS function on [tblNamesList]. No other arguments in RANDARRAY are used, so the function is returning decimal values between 0 and 1.

FREQUENCY Function

The FREQUENCY function calculates how often values occur within a range of values (known as bins). The results are always returned as a vertical array.

The score must be greater than the interval value to be grouped in that range. So, the bins array can be read as 1–50, 51–65, 66–80, 81–90, and >90.

The FREQUENCY function always returns one extra value to the number of intervals in the bins array. This extra value is the count of values greater than the largest interval.

To cater for this behavior, the formula was entered into cell F3, one cell before the range specified for the bins array.

TRANSPOSE Function

Not many functions benefitted from the introduction of the dynamic array engine more than TRANSPOSE. The TRANSPOSE function was a sleeping giant shackled by Excel’s inability to natively handle arrays in older versions.

The shackles have now been broken for this function to show its value. Welcome to TRANSPOSE 2.0.

Many people are familiar with the transpose functionality that is available when you copy and paste data. This is very useful. With the TRANSPOSE function, you can automatically transpose the results of a formula or feed a formula with a transposed array.

The data in range B2:H4 has been rotated from being arranged along rows to being arranged down columns.

In versions of Excel that do not have dynamic arrays, the TRANSPOSE function is a horrible experience.

To achieve the same result for this example, you would need to select the output cells for the TRANSPOSE function. The range to be transposed is seven columns wide and three rows high. So, you would need to select an output range that is three columns wide and seven rows high and then type the TRANSPOSE function followed by pressing Ctrl + Shift + Enter.

This is not a feasible way to work. It is awkward, not dynamic, and required Ctrl + Shift + Enter to operate.

Thankfully, it now works like any formula – one cell, one formula that spills.

Let’s see a quick second example.

Earlier in this chapter, we used the UNIQUE function as shown in Figure 10-59. It is entered in cell B2 to compare columns in the range B4:G4 and return the distinct values.

We may need this array to be rotated into a vertical array. This could be to feed a Data Validation list or maybe to feed an Excel function that only works with vertical arrays (there are a few).

In Figure 10-60, the following formula is entered into cell I2:

=TRANSPOSE(UNIQUE(B4:G4,TRUE))

PivotTable Style Report Using Formulas

A fantastic use of the TRANSPOSE function is how it can be used to produce labels for a PivotTable style report.

We saw an example earlier in the chapter of the SEQUENCE function generating time-series data across columns for a PivotTable style report. This is simple with SEQUENCE as it has a columns argument, and this is great for numeric values. But to get text values dynamically displayed across columns, TRANSPOSE is superb.

Figure 10-61 shows a table named [tblSales]. This will be used as the source data for our pivot style report.

We will create a report that displays the [Product Name] values as row labels and the [Store] values as column labels. To show the [Store] values across columns, they will need to be transposed. The values in the [Total] column will be summed for each product and store.

In Figure 10-62, the following formula is used in cell C2 to create the column labels of the report. The distinct values from the [Store] column are sorted and then transposed.

=TRANSPOSE(SORT(UNIQUE(tblSales[Store])))

This is the reverse of the previous two examples where the TRANSPOSE function was used to switch data that was arranged horizontally to vertically.

By using dynamic array formulas based on a table, we have a completely dynamic solution here that updates automatically, unlike PivotTables. It also provides greater flexibility than a built-in tool like PivotTables would allow.

Sum Based on Complex Criteria

Let’s dive straight in and see the SUM function performing a sum that is based on complex criteria. We need to perform both AND and OR logic in the criteria of this formula.

Figure 10-63 shows the first few rows of a table named [tblSales]. This is the source data that our formula will be using.

When performing formulas such as this, the “+” operator is used to stipulate OR logic between conditions, and the “*” specifies AND logic. An extra set of brackets surround the OR logic part of the formula to force that operation to calculate before the AND operation.

We saw this example in Chapter 9 when we covered the SUMPRODUCT function in detail. In modern Excel, SUMPRODUCT is no longer required for this work. For a detailed breakdown of how this formula works, visit the “SUMPRODUCT Function” section of Chapter 9.

Sum with Arrays

As formulas in Excel can now handle arrays, this reduces the requirement for using intermediary formulas that store their results in columns.

In Figure 10-65, we have daily sales transactions in a table named [tblDailySales]. We would like to sum the values that occurred at a weekend only. In this example, the weekend is classified as Friday to Sunday.

In non-dynamic array–enabled versions of Excel, the typical approach would be to use the WEEKDAY function in a column to return the number that identifies the day of the week. A function such as SUMIFS can then be used that tests the values from the weekday column to sum the required values.

The WEEKDAY function returns a number that identifies the day of the week. The week starts from a Monday as 1 and ends with Sunday as 7. The formula tests if the value returned by the WEEKDAY function is greater than 4.

The results of this logical expression are multiplied by the values in the [Total] column and then summed by the SUM function.

This formula can be achieved in non-DA versions of Excel also, but it requires the user to press Ctrl + Shift + Enter on completion to specify an array formula.

Sum Based on Multiple Columns

The SUM function is extremely versatile and can sum values of any array dimensions and include any criteria.

Figure 10-67 shows a matrix of sales data in range D1:I16. It contains product names in range D2:D16 and location names in range F1:I1.

You would not be able to sum values from multiple columns (F to I) like this with the SUMIFS function. So, this demonstrates the versatility of SUM nicely.

Taking it a step further, we can set both row and column criteria, creating a two-way SUM.

In Figure 10-69, the following SUM function is summing the values at the intersection of the rows for the product stated in cell A2 and the column for the location stated in cell B1. How cool is that?

=SUM((D2:D16=A2)*(F1:I1=B1)*(F2:I16))

DA Formulas with Conditional Formatting

Unfortunately, Conditional Formatting does not recognize a spill range. You cannot reference a spill range directly in a Conditional Formatting rule. And if you select a spill range and apply a Conditional Formatting rule to it, it will not update dynamically with the spill range.

Figure 10-70 shows a dynamic report with two spill ranges – one in range B3 and another in range C3. The spill range in cell C3 returns the total sales for each product.

We will apply a Conditional Formatting rule to change the cell color for all values that are greater than or equal to 1700.

The approach we will take to make the best of an unfortunate situation is to select more cells than is required. The dynamic array formula may expand the spill range in the future, and we want the rule to apply to the additional cells.

As mentioned, Conditional Formatting cannot work directly with a spill range, so we must resort to selecting cells on the grid and plan a little for future changes.

In Figure 10-71, two additional cells are selected beyond the spill range to cater for the spill range expanding. The Conditional Formatting rule is then created as usual.

1. 1.

   Click Home ➤ Conditional Formatting ➤ New Rule.

    
2. 2.

   Click Format only cells that contain.

    
3. 3.

   Select greater than or equal to from the list of logical operations and type “1700” into the box provided (Figure 10-72).

    
4. 4.

   Click Format and specify the formatting you want to apply.

    
5. 5.

   Click OK.

    

The Conditional Formatting rule is applied to the selected range as shown in Figure 10-73. If the spill range expanded within the additional two selected cells (we could have selected more), the formatting rule would automatically be applied.

DA Formulas with Data Validation

The good news is that Data Validation works with dynamic array formulas. You cannot enter DA formulas directly in the Data Validation window, but you can reference spill ranges on the grid. When the spill range updates, the Data Validation rule will update with it.

For this example, we will use the UNIQUE and SORT functions together to create a source for a Data Validation list. Figure 10-74 shows a table named [tblSubscribers].

We will create a dynamic Data Validation list from the countries in the [Country] column. If subscribers are added from new countries, the Data Validation list will automatically update to include them.

In cell B3, the following formula is used to create a distinct list of the countries and is sorted in ascending order (Figure 10-75):

=SORT(UNIQUE(tblSubscribers[Country]))

Figure 10-77 shows the Data Validation list. The spill range in cell B3 will update with the data in the [tblSubscribers] table, and the Data Validation list will automatically update as it references the spill range.

DA Formulas with Charts

It is further good news for charts in Excel, as they also can be used with dynamic arrays. When the spill range of a dynamic array updates, the chart will update with it.

However, the spill reference cannot be used directly in charts. Also, if a chart is created by selecting the spill range on the grid, it does not update with the spill range.

We need to define a name for each spill range and then use the defined name in the appropriate areas of the chart.

Figure 10-78 shows two spill ranges – one in range B3 and the other in range C3. The dynamic array formulas are summing the total values for each product and sorting the product names by their total sales values.

This is a cool technique, and we demonstrated how to create this report earlier in the chapter with the SORTBY function.

We will insert a column chart that is connected to these spill ranges, so that the chart always sorts the product names by their sales totals in descending order.

The next step is to insert the chart and then connect it to our defined names.

1. 6.

   Click Insert ➤ Insert Column or Bar Chart ➤ Clustered Column.

    
2. 7.

   With the chart selected, click Chart Design ➤ Select Data.

    
3. 8.

   Click the Add button in the Legend Entries (Series) area on the left.

    
4. 9.

   Type “Product Sales” for the Series name and type the following reference in the Series values box (Figure 10-80). Click OK.

    

Even though the defined name has workbook scope, it is essential that the references in charts include the sheet name.

1. 10.

   Click the Edit button in the Horizontal (Category) Axis Labels area on the right.

    
2. 11.

   Type the following reference into the Axis label range box and click OK (Figure 10-81).

    

Figure 10-82 shows the completed column chart based indirectly on the spill ranges. This chart will automatically update to reflect any changes in the spill ranges.