How to Add Dynamic Array Formulas in Excel Spreadsheets using C# and .NET 6

February 16 2022

The Dynamic Array Formula has been the most significant feature introduced for Excel formulas, which splits Excel into two flavors Excel 365 and Traditional Excel (2019 or earlier) because Dynamic Arrays are supported only in Excel 365.

The Dynamic Arrays feature lets users return multiple resultant values from the formula in a single cell and populate these values into the cells adjacent to the formula cell. Since the results are spilled into multiple cells, this is also known as spill range functionality.

In Traditional Excel, users had to explicitly use Ctrl + Shift + Enter to help Excel recognize the formula as an Array formula, or the formula result would return only a single value. Hence, it is straightforward to understand what convenience dynamic arrays provide users. Formulas returning multiple values are now known as dynamic array formulas.

Excel has also introduced the following six new functions, known as dynamic array functions:

FILTER - Filters data based on criteria defined by the user/developer
RANDARRAY - Generates an array of random numbers
SEQUENCE - Generates an array of sequential numbers
SORT - Sorts a cell range by a specified column
SORTBY - Sorts a cell range by another cell range or array
UNIQUE - Extracts unique items from a range of cells
SINGLE - Returns a single value using logic known as implicit intersection

GcExcel v5.0 now supports the Dynamic Array Formulas feature in code, which means even if you do not have MS Excel installed on your system, or a subscription to Excel365, you will still be able to add dynamic array formulas to the cells through code and save the Excel file with all these formulas. This blog discusses implementing Dynamic Array Formulas in C# using GcExcel API.

Dynamic Array Functions

GcExcel provides support for all the above-listed six functions. Here we go over how to use the six Dynamic Array functions described above in C#. GcExcel provides implicit support for dynamic array formulas in GcExcel, using the Formula2 property from the IRange interface to assign the dynamic array formula to a cell.

The table below summarizes all the supported functions along with their syntax, description, and code example:

Function	Code Snippet	Image
FILTER: The FILTER function filters a range or array-based on criteria you specify.FILTER(array,include,[if_empty])	sheet.Range["I4"].Formula2 = "=FILTER(D4:E12,E4:E12>G4,\"\")";
RANDARRAY: The RANDARRAY function allows you to generate a list of random numbers from 0 to 1 in an array.RANDARRAY([rows],[columns])	sheet.Range["$D$3"].Formula2 = "=RANDARRAY(4,5)";
SEQUENCE: The SEQUENCE function allows you to generate a list of sequential numbers in an array.SEQUENCE(rows,[columns],[start],[step])	sheet.Range["$D$4"].Formula2 = "=SEQUENCE(10,,100,-10)";
SORTBY: The SORTBY function allows you to sort a range or array based on the values in a corresponding range or array.SORTBY(array, by_array1, [sort_order1],[[by_array2], [sort_order2]],…)	sheet.Range["$G$5"].Formula2 = "=SORTBY($D$5:$E$12,$E$5:$E$12)";
SORT: The SORT function to sort a range or array in ascending or descending order.SORT(array,[sort_index],[sort_order],[by_col])	sheet.Range["$I$5"].Formula2 = "=SORT(D5:G13,4,1,FALSE)";
UNIQUE: The UNIQUE function allows you to return a unique list from a range or array of items.UNIQUE(array,[by_col],[occurs_once])	sheet.Range["$G$4:$I$4"].Formula2 = "=UNIQUE(B4:B12)";

The above code snippets focus on applying formulas; download the sample or refer to the following demo for complete implementation of the output depicted in the screenshots.

Note: This sample is implemented using VS2022 and .NET6, showcasing the GcExcel support in the latest version of Visual Studio and .NET.

Two more operators introduced with Dynamic Arrays are helpful when working with Dynamic Array Formulas. The sections ahead discuss these operators in detail.

The @ Operator

The @ operator, also known as an implicit intersection operator, implements a formula behavior known as implicit intersection, which reduces a set of values to a single value. It has been introduced to replace the SINGLE function for a cleaner implementation.

This applies to an array formula returning multiple values, in which case a single value is returned based on the cell position, i.e., row and column. This was the default behavior of traditional Excel, and hence no explicit operator was required.

However, in Excel 365, all the formulas are array formulas. If you don’t want an array formula to spill over, you can prefix the formula with @ operator, and it would return only a single value.

The code snippet below depicts the use of this operator in code by setting Formula2 property of cell range:

static void ImplicitIntersection()  
{  
   //Initialize worksheet  
   var sheet = workbook.Worksheets[6];  
   sheet.Name = "IMPLICIT INTERSECTION";  

   //Add sample data  
   sheet.Range["$D$3:$D12"].Value = new object[] { "Products", "Apple", "Grape", "Pear", "Banana", "Apple", "Grape", "Pear", "Banana", "Banana"};  
   sheet.Range["$E$3"].Value = "Unique Products";    

   //Apply dynamic array formula with implicit intersection operator  
   sheet.Range["$E$4"].Formula2 = "=@UNIQUE(D4:D12)";   
}

The screenshot below shows the output from applying the dynamic array formula using implicit intersection operator as implemented in the above code snippet:

output

You will now find this operator more often in formulas when opening an Excel file created in traditional excel in Excel 365. This has been provided for backward compatibility and to keep the old formula behavior intact.

Note: It is not recommended to use this operator and an array formula to create a mixed formula such as "=B2:B5 * @C2:C5", as older versions of Excel do not support such types of functions.

Spill Range Reference (#)

The range of cells to which a dynamic array formula spills its multi-value results is the spill range. The spill range is highlighted by a blue border whenever you click any cell in the spill range, depicting that the formula has calculated all the values in the range in the top-left cell of the range.

The spill range reference operator is used to refer to this spill range. To refer to the spill range, put a hashtag or pound symbol (#) after the address of the upper-left cell in the spill range.

For example, to find how many unique values are extracted by the UNIQUE formula applied in cell E4, supply the spill range reference to the COUNTA function as depicted in the code below:

static void SpillReference()  
{  
   //Initialize Worksheet  
   var sheet = workbook.Worksheets[7];  
   sheet.Name = "SPILL REFERENCE";  

   //Add sample data  
   sheet.Range["$D$3:$D12"].Value = new object[] { "Products", "Apple", "Grape", "Pear", "Banana", "Apple", "Grape", "Pear", "Banana", "Banana" };  
   sheet.Range["$E$3"].Value = "Unique Products";  
   sheet.Range["F3"].Value = "Unique Products Count";    

   //Apply dynamic array formula  
   sheet.Range["$E$4"].Formula2 = "=UNIQUE(D4:D12)";   
   //Apply formula using spill range reference  
   sheet.Range["$F$4"].Formula2 = "=COUNTA(E4#)";   
}

The screenshot below depicts the output observed by applying the spill range reference operator as implemented in the above-defined code snippet:

output

Now that we understand how GcExcel supports Dynamic array functions in C# let's put this understanding to work and implement a real-time scenario showcasing the utilization of dynamic array formulas.

Note: Using this operator and an array formula is not recommended to create a mixed formula such as "=B2:B5 * @C2:C5", as older Excel versions do not support such functions well.

Use Case: Create an Interactive Chart in Excel with Dynamic Arrays

Consider the scenario of creating a column chart using tabular data available in a cell range; tables are generally used to provide a detailed form of data having many rows and columns of data. However, when plotting the data on a chart, there needs to be a way to choose selective data from the table.

We must filter the data based on some criteria to extract the required data. This is where the dynamic array formulas come into the picture. We use the FILTER function to filter the tabular data by defining criteria, and once the filtered data is retrieved, we will use it to create a Column Chart.

The Column Chart plots the data from a pre-defined cell range bound to the chart series. So, the number of filtered rows changes as soon as the user alters a value in the Show column. Because a change in the Show column causes the recalculation of the FILTER formula used to filter the data and hence the filtered data cell range changes, but the chart series refers to the same cell range always, so any new row of filtered data that does not fall in the series cell range will not be plotted on the chart.

However, we expect all the filtered data to be plotted on the chart because the chart’s purpose is to show all products having value 1 in the Show column. We would also work on this aspect and showcase how we can transform a simple static chart into an interactive chart to refresh its plot area to plot all the values in filtered data.

So, follow along to understand the detailed steps to implement the above-defined scenario.

Step 1: Load Source Data

We will begin by loading source data in the GcExcel workbook, which will plot the chart. The source data has been saved in an Excel file named 'SalesReport_sourcedata,xlsx.’ You can download and view the excel source data file from here.

The code snippet below depicts how we can load the Excel file into GcExcel Workbook using the Open method of the Workbook class:

//create a new workbook  
var workbook = new GrapeCity.Documents.Excel.Workbook();  

var fileStream = GetResourceStream("xlsx\\SalesReport_sourcedata.xlsx");  
workbook.Open(fileStream);

Here is a quick look at the workbook after loading the source data:

data

Step 2: Extract Chart Data Using Dynamic Array Formulas

We will now extract relevant data to be plotted in the chart. We will be plotting sales based on products in the chart. Therefore, chart data should contain values from the Product column and the Amount column.

The criteria for extracting specific values from both columns are based on the Show column. We will extract product and amount values from each row having value 1 for the Show column and plot this filtered data on the chart.

The FILTER function will filter the data from the Product column, which would be sorted into descending order of Amount column values using the SORTBY function. Hence, we will be creating a dynamic array formula by chaining two dynamic array functions, i.e., FILTER and SORTBY.

The Amount column values will be extracted using the VLOOKUP function and passing the spill range of the FILTER function as a parameter to fetch the amount value equivalent to each filtered product.

The code snippet below depicts the same:

//Filter data based on Show Column  
IWorksheet worksheet = workbook.Worksheets[0];  
worksheet.Range["G3"].Value = "Product";  
worksheet.Range["H3"].Value = "Sales";  
worksheet.Range["G3"].Font.Bold = true;  
worksheet.Range["H3"].Font.Bold = true;  
//Apply chained dynamic array formula to fetch Product column values  
worksheet.Range["G4"].Formula2 = "=SORTBY(FILTER(B4:B13,E4:E13=1),FILTER(D4:D13,E4:E13=1),-1)";  
//Use Spill Range reference to fetch Amount column values  
worksheet.Range["H4"].Formula2 = "=VLOOKUP(G4#,B4:D13,3,FALSE)";

Here is a quick look at the worksheet containing the filtered and sorted data in Column G and H, with the highlighted spill range:

worksheet

Step 3: Add a Simple Chart Using Filtered Data

The above step has generated the data to be plotted in the chart. Here we add a simple chart to showcase the sales by adding two named ranges to the worksheet, one of which refers to the Product column and the other refers to the Sales column in filtered out data. These named ranges are then used to create the chart series. The code snippet below shows how to accomplish this:

//Add named ranges to refer to chart data  
workbook.Names.Add("Product", "=Sheet1!$G$4:$G$8");  
workbook.Names.Add("Sales", "=Sheet1!$H$4:$H$8");  
//Add Chart  
IShape chartShape = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 600, 170, 500, 300);  
IChart chart = chartShape.Chart;             
//Adding series to SeriesCollection  
ISeries series = chart.SeriesCollection.NewSeries();  
series.Formula = "=SERIES(\"Sales\", Product, Sales, 1)";

Here is a quick look at the generated excel file with chart:

excel

The steps above complete the creation of a chart using the filtered data created with the help of dynamic array formulas. Now, let's make this chart interactive utilizing another aspect of dynamic arrays described in the section.

Step 4: Make the Chart Interactive

Let's start by understanding the need to make this chart interactive. Observe the GIF below, and you will find that changing a value in the Show column recalculates the dynamic array formula and updates the filtered data cell range. However, this change is only visible in the chart if the newly filtered data falls in cell range G3:H8, the series cell range.

In case the filtered data does not fall in the cell range G3:H8, then it is not shown in the chart, as in this example filtered data range expands to G3:H10, but the chart renders data only from G3:H8:

chart

This chart should instead plot all the filtered-out data. To do this, we must update the series cell range using the spill range reference, ensuring that the series cell range always includes the complete cell range containing the filtered data.

Here is the sample code snippet, which updates the named ranges to use spill range reference making the referenced cell range dynamic depending on the result of the dynamic array formula. These dynamic named ranges are then used to create the chart series, making the chart interactive.

 //Add named ranges to refer to chart data  
workbook.Names.Add("Product", "=Sheet1!$G$4#");  
workbook.Names.Add("Sales", "=Sheet1!$H$4#");  
//Add Chart  
IShape chartShape = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 600, 170, 500, 300);  
IChart chart = chartShape.Chart;  
//Adding series to SeriesCollection  
ISeries series = chart.SeriesCollection.NewSeries();  
series.Formula = "=SERIES(\"Sales\", Product, Sales, 1)";

Refer to the following GIF, which depicts the working of an interactive chart, which renders all the filtered data:

interactive