How to Add Dynamic Array Formulas in Excel Programmatically Using C#

The Dynamic Array Formula has been the most significant feature introduced for Excel formulas, which splits Excel into two flavors Excel365 and Traditional Excel(2019 or Earlier) because Dynamic Arrays are supported only in Excel365. 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 very easy to understand what convenience dynamic arrays provide to the 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:

1. FILTER - Filters data based on criteria defined by the user/developer.
2. RANDARRAY - Generates an array of random numbers.
3. SEQUENCE - Generates an array of sequential numbers.
4. SORT - Sorts a cell range by a specified column.
5. SORTBY - Sorts a cell range by another cell range or array
6. UNIQUE - Extracts unique items from a range of cells.

GcExcel v4.2 (now DsExcel) 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 how to implement Dynamic Array formulas in C# using DsExcel API.

Dynamic Array Functions

DsExcel 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#. To enable the support for dynamic array formulas in DsExcel, set the AllowDynamicArray property of Workbook class to true and assign the dynamic array formula to the Formula property of a cell.

The table below summarizes all the supported functions along with their syntax, description, and code examples. The code snippets below are written with the assumption that the AllowDynamicArray property has been set to True:

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"].Formula= "=FILTER(D4:E12,E4:E12>G4,\"\")";
RANDARRAY: The RANDARRAY function allows you to generate a list of random nu mbers from 0 to 1 in an array. RANDARRAY ([rows],[columns])	sheet.Range ["$D$3"]. Formula = "=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"]. Formula = "=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"].Formula = "=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"]. Formula = "=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"]. Formula = "=UNIQUE(B4: B12)";

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

There are two more operators introduced with Dynamic Arrays which are quite 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. 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, hence in case you don't want an array formula to spill over then 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 Formula 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"].Formula = "=@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:

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

Spill Range Reference (#)

The range of cells to which a dynamic array formula spills its multi-value results is known as spill range. The spill range is highlighted by a blue border whenever you click any cell in the spill range, depicting that all the values in the range have been calculated by the formula 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"].Formula = "=UNIQUE(D4:D12)"; 
   //Apply formula using spill range reference
   sheet.Range["$F$4"].Formula = "=COUNTA(E4#)"; 
}

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

Now that we understood how GcExcel provides support for 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.

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. To extract the required data, we must filter the data based on some criteria; 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 that is bound to the chart series. So, in case the number of filtered rows changes which can happen as soon as the user alters a value in the Show column. Because a change in the Show column causes the recalculation of 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 purpose of the chart 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 so that it refreshes 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 DsExcel workbook, which will be used to 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 DsExcel Workbook using the Open method of 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:

Step 2: Enable Dynamic Array Formula support for DsExcel Workbook

Next, we enable the dynamic arrays support in the DsExcel workbook by setting the AllowDynamicArray property of the Workbook class to true, as depicted in the code below:

//Enable Dynamic Arrays Support in Workbook
workbook.AllowDynamicArray = true;

Step3: Extract chart data using Dynamic Array Formulas

We will now move on to extracting 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.

FILTER function will be used to filter the data from the Product column, which would further be sorted in 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 values from the Amount column will be extracted by using the VLOOKUP function and passing the spill range of FILTER function as a parameter, so as 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"].Formula = "=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"].Formula = "=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:

Step 4: 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 the chart:

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 using another aspect of dynamic arrays, as described in the section ahead.

Step 5: Make the Chart Interactive

Let's start by understanding what is 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 visible in the chart only if the newly filtered data falls in cell range G3:H8, which is 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:

This chart should rather plot all the filtered out data. To do this, we must update the series cell range using the spill range reference which would make sure that 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:

Refer to the following demo to see the above scenario in action. You may refer to the demos and documentation for details.

Download Now!