Compare Two Lists

The MATCH function provides a neat way to compare two lists and identify the differences.

When comparing two lists, if a value is matched, its relative position is returned; otherwise, the #N/A error is returned.

In Figure 11-3, the following formula is entered in the [Match] column of the first table. It checks for matches for the values in the [Number] column of the first table within the [Number] column of the second table [tblSecond]:

=MATCH([@Number],tblSecond[Number],0)

You can see that there are three names in the first table that are not matched in the second table. This is shown by the #N/A error message.

Although this works, it is not a very polished result.

The MATCH function can be nested within other functions to display an alternative value or to perform an action based on the result. It could also be inserted into a Conditional Formatting rule to visualize the differences between the two lists better.

The ISNUMBER function returns TRUE if MATCH returns a number relating to the value’s position and FALSE if the #N/A error is returned. IF uses these results to then display the “Yes” and “No” values.

Dynamic Column Index Number in VLOOKUP

We covered the VLOOKUP function in Chapter 7 in great detail, including tricks for returning the column index number as an alternative to typing in the column number directly.

The MATCH function is a terrific method for fetching the column index number for VLOOKUP. It provides an approach that is robust, durable, and dynamic.

We will use the MATCH function to search for the header text of the column we want, along the header row of the lookup table, and return the relative position of that column to VLOOKUP.

Figure 11-5 shows the lookup table named [tblProducts]. It contains information about products being sold including the [P Code] column. These values are used to uniquely identify a product.

The MATCH function searches the header row of [tblProducts] to return the column number of the header stated in cell D1. The header text of the value in cell D1 and that in the lookup array must match exactly.

Notice the absolute reference to the P Code lookup value. This technique was covered in Chapter 4, when we covered tables in detail. Also, the row has been made absolute in the reference to the header text in cell D1.

These references have been applied so that the VLOOKUP formula can be filled across the [Category] and [Price] columns too.

No need to manually edit the column number returned, as the MATCH function returns the required column number automatically. It is also durable and will not break if someone changed the order of the columns in [tblProducts] or inserted a new column before the column being returned.

Sort a Range by Drop-Down List Value

Using the MATCH function combined with the SORT function, we can create a nice effect for our Excel reports and models, where a user can sort a range by selecting the column to sort by from a drop-down list.

This is a great technique, as it makes our reports interactive from functionality on the grid, instead of a user having to know how to use built-in Excel features. It also gives us more control to setting up how a feature of a report should be used.

The SORT function requires that the column to sort by is entered as an index number. This is a perfect scenario for the MATCH function to assist.

Figure 11-7 shows a table named [tblCoffee] that contains aggregated sales data for six different products.

On a different sheet, we would like to use the SORT function to sort this data dependent upon the value in a cell.

The same MATCH function is used twice in the formula. The first MATCH is used to return the sort index. It matches the text in cell B2 to the headers in [tblCoffee] and returns the column number.

The second time the MATCH function is used is within an IF function. The IF function is used here to ensure that the correct sort order is applied.

It checks if the column stated in cell B2 is column 1, the [Product Name] column. If it is, the SORT function is instructed to order the list in ascending order. If the selected column is not column 1, then a descending sort order is applied.

To avoid the repetition of the same MATCH function in one formula, we could define a name for the MATCH function and use that name in the formula instead. This would help us to create a more meaningful and concise formula.

The reference to B2 has been made an absolute reference for the defined name. In Figure 11-9, you can see the sheet name precedes the $B$2 reference. This was automatically entered by Excel, as it is required for a defined name with workbook scope.

This formula is much more concise and meaningful.

XMATCH Defaults to an Exact Match

The most used match type is the exact match, and this is the default match type of XMATCH, unlike the MATCH function. So, when performing an exact match, we do not need to answer the third argument.

In Figure 11-10, the following formula is entered in cell F3 to return the relative position of the name stated in cell F2. Only the first two arguments have been specified.

=XMATCH(F2,B3:B7)

Returning the Position of the Last Match

The search mode argument of the XMATCH function provides the ability to search an array from last to first. This is useful for returning the position of the last match in an array.

Figure 11-11 shows team names in range B3:B8 and 12 rounds of results: W = Win, D = Draw, and L = Loss. This data is on a worksheet named [Horizontal].

In Figure 11-12, the XMATCH function has been used to return the round that a team recorded their first win and the number of rounds it has been since their last win.

To return the number of rounds since a team won their last match, we need to return the relative position of the last match of a “W.”

Generating Random Sample Data

CHOOSE is great for generating random sample data that can be used for testing formulas and models that you create. In fact, much of the sample data provided in this book was generated using these methods.

The RANDARRAY function returns an array containing ten rows of integer values between 1 and 3. The CHOOSE function returns the region from its list based on the index number provided by RANDARRAY and spills the results to the grid.

Remember, when using the random number–generating functions – RAND, RANDBETWEEN, and RANDARRAY – they return new results every time the worksheet calculates.

So, after producing the random values, you want to replace the formulas with the values only. There are many techniques to accomplish this, but my favorite is the following:

1. 1.

   Select the range you want to convert to values.

    
2. 2.

   Position the cursor on the border of the selection until you see the “move” cursor (four arrows facing away from the center).

    
3. 3.

   Right-click and drag away and then back to the range and release the mouse.

    
4. 4.

   Click Copy Here as Values Only (Figure 11-15).

    

To complete the sample data, we will generate random values for the [ID], [Date], and [Total] columns. We will not be using the CHOOSE function for this task, despite it being the focus of this part of the chapter, but it is important to finish the job.

To generate the ID values, we could use RANDBETWEEN or RANDARRAY, but I like to use SEQUENCE for this task as it returns a series of values in order (Figure 11-16).

=SEQUENCE(10,,1422)

The DATE function is used in the min and max arguments of RANDARRAY to specify the start and end dates of the date range. The SORT function is added to order the dates returned in an ascending order.

Finally, the following formula is used to produce the values for the [Total] column (Figure 11-18). The RANDARRAY function returns decimal values between 10 and 300. These values are then rounded to two decimal places.

=ROUND(RANDARRAY(10,,10,300),2)

Return a Range Based on a Cell Value

In this example, we want to return a range dependent upon the value selected from a drop-down list.

Figure 11-19 shows a worksheet named [Data] that contains product sales for four different locations – Edmonton, Argyll, Regent, and Euston.

When a different location is chosen in cell C4, the formula returns the required results.

In a dynamic array version of Excel, the results of this formula are spilled to the cells below.

Sure! In this example, the total could have been produced by referencing the range in C5:C10. In a dynamic array version of Excel, the spill range could be used, =SUM(C5#), but in other versions the fixed range could be used, =SUM(C5:C10).

However, we wanted to demonstrate the use of the CHOOSE function returning a range based on a cell value and passing this directly to the SUM function. This formula would sum the values for the chosen location without the need to return the values to the worksheet.

Choosing a Function from a List

The technique of selecting a different function from a list is one of my favorite uses of the CHOOSE function. The list can contain any functions you want. In this example, the SUM, AVERAGE, and MAX functions are made available for a user to choose from.

In Figure 11-21, there is a drop-down list in cell B2, so the user can easily select the function they want to use. The table of product names and total values is named [tblSales].

The function list in range E1:E3 is shown on the same sheet to simplify the demonstration of the technique. This would typically be on a hidden sheet.

Simple and very effective. We have seen in the examples so far how easily the CHOOSE function can be used to insert some interactivity into an Excel report or model, and how a user could change the calculation being applied or the range that is being used.

Using CHOOSE with Form Controls

A few of the form controls in Excel, such as the list box, combo box, and option buttons, return an index number to represent the selection made in the control. This makes them ideal to be used with the CHOOSE function, as CHOOSE also uses an index number to identify the selection made.

Continuing with the previous example, let’s provide a form control for the user to make the function selection instead of the drop-down list. We will use the option button control for the example.

Figure 11-22 shows three option buttons, one for each of the three functions to choose from – SUM, AVERAGE, and MAX. The table of product names and totals is named [tblProductSales].

Cells C2 and D2 are currently empty. We will use the CHOOSE function in cell C2 to return the chosen function name, and in cell D2, the CHOOSE function will run the chosen function and return its result.

1. 2.

   Right-click the option button and click Edit Text. Replace the existing text with the name of the function, that is, Sum.

    
2. 3.

   Repeat steps 1 and 2 for each option button.

    
3. 4.

   With an option button selected, click Developer ➤ Properties.

    
4. 5.

   On the Control tab of the Format Control window (Figure 11-24), click in the Cell link box and click the cell you want to connect the option button with. Click OK.

   Cell F1 has been used in this example. This means that the index number of the selected option button will appear in this cell.

    

Now that we have our option button controls inserted, let’s get back to the CHOOSE function.

Indirect Table Reference in VLOOKUP

In this first example, we will use the INDIRECT function to indirectly reference a table.

We want to use the VLOOKUP function to return the grade associated with the score a student achieved in a subject. Each subject has its own grading system, so they have their own separate lookup tables. VLOOKUP will know which lookup table to refer to because it is specified in a cell. INDIRECT will perform the task of converting this cell value into a reference for VLOOKUP.

Figure 11-27 shows the three lookup tables – [French], [Maths], and [Art].

The INDIRECT function is nested in the table array argument of VLOOKUP. It takes the value from the [Subject] column for the current row and converts it to a reference. The values in the [Subject] column match the names of the three lookup tables, so this is a simple reference to make.

Referencing Table Columns with INDIRECT

In this example, instead of referencing an entire table we will reference a specific column within a table.

We have four tables, each containing sales data for a different city – [tblManchester], [tblLeeds], [tblPlymouth], and [tblLincoln]. We want to use the SUM function to sum values from the table specified by a cell value.

Figure 11-29 shows the four different tables. We want to sum the values in the [Total] column for the specified table. To do this, we need to build a reference from some text strings and a cell value in INDIRECT.

The reference is made up of three separate sections, the “tbl” prefix, the table name, and then the column reference as text “[Sales].”

In Figure 11-30, the following formula sums the [Sales] values from the table stated in cell B3:

=SUM(INDIRECT("tbl"&B3&"[Sales]"))

Referencing Other Worksheets

The INDIRECT function can be used to dynamically reference other worksheets using the value in a cell.

In Figure 11-31, we have sales values like the previous example, but this time they are ranges on separate worksheets instead of separate tables. There are four worksheets – [Milton Keynes], [Derby], [Hartlepool], and [Gloucester]. The sales values in each worksheet are in range C3:C7.

Within the INDIRECT function, the reference to the sum range is made up of three parts. There is a single quotation ('), followed by the value in cell B3, and then the final string. The final text string contains the closing single quotation, an exclamation mark, and then range C3:C7.

When the sheet name in cell B3 is changed, the formula updates to sum the correct values.

In Chapter 1, we covered the topic of sheet references. If any aspect of the references is unclear, I encourage reading through that chapter and the dedicated chapters for defined names and working with tables. A strong understanding of how to reference cells is imperative for advanced formula skills.

Pivot Style Report with Dynamic Row Labels

We have created a couple of pivot style reports in this book using dynamic array formulas. In this example, we take it further by using the INDIRECT function to create dynamic row labels. The example uses dynamic arrays and so requires an Excel for the Web, Excel for Microsoft 365, or Excel 2021 version of Excel.

The report will be created using the table shown in Figure 11-33. This table is named [tblSales]. The image shows a snapshot of the first nine rows of the table.

For the pivot style report, we will display the last N months along the column labels using the SEQUENCE formula on the [Date] column that we detailed in Chapter 10.

For the row labels, we will provide a drop-down list for the user to pick between the [Sales Rep] column and the [Region] column. The INDIRECT function will be used to create the functionality for a user to switch between columns via a drop-down list.

A SUMIFS function will then be used to total the values in the [Sales] column for the month specified in that column and the sales rep or region value stated in that row.

Figure 11-34 shows the setup of the pivot style report before we introduce the dynamic row labels and sales totals.

A Data Validation list is added to cell C4 to make it easy for a user to pick the column they want to use for the row labels.

To create the dynamic row labels, we will create a distinct list for each of the [Sales Rep] and [Region] column values using dynamic array formulas. We will then define names for these spill ranges and use the INDIRECT function to dynamically refer to these names.

In Figure 11-35, the following formula is entered in cells A1 and C1 of a sheet named [Lists] to create the distinct and ordered lists:

=SORT(UNIQUE(tblSales[Sales Rep]))

In Figure 11-36, the Name Manager shows the two defined names established for each spill range.

The [Sales_Rep] name has been defined for the “Sales Rep” value in the drop-down list. You cannot use spaces in a name, so the underscore was used as the delimiter.

This formula refers to the table column using a reference that is a combination of text strings and the value in cell C4, like we covered a couple of examples previously.

The decision to use defined names instead of a table column reference in the main example was because in a “real-world” scenario, we may require those distinct lists for other formulas or tasks, such as the labels of a category axis in a column chart. The defined names can be reused as many times as required.

In the criteria range 1 argument of SUMIFS, the INDIRECT function is used to refer to the correct table column as stated in cell C4.

The spill ranges for the row labels and column labels are both referenced. The “+” operator is entered before the D4# reference in the EOMONTH function to enable the spill range reference. EOMONTH does not natively work with spill ranges.

R1C1 Reference with INDIRECT

In addition to the A1 style of reference generally used with INDIRECT, the R1C1 reference style can also be applied. With the R1C1 style, row and column numbers are used to specify the cell reference. This is especially useful for columns as the A1 style does not enable us to refer to a column using a number.

A MATCH function is used to return the row number of the name specified in cell B2. The R1C1 reference of INDIRECT refers to the row and column numbers of the sheet, so four is added to this value to offset the four rows above the names in the table (B1:B4).

The COLUMNS function is used to return the number of columns in the [tblMonthly] table. One is added to account for column A to the left of the table.

This is all then joined together to form the R1C1 reference. The completed R1C1 reference used in Figure 11-39 would read R9C7. FALSE is entered for the a1 argument to specify that an R1C1 reference style has been used.

The INDIRECT function can also be used to return a range.

In this formula, two INDIRECT functions using R1C1 reference styles are used to form a range by entering a colon (:) between them. This is given to a SUM function to sum the values.

The first INDIRECT is fixed to start from column 3, while the second INDIRECT returns the final column in the table using the COLUMNS function with one added to it. MATCH is again used to return the row number for the name entered in cell B2.

Simple Examples

Let’s begin by looking at some simple examples of OFFSET to get a strong understanding of how it operates before we progress to more practical examples.

The height and width arguments are omitted, so a single cell reference is used, as this matches the height and width of the start reference.

Functions such as COUNT, MATCH, and COLUMNS are often used to calculate the number of rows or columns to offset. So, let’s see an example.

The start reference is G4. This is the top-left corner of the range that we are returning the value. This start reference can be any reference. However, it is often the top-left corner cell of the range or table from which you are returning the reference.

The MATCH function is used to find the number of rows to offset, and an input cell (H2) is used for the number of columns to offset.

Both these examples return a single cell reference, but we will start to look at returning multiple cell ranges next.

Sum the Values in the Nth Column

The OFFSET function can return a range of cells, and this range can be given to another function for use. This delivers the ability to provide dynamic ranges to functions.

The start reference is cell B5, so the range to return is offset by one row. If the OFFSET function was given a start reference of B6 (same row that the numbers start), we would not need to offset a row. However, it is important to be consistent in the way you work, and I like to use the top-left corner cell. The number of columns to offset is specified by the value in cell B3.

The ROWS function is used to ascertain the height of the range to return. The ROWS function returns the number of rows in the table, excluding the headers.

The width of the range is a fixed value of 1. This did not need to be stated, as if omitted, the same width as the start reference is used. However, it is included for a fully documented function.

The cell input being provided may be the month name instead of the month number. This is no problem, as a function such as MATCH can be used.

One is subtracted from the result returned by MATCH. This is because the MATCH function is searching for the matching month name in all headers of the table. And because the start reference is in the first column of the table, MATCH returns a column number one more than is required.

This is a key point to understand when familiarizing yourself with OFFSET. It is not using the column or row number of the table or range like the INDEX function does, it is using the number of columns or rows away from the start reference.

Of course, instead of subtracting one, we could have specified a start reference as the first column to the left of the table. You may prefer this adaptation to get OFFSET to return the correct range. However, as mentioned before, I like to keep consistency to my approach, so I know what to expect.

Return the Last X Columns

Instead of returning a range to another function, it could be returned to the worksheet.

Figure 11-45 shows a table named [tblResults] containing 12 rounds of competition results for six different teams. There are three types of result: “W” = win, “D” = draw, and “L” = loss.

From another sheet, we want to return the last six results for a specified team. It is common in sports dashboards to see recent results for teams like this.

The COLUMNS function is used to find the starting column. COLUMNS returns the number of columns in the table, and six is then subtracted from this result to return the correct number of columns to offset.

The returned array is specified as one row high and six columns wide. Although these values are entered into the formula, they could be provided by a cell value, so a user can specify the number of results to return.

The start reference used in both formulas is a reference to cell B2 of the [Results] worksheet. This is the cell in the top-left corner of the table. This works great, but an alternative approach could be to refer to the table itself.

The OFFSET function returns a reference with the same dimensions as the start reference if the height and width arguments are not stated. This is important to remember if you are going to use multiple cell start references.

OFFSET with COUNTIFS

The OFFSET function can be used to provide a range to any function that requests one. We have seen an example of this already with the SUM function. Let’s use the formula from this example with the COUNTIFS function to return the number of wins for a specific team in the last X number of rounds.

The COUNTIFS function uses the range returned by OFFSET for its criteria range and counts the occurrences of “W.”

Introduction to the INDEX Function

The power of the INDEX function is really down to its simplicity and flexibility. The role of the INDEX function is to return the value or reference of a cell from a specified row and column number, in a given range.

A key part of that INDEX function description was its ability to return a value or a reference. It is incredibly helpful in accessing values in ranges, arrays, and spill ranges, but also creating dynamic references for other formulas or Excel features such as charts.

The description also stated that we need to provide the row and column number from which it returns the value or reference. This indexing approach is its superpower. We can state the row and column number in an absolute manner or find them using another function such as MATCH, COLUMN, COUNTA, and so on.

The sky is the limit for the INDEX function.

Let’s take a look at its syntaxes. Yes, it has two different syntaxes (Figure 11-48).

When using the INDEX function, you will nearly always be using the first syntax. It is only the final INDEX function example in this book that demonstrates a use of the second syntax.

Basics of the INDEX Function

To get a solid understanding of how the INDEX function operates, we will start with some basic examples and progress to the more exciting stuff.

The purpose of this example is to show that the INDEX function returns from the row and column numbers of the array provided in the first argument, and not the row and column numbers of the sheet. This is a common mistake when beginning with INDEX.

An interesting aspect of the INDEX function is that it returns the values or reference of all cells in a row or column, if a row or column number is omitted.

This is very useful for users with a dynamic array–enabled version of Excel. It is a powerful method to access elements of an array. And we will see this technique later in this “INDEX Function” section and again later in the book.

Let’s see exactly what I mean by this using the same range of B3:C8.

In Figure 11-50, the following INDEX function is entered in cell F3 and returns a spilled array of all values from the fifth row of the range B3:C8. Notice the comma after the row num argument. This is required.

=INDEX(B3:C8,5,)

These returned arrays can be fed to an aggregation function such as SUM or AVERAGE or given to a chart via a defined name. There are fantastic possibilities for such a simple little function that does something so awesome.

In all the examples so far, the column and row numbers have been entered directly into the formula. These numbers are normally returned by a function such as MATCH, COLUMNS, ROWS, or COUNTA for a dynamic and more robust method. Form controls such as the list box and option buttons are also great for serving index numbers to the INDEX function.

The most popular function to be affiliated with INDEX is the MATCH function. And in modern versions of Excel, it is the XMATCH function. With these functions, we can return the row or column number that matches a value we are looking for.

The MATCH function is used to return the row number from range B3:B8 where there is a match for the name. In this example, the name “Sergei” is used. The MATCH function returns the row number 2. INDEX then returns the value in row 2 and column 2 from range B3:C8.

This example uses a sheet range for the array that INDEX returns from and for the lookup array that MATCH searches in. This is adequate, but using data formatted as a table is more commonplace and provides additional advantages. Therefore, many, though not all, forthcoming examples will be working with data formatted as a table or an array returned by another function.

INDEX and MATCH/XMATCH for Versatile Lookups

Let’s continue with further examples of the fantastic combination of INDEX and MATCH or INDEX and XMATCH functions. Together they provide an extremely versatile lookup formula that is available to all versions of Excel.

We saw a workaround in Chapter 7 that used the CHOOSE function to enable VLOOKUP to return a value from a column to the left of the lookup column. Now, the better alternative to that approach is the INDEX and MATCH combination. And these functions are available in all versions of Excel, unlike the newer XLOOKUP and FILTER options.

With INDEX and MATCH, you can look for a value down any column of a range or table (VLOOKUP only looks down the first column) and return from any column of the range or table.

In Figure 11-53, we have two tables. The table on the right is named [tblGrades]. It contains four different grades, each one associated with achieving a particular score. This is the lookup table, and notice that the [Grade] column is to the left of the [Score] column.

The table on the left is named [tblScores] and contains the scores achieved by different people.

This formula demonstrates the versatility of INDEX and MATCH. It makes it simple to look for and return a value from any column of a table. It also highlights that INDEX-MATCH works brilliantly with data formatted as a table.

And finally, this formula shows the use of a range lookup. The 1 in the match type argument of MATCH specifies to return the exact match or next item smaller than the lookup value.

In this example, the rows in the lookup table [tblGrades] are jumbled. This is a little unrealistic but demonstrates the robustness of the XMATCH function. It continues to function correctly. When using MATCH to look in ranges, the values in the lookup array must be in ascending order.

Two-Way Lookup with INDEX and MATCH/XMATCH

A two-way lookup can be created by using two MATCH or XMATCH functions with INDEX – one to search for a value down a column and another to search along a row.

Figure 11-55 shows a matrix of data. It contains prices for different holiday accommodations. The price is dependent upon the type of accommodation and the location. The type of accommodation is labeled in range B3:B7, and the different locations are labeled along range C2:F2.

We need a lookup formula to return the price for a given accommodation type and location. We will write this formula on another sheet. The sheet containing the price matrix is named [Prices].

In Figure 11-56, the following formula uses INDEX and MATCH to return the prices for the values stated in the [Accommodation] and [Location] columns of the table:

=INDEX(Prices!$C$3:$F$7,

MATCH([@Accommodation],Prices!$B$3:$B$7,0),

MATCH([@Location],Prices!$C$2:$F$2,0)

)

It is important in this formula that the array given to INDEX is the same height as the vertical lookup array and the same width as the horizontal lookup array given to the MATCH functions.

Using Wildcards with INDEX and MATCH/XMATCH

The MATCH function will handle wildcard characters natively. This makes it simple to perform partial text matches with the INDEX and MATCH combination. With the XMATCH function however, a wildcard character match needs to be specified.

Figure 11-57 shows a lookup table named [tblTargets]. It contains target values for different cities. The cities are not alone in the cell though. The cells contain a string of city followed by country.

We need to perform a lookup using the city name only, so will perform a partial match for any value that begins with the city we are looking for.

In Figure 11-58, the following formula uses the asterisk wildcard character in the MATCH function. A string is created by joining the value in cell C3 with the asterisk character. The wildcard character match does not need to be specified with MATCH.

=INDEX(tblTargets[Target],

MATCH(C3&"*",tblTargets[City],0)

)

Last Occurrence of a Value with XMATCH

One of the advantages that XMATCH has over MATCH is the ability to search an array from last to first. By searching from last to first, XMATCH can be used to return a value relating to the last match in a row or column.

This option is specified in the search mode argument of XMATCH (Figure 11-59).

In Figure 11-60, the INDEX and XMATCH combination is used in cells G3 and H3 to return the date and number of attendees for the last occurrence of the training course entered in cell F3.

Let’s see another example. This time, we will use INDEX and XMATCH to search both down a column and along a row.

Figure 11-61 shows the results of 12 rounds of games for six different teams. The teams are listed down column B and the results arranged along rows. “W” represents a win, “D” is a draw, and an “L” is a loss.

This data matrix is stored on a sheet named [Results]. On another sheet, we will use a formula to return the round from range C2:N2 for the last win for each team, that is, the last win for “Tigers” was in round “R10.”

There is a nested INDEX and XMATCH in this formula to return all results for the team name entered in the corresponding row of range B3:B8. This is important as the team names listed on this sheet are in a different order to those on the [Results] sheet.

Notice the comma entered after the XMATCH function. Remember, by omitting the column num argument, an array containing the values for all columns of the given range is returned.

The outer INDEX and XMATCH combination then searches last to first in this array for the “W” and returns the column number for the final occurrence of the “W.” INDEX then returns the round number from range C2:N2 for that column.

Last Value in a Row/Column

The INDEX function makes it simple to return the last value in a row or column. The versatility of this function is its superpower, and by combining it with the necessary functions, we can return whatever value or reference we require.

I like the use of the ROWS function (or COLUMNS for last value in a row), especially when working with data formatted as a table. Alternative methods to return the last row/column in a range/table include the use of the COUNT or COUNTA functions.

Sum Values in the Last Row/Column

The INDEX function makes it very simple to return all values in a row or column. Armed with this, let’s return all values in the last column of a table and aggregate them with the SUM function.

In Figure 11-64, the following formula is entered in cell D2. It uses the COLUMNS function to return the last column in the [tblMonthly] table. The row num argument of INDEX is ignored to return all values in the column. SUM then totals all the values in the returned array.

=SUM(INDEX(tblMonthly,,COLUMNS(tblMonthly)))

Last Value in a Row/Column for a Specific Match

Taking it further, we now want to return the last value in a row for a specific match. The following formula is entered in cell C2 to return the last value in the row that matches the name entered in cell B2 (Figure 11-65). The MATCH function is used to return the row number.

=INDEX(tblMonthly,MATCH(B2,tblMonthly[Name],0),COLUMNS(tblMonthly))

Most and Least Frequently Occurring Values

In Chapter 8, we discussed different functions in Excel for calculating averages, and there were three different MODE functions. The MODE functions return the most frequently occurring value in an array, but they only work with numeric values.

In this example, we have a table of ice cream flavors purchased from two different regions, north and south. We want to return the most popular and least popular ice cream flavors.

The same COUNTIFS function is used twice in the formula. They return the number of occurrences for each ice cream flavor. This is returned as an array of values, for example, {7;7;7;9;8;8…}.

The MAX function is used in the lookup value argument of MATCH. It returns the maximum value from the first COUNTIFS array. This is number 9 in this example and relates to the “Cookies & Cream” flavor.

The MATCH function searches for number 9 in the second COUNTIFS array and returns the position of the first instance of this number. INDEX then returns the value (flavor) in that position.

With the COUNTIFS function providing a key role in this formula by returning the number of occurrences of each flavor, we can easily incorporate extra conditions, by adding them to the COUNTIFS.

For the “South” region, there are two flavors that are tied as the most frequent. The INDEX and MATCH combination will only return the first instance of these two flavors. So, the one that occurs first in the [Flavour] column is returned.

To return all flavors when there is a tie, the formula can be adapted to use the FILTER function. The FILTER function is covered in detail in Chapter 13 and is only available to users of Excel 365, Excel 2021, and Excel Online.

The MATCH function is adapted into a logical test between the two COUNTIFS functions. This returns an array of TRUE and FALSE values.

The INDEX function is swapped for FILTER to return the flavors where the logical test evaluates to TRUE. SORT and UNIQUE are added to order the flavors and prevent duplicates.

Returning a Range with INDEX

A special feature of INDEX is that it is a function that can return a reference. There are only a few functions in Excel that can do this. Others include, but are not limited to, OFFSET, XLOOKUP, and IF.

As INDEX is an incredibly versatile function that accepts numeric inputs for the rows and columns of a reference, it makes it a very effective way to create rolling ranges.

Figure 11-69 shows a table named [tblResults]. It contains 12 rounds of results for six different teams. You may recognize this data from an earlier example; however, it was not formatted as a table previously.

We want to use a formula to return the last five rounds of results from the table for each team. When additional columns are added to the table (new rounds of games), the formula continues to return the last five.

In Figure 11-70, the following formula is entered in cell C3. A key aspect of this is the use of the range operator, the colon (:), to create a range with the two references returned by INDEX.

Instead of returning the range to the grid to be spilled across five different columns, the TEXTJOIN function can be added to combine the returned values in one cell (Figure 11-71). In the following formula, the hyphen “-” is used as the delimiter between the five results:

=TEXTJOIN("-",,

INDEX(tblResults,MATCH(I3,tblResults[Team],0),COLUMNS(tblResults)-4):

INDEX(tblResults,MATCH(I3,tblResults[Team],0),COLUMNS(tblResults))

)

Moving Average Formula

This technique to return a range can be used to create calculations on dynamic ranges. A common example of this is the moving average calculation.

In this example, we have a table of values named [tblDaily], and we will use a moving average formula to return the average for the last seven days only.

In Figure 11-72, the following formula is entered in cell D2. The INDEX function is used with the ROWS function to build a range for the last seven values in the [Value] column of [tblDaily]. The average is then performed with these values.

=AVERAGE(

INDEX(tblDaily[Value],ROWS(tblDaily)-6):

INDEX(tblDaily[Value],ROWS(tblDaily))

)

Working with Arrays and Spilled Ranges

The INDEX function is fantastic for working with arrays. It enables us to extract specific rows, columns, or cells from arrays and from the spilled ranges of other formulas. Let’s see some examples.

Creating a Top N List

A great example of the INDEX function extracting specific rows from an array is when used with SORT to create a top N report.

Figure 11-73 shows a table named [tblProductSales]. It is a small table for the purposes of this demonstration.

We want to return the top selling products, and this will be determined by sorting the data in descending order by the [Total] column using SORT. The number of rows (products) to return will be specified by a cell value. This makes it dynamic, and a user can easily change the number of products returned.

In Figure 11-74, the following formula is entered in cell B5. The SORT function is used to return an array of sorted product names for INDEX. The products table is sorted in descending order by column 4, the [Total] column.

=INDEX(

SORT(tblProductSales,4,-1),

SEQUENCE(C2),

SEQUENCE(,COLUMNS(tblProductSales))

)

Two SEQUENCE functions are then used – one to return the rows and another for the columns.

The first SEQUENCE function uses the value stated in cell C2 for the number of rows to return. If the value is 5, the SEQUENCE function returns an array of {1, 2, 3, 4, 5}. So, the first five rows are returned. The number of rows is dependent on the value in cell C2.

The second SEQUENCE function returns all columns of the table. The COLUMNS function is used to return how many columns there are. This is passed to SEQUENCE to generate, well, the sequence of column numbers. Notice that the rows argument is omitted in the second SEQUENCE.

Prefer the SORTBY function? No problem. This could be used instead of SORT. The following formula shows the SORTBY alternative for creating a top N report. This enables us to specify the [Total] column absolutely instead of referencing it as column 4.

=INDEX(

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

SEQUENCE(C2),

SEQUENCE(,COLUMNS(tblProductSales))

)

Extracting Headers from a Table/Range

Functions such as SORT, FILTER, and many others do not return headers with a returned array, especially when based on a data formatted as a table. This is a good thing, because headers are not data and therefore should be kept distinct.

This means that the headers are typed or pasted into cells when creating a report in Excel. This is not much of an issue and indeed has its benefits (we will create dynamic headers shortly). However, if you want to return the headers using a formula, then this is simple. And when we require specific headers, then INDEX is up to the task.

Figure 11-75 shows the following simple formula entered in cell B4 to return the header names. If a header is changed in the table, it will automatically update on the report.

=tblProductSales[#Headers]

The rank column is not part of the original table data, so this header needs to be entered manually.

If headers for specific columns were required, then INDEX could be used to specify the column index numbers. The following formula would return the column headers for columns 1 and 4 only.

=INDEX(tblProductSales[#Headers],,{1,4})

Returning Rows/Columns from a Spill Range

Extracting specific rows and/or columns from a spill range is no different to extracting them from a table or array.

For this example, let’s imagine we want to sum the total values from the spill range of the previous formula. This formula returned a spill range that is four columns wide. The [Total] column is the fourth column of the spill range. So, we need to specify that the SUM function uses column 4 only.

In Figure 11-76, the following formula uses INDEX to extract all values from the fourth column of the spill range B5#. The row num argument is omitted to return all rows, and the column is explicitly entered as 4.

=SUM(INDEX(B5#,,4))

Instead of entering the index value of 4 for the col num, sometimes using a function may be a better option, as it is dynamic. We will see examples of dynamically returning columns in the next example.

For now, in addition to being column 4, the [Total] column is also the last column in the spill range, so an alternative approach could include the COLUMNS function.

=SUM(INDEX(B5#,,COLUMNS(B5#)))

Returning Non-adjacent Columns from a Table with INDEX-MATCH

Functions such as FILTER, SORT, and SORTBY do not natively allow the selection of specific columns from a table or array. They will only allow the return of a group of adjacent columns. Fortunately, we know INDEX, and INDEX does not understand the words “it can’t be done.”

The INDEX and MATCH/XMATCH combination provides an effective way to dynamically return specific columns from a table or array using cell values.

In Figure 11-77, the following formula returns the top N values using the SORTBY function with SEQUENCE and INDEX. The MATCH function is entered in the column num argument of INDEX. It returns the array of column numbers for the column names entered in range B4:C4.

=INDEX(

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

SEQUENCE(C2),

MATCH(B4:C4,tblProductSales[#Headers],0)

)

Note

The CHOOSECOLS function is covered shortly and is a great alternative method for returning specific columns from an array. CHOOSE has also been discussed in this book. It is fantastic to have so many different options for this task.

In this example, INDEX was already being used to return the top N values, so was the obvious choice in preference of the aforementioned functions.

Returning a Reference from a List

In this final example of the INDEX function, we will be using its second syntax. By using INDEX in this way, we can internally list a collection of references, and then return the reference we want dynamically using an index number.

For this example, we will be using the table of sales data shown in Figure 11-78. This shows only the first few rows of the table named [tblData].

We want to create a report that includes a drop-down list to dynamically choose the column from which to summarize the total sales values. We will have three columns in the drop-down list – [Sales Rep], [Region], and [Category].

In Figure 11-79, the drop-down list with the three column names is in cell C2, with “Region” currently specified.

The first argument within INDEX is the collection of references. The three columns [Category], [Region], and [Sales Rep] are entered here within brackets. The order is important, as they are indexed as references 1, 2, and 3.

The row num and column num arguments are ignored as we want to return all values for the stated column.

The MATCH function is entered in the area num argument. It searches for the value stated in cell C2 within the range A1:A3 and returns the index number of the found value. It is essential that the order of the references in range A1:A3 match the order of the references in the first argument of INDEX.

The INDEX function is used again in this formula to return the stated reference for the criteria range argument of SUMIFS.

This is a pretty cool technique that further demonstrates the sheer number of tasks that INDEX can help us to achieve. However, this type of task is easier to accomplish with the CHOOSE or INDIRECT functions or by using a newer function such as SWITCH or XLOOKUP.

CHOOSECOLS to Return Specific Columns

The CHOOSECOLS function, as its name beautifully describes, enables us to choose specific columns from an array. This function has been eagerly awaited by Excel users as soon as we realized that dynamic array functions such as SORT and FILTER would not allow the selection of non-adjacent columns.

Now, we know that there are already preexisting methods of overcoming this issue such as the INDEX function. But the CHOOSECOLS function is built for this purpose and is therefore simpler and more discoverable by Excel users needing this functionality.

Figure 11-80 shows a table of product sales data named [tblProductSales]. For this example, we will use the SORT function in Excel to sort the data by the fourth column [Total]. We only want to return the [Product Name] and [Total] columns in the resulting array. This is where CHOOSECOLS will assist us.

In Figure 11-81, the following formula is entered in cell B3. The SORT function returns an array with all columns of the table [tblProductSales] sorted by column 4 in descending order. CHOOSECOLS then returns only columns 1 and 4 from that array.

=CHOOSECOLS(

SORT(tblProductSales,4,-1),

1,4)

CHOOSECOLS with MATCH

Typing the column index numbers to be returned is not ideal, especially with tables that have many columns. The MATCH function can be used to return the column numbers based on cell values.

Because of this functionality, other functions can be used to return the array of column numbers to CHOOSECOLS.

In Figure 11-82, the following formula uses the MATCH function to return the column numbers from the header row of [tblProductSales] where there is a match for the values entered in range B2:C2:

=CHOOSECOLS(

SORT(tblProductSales,4,-1),

MATCH(B2:C2,tblProductSales[#Headers],0)

)

This technique does require that the values in range B2:C2 match the header name exactly. However, it also means that you can change the value in cell B2 or C2 to return a different column, for example, “Product Name” to “Category.” This could be a cool feature in a reporting scenario.

CHOOSE for Absolute Column Selection

I know, I know, the CHOOSE function has been covered in this chapter and in other parts of this book already. But it feels appropriate to demonstrate it here along with CHOOSECOLS as an alternative approach.

CHOOSECOLS is great for dynamic reports and models. It can be combined with other formulas or controls to return the column numbers such as MATCH and SEQUENCE. But you may want to just specify the columns to return by their name. And in that scenario, one could argue that CHOOSE is better.

In Figure 11-83, the following formula uses the CHOOSE function to provide the SORTBY function with an array containing the [Product Name] and [Total] columns only. As this example uses absolute table column references, it made sense to use SORTBY instead of SORT to avoid entering column numbers when sorting also.

=SORTBY(CHOOSE({1,2},

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

tblProductSales[Total],-1)

The structure of this formula is different to before in respect that CHOOSE is serving an array to SORTBY, while previously SORT was serving CHOOSECOLS with the array. So, the sequence is reversed.

In simple examples, you can choose the method that you prefer. CHOOSECOLS offers more potential when it comes to dynamic, robust, and interactive spreadsheets.

Finally, with the CHOOSE example, if there were many columns to return, the SEQUENCE function could be added to avoid entering the sequence of column numbers manually.

Reordering Columns with CHOOSECOLS

In addition to returning specific columns from an array, CHOOSECOLS enables us to position the columns in any order that we require.

Following on from the previous examples, let’s return the [Category], [Product Name], and [Total] columns from the SORT function and position them in the order just stated.

In Figure 11-84, you can see in the snapshot of the [tblProductSales] table that the [Category] column is column 2 and the [Product Name] column is column 1. However, in our results we want the [Category] column to precede the [Product Name] column.

In Figure 11-85, the following formula is entered in cell B3. With CHOOSECOLS, you simply enter the column numbers in the order that you want them to appear.

=CHOOSECOLS(

SORT(tblProductSales,4,-1),

2,1,4)

In this example, the MATCH function could have been used to return the column numbers from range B2:D2 instead of the entry of the 2, 1, 4 numbers, if preferred.

Returning Every Nth Column

By combining the CHOOSECOLS function with SEQUENCE, you can return all columns that occur at specific intervals.

Figure 11-86 shows a table named [tblQuartely]. It contains a [Name] column and then quarterly totals for an entire calendar year. We want to return the [Name] column followed by the quarterly totals only. All month columns should be neglected.

In this example, the columns that we require occur in every fourth column. We want to return columns 1, 5, 9, 13, and 17.

In the SEQUENCE function, the number of columns to return is calculated by dividing the total number of table columns by 4 and rounding the result up. In this example, this results in 17 divided by 4 equals 4.25. This is then rounded up to 5. So, 5 columns are returned. The step argument of SEQUENCE is entered as 4.

This approach can be used for any number of columns and any divisor that you require.

CHOOSEROWS to Return Every Nth Row

The CHOOSEROWS function is used to return specific rows from an array. This function is generally not as commonly used as CHOOSECOLS, because there are many other Excel functions to return rows, and values from rows, including FILTER and VLOOKUP.

The FILTER function enables us to return rows from a table that meets specific criteria. So, the specific advantage of CHOOSEROWS is that we can specify the exact rows we need or the rows that follow a specific pattern.

In Figure 11-88, we have a table containing payments that have been received from different sources. The table is named [tblPayments], and it contains subtotal rows.

We will use the CHOOSEROWS function to return only the total rows from the table. The totals occur in every third row, so we will use the SEQUENCE function to return the array of row numbers for CHOOSEROWS.

In Figure 11-89, the following formula returns the total rows only. The number of rows to return is calculated by dividing the total rows by three. SEQUENCE is told to step every third row.

=CHOOSEROWS(tblPayments,

SEQUENCE(ROWS(tblPayments)/3,,,3)

)

Returning the Top and Bottom N Values

Another example where the CHOOSEROWS function can be useful is to return the top and bottom N values from an array.

Stacking Multiple Tables with VSTACK

Combining multiple tables is a task that previously required endless copy and paste operations, a Power Query, or a macro to perform. Now, with VSTACK, it is incredibly simple and will automatically update when data in the tables update.

In Figure 11-97, we have two tables named [tblNorth] and [tblSouth]. We want to combine the data from both tables into a single table. This is a simplified example, and the VSTACK function can handle as many tables as you need.

Adding Calculated Columns to an Array with HSTACK

The HSTACK function is used to stack arrays horizontally. This function can be great for adding calculated columns to an array. With the VSTACK and HSTACK functions, we can create reports with a single formula.

For an example of this, we will create a report that we have covered previously in this book, but this time, with a single formula.

In Figure 11-99, we have a table of sales data named [tblSales]. We will create a report that lists the distinct product names in one column and the sum of the [Total] column for each product in another column. The report will be sorted in descending order by the total.

The HSTACK function combines two arrays together. The first array is the distinct list of product names. And the second is the SUMIFS function to return the total for each product name.

The SORT function then sorts the returned array by the second column in descending order.

The HSTACK function can handle more than two arrays, so more calculated columns can be added if required.

In Chapter 15, the LET function is covered. This function allows us to perform intermediate calculations, leading to faster operation and cleaner formulas. This function is a great help when creating more complex examples of the VSTACK and HSTACK functions.

Dynamic Data Validation List with INDEX or OFFSET

One of the most common requirements for the INDEX and OFFSET functions is to create a dynamic range. And a reason for this can be to feed a Data Validation list. When items are added or removed from the range used as the source for the list, the list automatically updates to include or exclude the items.

My personal preference for this task is to use the INDEX function, but it is useful to be familiar with both.

Figure 11-101 shows four separate ranges of city names by country. For each range, additional cities may be added, or some removed over time. We want to make these ranges dynamic to accommodate such changes automatically.

For this example, we will create a dynamic range of the cities in Germany only. The technique can then be replicated for the other lists.

This formula cannot be entered directly into a Data Validation rule due to the use of the range operator (:). Figure 11-102 shows the type of error you can expect to receive if you try.

A name will need to be defined for the formula, and that name is then used in the Data Validation rule. Even if we were able to enter it directly, I would be inclined to define a name anyway for better management of the workbook.

Figure 11-103 shows the formula entered as the source for a name defined as “lstGermany.”

As the name has workbook scope, Excel will automatically add the sheet name before each range of the formula. We can let Excel handle this and save ourselves a job.

This name can now be used as the source for a Data Validation rule:

1. 1.

   Click Data ➤ Data Validation.

    
2. 2.

   On the Settings tab, select List for the Allow list and enter the following reference in the Source box (Figure 11-104). Click OK.

    

This formula can be entered directly into the Data Validation rule as it does not include a range operator or other illegal characters. This, I suppose, is an advantage over INDEX, though I do like the concept of defining names for ranges such as this still.

You may be thinking “if a Data Validation list is based on a range formatted as a table, would the list auto-expand and contract in size with the table?”. Well, at the time of writing this book, a Data Validation list only effectively updates with a table when they are on the same sheet.

Now, we do not need this formula approach, because if we simply define a name for a range formatted as a table, the name and table work together to create the dynamic range.

However, due to this confusion, until Microsoft has a Data Validation rule and table data working together efficiently, using INDEX or OFFSET for a dynamic range is still very beneficial. It should also be noted that understanding this technique assists us in scenarios outside of Data Validation list too.

Dependent Data Validation List with INDIRECT

Creating dependent drop-down lists with Data Validation is a common question asked in training courses and across Excel forums. Often, a list of items is too large, and it is useful to have a first list that reduces the items shown in a second list.

In modern Excel versions, the searchable list functionality recently introduced has lessened the requirement for creating dependent drop-down lists. But this is only available for those using Excel 365 or Excel Online. This technique is still important for previous Excel versions.

In Figure 11-105, we have four lists of cities by country. Each range has been named using the country name as shown in row 1 prefixed with the text “lst” – for example, lstGermany, lstSpain, etc.

Figure 11-106 shows the setup for a dependent list scenario. Cell B3 contains a list with the four country names and “Germany” currently selected. The dependent drop-down list of cities will be in cell D3.

This formula creates a reference by combining the text string “lst” with the value in cell B3. This allows us to indirectly reference the named range of cities for the country stated in cell B3.

Figure 11-108 shows the completed dependent drop-down list showing cities from the USA as that is the country specified in the first list.

In this example, all country names are single words – Germany, Spain, UK, and USA. If there was a country with more than one word in its name such as South Korea or New Zealand, then the formula would be a little different.

Why? Well, you cannot use spaces in the name of a range, yet the name of the range and the name of the country selected in cell B3 must match exactly.

A common solution for this is to use an underscore in the named range of that country’s cities, that is, lstnew_zealand. The name of the country in the first drop-down would be “New Zealand,” as one would expect.

Format Last X Number of Rows

In the INDEX function part of this chapter, we saw INDEX used to calculate the moving average for a specified number of days. Now, this could be any calculation; the key is that the range being calculated is moving as new rows are added to a table.

For a nice visual touch, it has been decided to format the rows being used in the calculation.

We will use the OFFSET in a Conditional Formatting rule for this task. INDEX would be an obvious choice for this task, but mixing up the techniques used is good for learning.

In Figure 11-109, the last x number of days is formatted. The number of days is specified by the value in cell D2. The following formula is entered in cell E2 to return the moving average for the number of days stated in cell D2:

=AVERAGE(OFFSET($B$2,ROWS(tblDaily)-D2,,D2))

Interactive Chart with INDIRECT

The INDIRECT function offers a simple method to return data from a table dependent upon user selection. In this example, we have four tables containing sales data (Figure 11-111). Each table shares its name with the name of the city written above the table prefixed by the characters “tbl”.

Each table has the same number of rows and columns, so we will not concern ourselves with a dynamic approach (like the following two examples). We simply want to return the data for the specified table to a range on the sheet, to then create a chart from.

This formula returns an array of values that are spilled to the adjacent cells. Although we do not require a dynamic approach for this example, this is great because we only have one formula returning the values.

The INDEX function is wrapped around the INDIRECT function to return the value from the row and column numbers of cell A1. So, the formula in cell B5 returns the value from row 1 and column 1 of the [tblLincoln] table.

When this formula is filled to the four rows below cell B5 and then the column to the right, the INDEX function returns the row and column numbers from the relative cell. For example, the formula in cell B6 returns the value from row 2 and column 1 of the table. And the formula in cell C5 returns the value from row 1 and column 2 of the table.

Figure 11-114 shows the finished column chart. A few modifications have been made (never stick with the standard chart Excel inserts), and it has been positioned over the data that the chart is using as its source.

Interactive Chart with CHOOSEROWS

For this example, we will be using the table of monthly data shown in Figure 11-115. The table is named [tblMonthly].

We want to chart all monthly values for the name selected from the drop-down list in cell C2 (Figure 11-116). This cell has been named [rngName].

The row of values that we require is specified by a cell value, so a combination of CHOOSEROWS and MATCH would be a nice choice of functions for this task.

We want a dynamic method, so that when new months (columns) are added to the [tblMonthly] table, the chart automatically picks them up.

The formulas have been entered on the sheet to check the results and that they function correctly before proceeding with the chart.

We do not want to use the results on the sheet directly as the source for the chart because we cannot take advantage of the dynamic arrays there. We want the chart to automatically update when new months are added to the [tblMonthly] table. So, we will define names for the two spill ranges and use the names for the chart data:

1. 1.

   Click Formulas ➤ Define Name.

    
2. 2.

   In the New Name window, type a Name for the chart values. Then click in the Refers to box, and click cell A2 that contains the formula on the sheet [CHOOSEROWS Data]. Add the “#” on the end to reference the dynamic spill range. In Figure 11-119, the reference has been named “chooserowsValues”. Click OK.

    

We will now create the chart and use the defined names for the chart source data. In this example, we will create a line chart.

1. 4.

   Click Insert ➤ Insert Line or Area Chart ➤ Line.

    
2. 5.

   Click Chart Design ➤ Select Data.

    
3. 6.

   In the Select Data Source window, click the Add button in the Legend Entries (Series) area of the window.

    
4. 7.

   In the Edit Series window (Figure 11-120), click in the Series values box, remove any text, click a cell on the sheet to enter a sheet name, remove the cell reference, and type “chooserowsValues”. Click OK.

    

Figure 11-121 shows the inserted line chart with some further modifications applied.

Rolling Chart with INDEX

For the final example, we will create a rolling chart using INDEX to chart the last seven days of values. With INDEX, we can create a dynamic range easily. This makes it perfect for the task of creating a chart that presents the last x number of values only, that is, the last 13 months, the last 6 weeks, etc.

This technique was demonstrated earlier in the chapter to create a moving average formula with INDEX. This time, we will chart the values instead of aggregating them. When new rows are added, the chart automatically adjusts.

Figure 11-122 shows the results of these formulas being returned to the sheet. This is only done to help write them and to test their functionality.

We will use the formulas in defined names to then be used for the chart’s data sources. The formulas will then be removed from the sheet as they are not required there. This technique works in all versions of Excel. Do not worry about the sight of the spill ranges if you are on an older version of Excel.

1. 1.

   Copy the formula that returns the dates in cell D2. This name will form the axis labels of a line chart.

    
2. 2.

   Click Formulas ➤ Define Name.

    
3. 3.

   In the New Name window, type a Name for the date labels. Click the Refers to box and paste in the formula. In Figure 11-123, the formula has been named “dateLabels”. Click OK.

    

We will now create the chart and use the defined names for the chart source data. In this example, we will create a line chart.

1. 5.

   Click Insert ➤ Insert Line or Area Chart ➤ Line.

    
2. 6.

   Click Chart Design ➤ Select Data.

    
3. 7.

   In the Select Data Source window, click the Add button in the Legend Entries (Series) area of the window.

    
4. 8.

   In the Edit Series window (Figure 11-124), click the Series values box, remove any text, click a cell on the sheet to enter a sheet name, remove the cell reference, and type “chartValues”. Click OK.

    

Figure 11-125 shows the line chart showing the last seven days’ dates and values only.