Spread WPF 18
Features / Formulas and Functions / Dynamic Array Formulas
In This Topic
    Dynamic Array Formulas
    In This Topic

    Spread for WPF provides extensive support for using Dynamic array formulas in the worksheets. When a cell contains a dynamic array formula, it can return multiple values because the elements of the array spill into the adjacent empty cells. Unlike generic arrays, dynamic arrays automatically resize when the data is inserted or removed from the source range. 

    Earlier, working with formulas in worksheets was a cumbersome and time-consuming task because users had to copy the formulas to every cell manually where they wanted the result to be calculated. With dynamic array formulas, multiple results can be returned as output via automatic spilling and spanning to the cell range. Dynamic arrays are useful especially when you're looking for a good locality of reference and effective data cache utilization in the worksheets. 

    Dynamic array formulas that return more than one result and spill successfully to the nearby cells are known as Spilled array formulas. Note that spilled array formulas are not supported in tables. However, while working with dynamic array formulas that spill to several rows and columns, the cell ranges used in the worksheets can be formatted explicitly to appear like tables.

    A range containing the results of dynamic array formulas spanned over multiple cells is called a spilled range. Using the HasSpill property of IRange Interface, you can check whether a cell or a range has a spilled cell within it. It returns True if all the cells in the range are part of a spilled range, False if none of the cells in the range are part of a spilled range, and null, otherwise. When you select any cell in the range of the generated Spilled array, the entire range is highlighted with a blue color border by default.

    Commonly used functions in the dynamic array formulas are listed below.

    Function Description
    SORT The SORT function sorts the data in a cell range or an array. The results of this function spill into the resultant range with a dynamic array of values arranged in the ascending (increasing) or descending (decreasing) order. If the sort order is not specified, then by default, the values are sorted alphabetically in the ascending order.
    SORTBY The SORTBY function sorts the contents of a cell range or an array on the basis of the values present in a corresponding range or array.
    SEQUENCE The SEQUENCE function returns a list of sequential numbers in an array in the ascending order.
    SINGLE The SINGLE function returns a single value, a single cell range or an error using the implicit intersection logic.
    FILTER The FILTER function allows users to filter a cell range on the basis of the defined criteria. The Filter operation can be performed based on a single criterion or multiple criteria. In order to combine two or more filter conditions, users can use the " * " operator.
    RANDARRAY The RANDARRAY function returns an array of random numeric values. Users can specify the number of rows and columns, minimum and maximum values and indicate whether to return integers or decimal values.
    UNIQUE The UNIQUE function returns a list of all the unique values in a cell range.

    The following example code demonstrates how the dynamic array functions are used in the worksheet.

    Copy Code 
    // Dynamic array formulas. 
GrapeCity.Spreadsheet.IWorksheet worksheet1 = spreadSheet1.Workbook.Worksheets.Add(); 
spreadSheet1.Workbook.WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.DynamicArray; 
spreadSheet1.Workbook.Worksheets[0].Cells[0, 0].Text = "Dynamic Array Functions"; 
spreadSheet1.Workbook.Worksheets[0].Cells[0, 0].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[0].Cells[2, 0].Text = "Customer's Name"; 
spreadSheet1.Workbook.Worksheets[0].Cells[2, 0].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[0].Cells[3, 0].Text = "Larry"; 
spreadSheet1.Workbook.Worksheets[0].Cells[4, 0].Text = "Safeway"; 
spreadSheet1.Workbook.Worksheets[0].Cells[5, 0].Text = "Safeway"; 
spreadSheet1.Workbook.Worksheets[0].Cells[6, 0].Text = "Raley"; 
spreadSheet1.Workbook.Worksheets[0].Cells[7, 0].Text = "Vallarta"; 
spreadSheet1.Workbook.Worksheets[0].Cells[8, 0].Text = "Safeway"; 
spreadSheet1.Workbook.Worksheets[0].Cells[9, 0].Text = "Raley"; 
spreadSheet1.Workbook.Worksheets[0].Cells[10, 0].Text = "Larry"; 
spreadSheet1.Workbook.Worksheets[0].Cells[11, 0].Text = "Gilbert"; 
spreadSheet1.Workbook.Worksheets[0].Cells[12, 0].Text = "Larry"; 
spreadSheet1.Workbook.Worksheets[0].Cells[13, 0].Text = "Larry"; 
spreadSheet1.Workbook.Worksheets[0].Cells[14, 0].Text = "Raley"; 
spreadSheet1.Workbook.Worksheets[0].Columns[0].ColumnWidth = 120; 
spreadSheet1.Workbook.Worksheets[0].Cells[2, 1].Text = "Age"; 
spreadSheet1.Workbook.Worksheets[0].Cells[2, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[0].Cells[3, 1].Text = "32"; 
spreadSheet1.Workbook.Worksheets[0].Cells[4, 1].Text = "23"; 
spreadSheet1.Workbook.Worksheets[0].Cells[5, 1].Text = "23"; 
spreadSheet1.Workbook.Worksheets[0].Cells[6, 1].Text = "39"; 
spreadSheet1.Workbook.Worksheets[0].Cells[7, 1].Text = "18"; 
spreadSheet1.Workbook.Worksheets[0].Cells[8, 1].Text = "23"; 
spreadSheet1.Workbook.Worksheets[0].Cells[9, 1].Text = "39"; 
spreadSheet1.Workbook.Worksheets[0].Cells[10, 1].Text = "32"; 
spreadSheet1.Workbook.Worksheets[0].Cells[11, 1].Text = "19"; 
spreadSheet1.Workbook.Worksheets[0].Cells[12, 1].Text = "32"; 
spreadSheet1.Workbook.Worksheets[0].Cells[13, 1].Text = "32"; 
spreadSheet1.Workbook.Worksheets[0].Cells[14, 1].Text = "39"; 
spreadSheet1.Workbook.Worksheets[0].Columns[1].ColumnWidth = 50; 
// Setting "Unique" Formula 
spreadSheet1.Workbook.Worksheets[0].Cells[2, 2].Text = "Unique List"; 
spreadSheet1.Workbook.Worksheets[0].Cells[2, 2].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightBlue); 
spreadSheet1.Workbook.Worksheets[0].Cells[3, 2].Formula2 = "UNIQUE(A4:A15)"; 
spreadSheet1.Workbook.Worksheets[0].Columns[2].ColumnWidth = 90; 
// Setting "Sort" Formula 
spreadSheet1.Workbook.Worksheets[0].Cells[2, 3].Text = "Sort"; 
spreadSheet1.Workbook.Worksheets[0].Cells[2, 3].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightBlue); 
spreadSheet1.Workbook.Worksheets[0].Cells[3, 3].Formula2 = "SORT(A4:A15)"; 
spreadSheet1.Workbook.Worksheets[0].Columns[3].ColumnWidth = 90; 
// Setting "Sort" Formula for Unique list 
spreadSheet1.Workbook.Worksheets[0].Cells[2, 4].Text = "Sort Unique"; 
spreadSheet1.Workbook.Worksheets[0].Cells[2, 4].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightBlue); 
spreadSheet1.Workbook.Worksheets[0].Cells[3, 4].Formula2 = "SORT(C4#)"; 
spreadSheet1.Workbook.Worksheets[0].Columns[4].ColumnWidth = 90; 
// Setting "Sort+Unique" Formula together 
spreadSheet1.Workbook.Worksheets[0].Cells[2, 5].Text = "Sort Unique"; 
spreadSheet1.Workbook.Worksheets[0].Cells[2, 5].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightBlue); 
spreadSheet1.Workbook.Worksheets[0].Cells[3, 5].Formula2 = "SORT(UNIQUE(A4:A15))"; 
spreadSheet1.Workbook.Worksheets[0].Columns[5].ColumnWidth = 90; 
// Setting "SortBy" Formula wherein we sort Range A4:B15 based on the values in a corresponding range B4:B15 
spreadSheet1.Workbook.Worksheets[0].Cells[2, 6].Text = "SortBy"; 
spreadSheet1.Workbook.Worksheets[0].Cells[2, 6].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightBlue); 
spreadSheet1.Workbook.Worksheets[0].Cells[3, 6].Formula2 = "SORTBY(A4:B15, B4:B15)"; 
spreadSheet1.Workbook.Worksheets[0].Columns[6].ColumnWidth = 90; 
// Setting Data in Cells of Sheet[1] 
spreadSheet1.Workbook.Worksheets[1].Columns[0, 9].ColumnWidth = 70; 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 0].Text = "Region"; 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 0].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[1].Cells[4, 0].Text = "East"; 
spreadSheet1.Workbook.Worksheets[1].Cells[5, 0].Text = "North"; 
spreadSheet1.Workbook.Worksheets[1].Cells[6, 0].Text = "Wast"; 
spreadSheet1.Workbook.Worksheets[1].Cells[7, 0].Text = "Sast"; 
spreadSheet1.Workbook.Worksheets[1].Cells[8, 0].Text = "East"; 
spreadSheet1.Workbook.Worksheets[1].Cells[9, 0].Text = "East"; 
spreadSheet1.Workbook.Worksheets[1].Cells[10, 0].Text = "West"; 
spreadSheet1.Workbook.Worksheets[1].Cells[11, 0].Text = "South"; 
spreadSheet1.Workbook.Worksheets[1].Cells[12, 0].Text = "North"; 
spreadSheet1.Workbook.Worksheets[1].Cells[13, 0].Text = "North"; 
spreadSheet1.Workbook.Worksheets[1].Cells[14, 0].Text = "East"; 
spreadSheet1.Workbook.Worksheets[1].Cells[15, 0].Text = "South"; 
spreadSheet1.Workbook.Worksheets[1].Cells[16, 0].Text = "West"; 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 1].Text = "Sales Rep"; 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[1].Cells[4, 1].Text = "Tom"; 
spreadSheet1.Workbook.Worksheets[1].Cells[5, 1].Text = "Fred"; 
spreadSheet1.Workbook.Worksheets[1].Cells[6, 1].Text = "Amy"; 
spreadSheet1.Workbook.Worksheets[1].Cells[7, 1].Text = "Sal"; 
spreadSheet1.Workbook.Worksheets[1].Cells[8, 1].Text = "Hector"; 
spreadSheet1.Workbook.Worksheets[1].Cells[9, 1].Text = "Xi"; 
spreadSheet1.Workbook.Worksheets[1].Cells[10, 1].Text = "Amy"; 
spreadSheet1.Workbook.Worksheets[1].Cells[11, 1].Text = "Sal"; 
spreadSheet1.Workbook.Worksheets[1].Cells[12, 1].Text = "Fred"; 
spreadSheet1.Workbook.Worksheets[1].Cells[13, 1].Text = "Tom"; 
spreadSheet1.Workbook.Worksheets[1].Cells[14, 1].Text = "Hector"; 
spreadSheet1.Workbook.Worksheets[1].Cells[15, 1].Text = "Sravan"; 
spreadSheet1.Workbook.Worksheets[1].Cells[16, 1].Text = "Xi"; 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 2].Text = "Product"; 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 2].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[1].Cells[4, 2].Text = "Apple"; 
spreadSheet1.Workbook.Worksheets[1].Cells[5, 2].Text = "Grape"; 
spreadSheet1.Workbook.Worksheets[1].Cells[6, 2].Text = "Pear"; 
spreadSheet1.Workbook.Worksheets[1].Cells[7, 2].Text = "Banana"; 
spreadSheet1.Workbook.Worksheets[1].Cells[8, 2].Text = "Apple"; 
spreadSheet1.Workbook.Worksheets[1].Cells[9, 2].Text = "Banana"; 
spreadSheet1.Workbook.Worksheets[1].Cells[10, 2].Text = "Banana"; 
spreadSheet1.Workbook.Worksheets[1].Cells[11, 2].Text = "Pear"; 
spreadSheet1.Workbook.Worksheets[1].Cells[12, 2].Text = "Apple"; 
spreadSheet1.Workbook.Worksheets[1].Cells[13, 2].Text = "Grape"; 
spreadSheet1.Workbook.Worksheets[1].Cells[14, 2].Text = "Grape"; 
spreadSheet1.Workbook.Worksheets[1].Cells[15, 2].Text = "Apple"; 
spreadSheet1.Workbook.Worksheets[1].Cells[16, 2].Text = "Grape"; 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 3].Text = "Units"; 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 3].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[1].Cells[4, 3].Text = "6380"; 
spreadSheet1.Workbook.Worksheets[1].Cells[5, 3].Text = "2344"; 
spreadSheet1.Workbook.Worksheets[1].Cells[6, 3].Text = "3434"; 
spreadSheet1.Workbook.Worksheets[1].Cells[7, 3].Text = "5461"; 
spreadSheet1.Workbook.Worksheets[1].Cells[8, 3].Text = "2341"; 
spreadSheet1.Workbook.Worksheets[1].Cells[9, 3].Text = "3234"; 
spreadSheet1.Workbook.Worksheets[1].Cells[10, 3].Text = "6532"; 
spreadSheet1.Workbook.Worksheets[1].Cells[11, 3].Text = "7323"; 
spreadSheet1.Workbook.Worksheets[1].Cells[12, 3].Text = "2334"; 
spreadSheet1.Workbook.Worksheets[1].Cells[13, 3].Text = "8734"; 
spreadSheet1.Workbook.Worksheets[1].Cells[14, 3].Text = "1932"; 
spreadSheet1.Workbook.Worksheets[1].Cells[15, 3].Text = "7682"; 
spreadSheet1.Workbook.Worksheets[1].Cells[16, 3].Text = "3293"; 
spreadSheet1.Workbook.Worksheets[1].Cells[0, 4].Text = "Product:"; 
spreadSheet1.Workbook.Worksheets[1].Cells[0, 4].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[1].Cells[0, 5].Text = "Apple"; 
spreadSheet1.Workbook.Worksheets[1].Cells[1, 4].Text = "Region:"; 
spreadSheet1.Workbook.Worksheets[1].Cells[1, 4].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[1].Cells[1, 5].Text = "East"; 
spreadSheet1.Workbook.Worksheets[1].Cells[2, 5].Text = "Filtering performed on one Criteria"; 
spreadSheet1.Workbook.Worksheets[1].Cells[2, 5].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightBlue); 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 5].Text = "Region"; 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 5].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 6].Text = "Sales Rep"; 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 6].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 7].Text = "Product"; 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 7].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 8].Text = "Units"; 
spreadSheet1.Workbook.Worksheets[1].Cells[3, 8].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
/* Setting "Filter" Formula( with one condition) wherein we filter range A5:D17 based upon criteria wherein range C5:C17 is equal to value in cell F1 */ 
spreadSheet1.Workbook.Worksheets[1].Cells[4, 5].Formula2 = "FILTER(A5:D17, C5:C17=F1)"; 
spreadSheet1.Workbook.Worksheets[1].Cells[12, 5].Text = "Region"; 
spreadSheet1.Workbook.Worksheets[1].Cells[12, 5].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[1].Cells[12, 6].Text = "Sales Rep"; 
spreadSheet1.Workbook.Worksheets[1].Cells[12, 6].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[1].Cells[12, 7].Text = "Product"; 
spreadSheet1.Workbook.Worksheets[1].Cells[12, 7].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[1].Cells[12, 8].Text = "Units"; 
spreadSheet1.Workbook.Worksheets[1].Cells[12, 8].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray); 
spreadSheet1.Workbook.Worksheets[1].Cells[11, 5].Text = "Filtering performed on two Criteria"; 
spreadSheet1.Workbook.Worksheets[1].Cells[11, 5].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightBlue); 

/* Setting "Filter" Formula( with two conditions) wherein we filter range A5:D17 based upon criteria wherein range C5:C17 is equal to value in cell F1 and range A5:A17 is equal to value in cell F2 */ 
spreadSheet1.Workbook.Worksheets[1].Cells[13, 5].Formula2 = "FILTER(A5:D17, (C5:C17=F1)*(A5:A17=F2))"; 
GrapeCity.Spreadsheet.IWorksheet worksheet2 = spreadSheet1.Workbook.Worksheets.Add(); 
// Setting "Sequence" Formula 
spreadSheet1.Workbook.Worksheets[2].Columns[0, 7].ColumnWidth = 130; 
spreadSheet1.Workbook.Worksheets[2].Cells[0, 0].Text = "SEQUENCE(4,5) Function"; 
spreadSheet1.Workbook.Worksheets[2].Cells[0, 0].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.SkyBlue); 
spreadSheet1.Workbook.Worksheets[2].Cells[1, 0].Formula2 = "SEQUENCE(4,5)"; 
// Setting "RandArray" Formula 
spreadSheet1.Workbook.Worksheets[2].Cells[6, 0].Text = "RANDARRAY(5,3) Function"; 
spreadSheet1.Workbook.Worksheets[2].Cells[6, 0].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.SkyBlue); 
spreadSheet1.Workbook.Worksheets[2].Cells[7, 0].Formula2 = "RANDARRAY(5,3)"; 
 
// Setting "Single" Formula 
spreadSheet1.Workbook.Worksheets[2].Cells[13, 0].Text = "SINGLE(A15:E15) Function"; 
spreadSheet1.Workbook.Worksheets[2].Cells[13, 0].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.SkyBlue); 
spreadSheet1.Workbook.Worksheets[2].Cells[14, 0].Value = "A"; 
spreadSheet1.Workbook.Worksheets[2].Cells[14, 1].Value = "B"; 
spreadSheet1.Workbook.Worksheets[2].Cells[14, 2].Value = "C"; 
spreadSheet1.Workbook.Worksheets[2].Cells[14, 3].Value = "D"; 
spreadSheet1.Workbook.Worksheets[2].Cells[14, 4].Value = "E"; 
spreadSheet1.Workbook.Worksheets[2].Cells[15, 2].Formula = "SINGLE(A15:E15)";
    Copy Code 
        ' Dynamic array formulas. 
    Dim worksheet1 As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.Worksheets.Add()
    spreadSheet1.Workbook.WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.DynamicArray
    spreadSheet1.Workbook.Worksheets(0).Cells(0, 0).Text = "Dynamic Array Functions"
    spreadSheet1.Workbook.Worksheets(0).Cells(0, 0).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(0).Cells(2, 0).Text = "Customer's Name"
    spreadSheet1.Workbook.Worksheets(0).Cells(2, 0).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(0).Cells(3, 0).Text = "Larry"
    spreadSheet1.Workbook.Worksheets(0).Cells(4, 0).Text = "Safeway"
    spreadSheet1.Workbook.Worksheets(0).Cells(5, 0).Text = "Safeway"
    spreadSheet1.Workbook.Worksheets(0).Cells(6, 0).Text = "Raley"
    spreadSheet1.Workbook.Worksheets(0).Cells(7, 0).Text = "Vallarta"
    spreadSheet1.Workbook.Worksheets(0).Cells(8, 0).Text = "Safeway"
    spreadSheet1.Workbook.Worksheets(0).Cells(9, 0).Text = "Raley"
    spreadSheet1.Workbook.Worksheets(0).Cells(10, 0).Text = "Larry"
    spreadSheet1.Workbook.Worksheets(0).Cells(11, 0).Text = "Gilbert"
    spreadSheet1.Workbook.Worksheets(0).Cells(12, 0).Text = "Larry"
    spreadSheet1.Workbook.Worksheets(0).Cells(13, 0).Text = "Larry"
    spreadSheet1.Workbook.Worksheets(0).Cells(14, 0).Text = "Raley"
    spreadSheet1.Workbook.Worksheets(0).Columns(0).ColumnWidth = 120
    spreadSheet1.Workbook.Worksheets(0).Cells(2, 1).Text = "Age"
    spreadSheet1.Workbook.Worksheets(0).Cells(2, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(0).Cells(3, 1).Text = "32"
    spreadSheet1.Workbook.Worksheets(0).Cells(4, 1).Text = "23"
    spreadSheet1.Workbook.Worksheets(0).Cells(5, 1).Text = "23"
    spreadSheet1.Workbook.Worksheets(0).Cells(6, 1).Text = "39"
    spreadSheet1.Workbook.Worksheets(0).Cells(7, 1).Text = "18"
    spreadSheet1.Workbook.Worksheets(0).Cells(8, 1).Text = "23"
    spreadSheet1.Workbook.Worksheets(0).Cells(9, 1).Text = "39"
    spreadSheet1.Workbook.Worksheets(0).Cells(10, 1).Text = "32"
    spreadSheet1.Workbook.Worksheets(0).Cells(11, 1).Text = "19"
    spreadSheet1.Workbook.Worksheets(0).Cells(12, 1).Text = "32"
    spreadSheet1.Workbook.Worksheets(0).Cells(13, 1).Text = "32"
    spreadSheet1.Workbook.Worksheets(0).Cells(14, 1).Text = "39"
    spreadSheet1.Workbook.Worksheets(0).Columns(1).ColumnWidth = 50
    ' Setting "Unique" Formula 
    spreadSheet1.Workbook.Worksheets(0).Cells(2, 2).Text = "Unique List"
    spreadSheet1.Workbook.Worksheets(0).Cells(2, 2).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightBlue)
    spreadSheet1.Workbook.Worksheets(0).Cells(3, 2).Formula2 = "UNIQUE(A4:A15)"
    spreadSheet1.Workbook.Worksheets(0).Columns(2).ColumnWidth = 90
    ' Setting "Sort" Formula 
    spreadSheet1.Workbook.Worksheets(0).Cells(2, 3).Text = "Sort"
    spreadSheet1.Workbook.Worksheets(0).Cells(2, 3).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightBlue)
    spreadSheet1.Workbook.Worksheets(0).Cells(3, 3).Formula2 = "SORT(A4:A15)"
    spreadSheet1.Workbook.Worksheets(0).Columns(3).ColumnWidth = 90
    ' Setting "Sort" Formula for Unique list 
    spreadSheet1.Workbook.Worksheets(0).Cells(2, 4).Text = "Sort Unique"
    spreadSheet1.Workbook.Worksheets(0).Cells(2, 4).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightBlue)
    spreadSheet1.Workbook.Worksheets(0).Cells(3, 4).Formula2 = "SORT(C4#)"
    spreadSheet1.Workbook.Worksheets(0).Columns(4).ColumnWidth = 90
    ' Setting "Sort+Unique" Formula together 
    spreadSheet1.Workbook.Worksheets(0).Cells(2, 5).Text = "Sort Unique"
    spreadSheet1.Workbook.Worksheets(0).Cells(2, 5).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightBlue)
    spreadSheet1.Workbook.Worksheets(0).Cells(3, 5).Formula2 = "SORT(UNIQUE(A4:A15))"
    spreadSheet1.Workbook.Worksheets(0).Columns(5).ColumnWidth = 90
    ' Setting "SortBy" Formula wherein we sort Range A4:B15 based on the values in a corresponding range B4:B15 
    spreadSheet1.Workbook.Worksheets(0).Cells(2, 6).Text = "SortBy"
    spreadSheet1.Workbook.Worksheets(0).Cells(2, 6).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightBlue)
    spreadSheet1.Workbook.Worksheets(0).Cells(3, 6).Formula2 = "SORTBY(A4:B15, B4:B15)"
    spreadSheet1.Workbook.Worksheets(0).Columns(6).ColumnWidth = 90
    ' Setting Data in Cells of Sheet[1] 
    spreadSheet1.Workbook.Worksheets(1).Columns(0, 9).ColumnWidth = 70
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 0).Text = "Region"
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 0).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(1).Cells(4, 0).Text = "East"
    spreadSheet1.Workbook.Worksheets(1).Cells(5, 0).Text = "North"
    spreadSheet1.Workbook.Worksheets(1).Cells(6, 0).Text = "Wast"
    spreadSheet1.Workbook.Worksheets(1).Cells(7, 0).Text = "Sast"
    spreadSheet1.Workbook.Worksheets(1).Cells(8, 0).Text = "East"
    spreadSheet1.Workbook.Worksheets(1).Cells(9, 0).Text = "East"
    spreadSheet1.Workbook.Worksheets(1).Cells(10, 0).Text = "West"
    spreadSheet1.Workbook.Worksheets(1).Cells(11, 0).Text = "South"
    spreadSheet1.Workbook.Worksheets(1).Cells(12, 0).Text = "North"
    spreadSheet1.Workbook.Worksheets(1).Cells(13, 0).Text = "North"
    spreadSheet1.Workbook.Worksheets(1).Cells(14, 0).Text = "East"
    spreadSheet1.Workbook.Worksheets(1).Cells(15, 0).Text = "South"
    spreadSheet1.Workbook.Worksheets(1).Cells(16, 0).Text = "West"
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 1).Text = "Sales Rep"
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(1).Cells(4, 1).Text = "Tom"
    spreadSheet1.Workbook.Worksheets(1).Cells(5, 1).Text = "Fred"
    spreadSheet1.Workbook.Worksheets(1).Cells(6, 1).Text = "Amy"
    spreadSheet1.Workbook.Worksheets(1).Cells(7, 1).Text = "Sal"
    spreadSheet1.Workbook.Worksheets(1).Cells(8, 1).Text = "Hector"
    spreadSheet1.Workbook.Worksheets(1).Cells(9, 1).Text = "Xi"
    spreadSheet1.Workbook.Worksheets(1).Cells(10, 1).Text = "Amy"
    spreadSheet1.Workbook.Worksheets(1).Cells(11, 1).Text = "Sal"
    spreadSheet1.Workbook.Worksheets(1).Cells(12, 1).Text = "Fred"
    spreadSheet1.Workbook.Worksheets(1).Cells(13, 1).Text = "Tom"
    spreadSheet1.Workbook.Worksheets(1).Cells(14, 1).Text = "Hector"
    spreadSheet1.Workbook.Worksheets(1).Cells(15, 1).Text = "Sravan"
    spreadSheet1.Workbook.Worksheets(1).Cells(16, 1).Text = "Xi"
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 2).Text = "Product"
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 2).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(1).Cells(4, 2).Text = "Apple"
    spreadSheet1.Workbook.Worksheets(1).Cells(5, 2).Text = "Grape"
    spreadSheet1.Workbook.Worksheets(1).Cells(6, 2).Text = "Pear"
    spreadSheet1.Workbook.Worksheets(1).Cells(7, 2).Text = "Banana"
    spreadSheet1.Workbook.Worksheets(1).Cells(8, 2).Text = "Apple"
    spreadSheet1.Workbook.Worksheets(1).Cells(9, 2).Text = "Banana"
    spreadSheet1.Workbook.Worksheets(1).Cells(10, 2).Text = "Banana"
    spreadSheet1.Workbook.Worksheets(1).Cells(11, 2).Text = "Pear"
    spreadSheet1.Workbook.Worksheets(1).Cells(12, 2).Text = "Apple"
    spreadSheet1.Workbook.Worksheets(1).Cells(13, 2).Text = "Grape"
    spreadSheet1.Workbook.Worksheets(1).Cells(14, 2).Text = "Grape"
    spreadSheet1.Workbook.Worksheets(1).Cells(15, 2).Text = "Apple"
    spreadSheet1.Workbook.Worksheets(1).Cells(16, 2).Text = "Grape"
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 3).Text = "Units"
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 3).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(1).Cells(4, 3).Text = "6380"
    spreadSheet1.Workbook.Worksheets(1).Cells(5, 3).Text = "2344"
    spreadSheet1.Workbook.Worksheets(1).Cells(6, 3).Text = "3434"
    spreadSheet1.Workbook.Worksheets(1).Cells(7, 3).Text = "5461"
    spreadSheet1.Workbook.Worksheets(1).Cells(8, 3).Text = "2341"
    spreadSheet1.Workbook.Worksheets(1).Cells(9, 3).Text = "3234"
    spreadSheet1.Workbook.Worksheets(1).Cells(10, 3).Text = "6532"
    spreadSheet1.Workbook.Worksheets(1).Cells(11, 3).Text = "7323"
    spreadSheet1.Workbook.Worksheets(1).Cells(12, 3).Text = "2334"
    spreadSheet1.Workbook.Worksheets(1).Cells(13, 3).Text = "8734"
    spreadSheet1.Workbook.Worksheets(1).Cells(14, 3).Text = "1932"
    spreadSheet1.Workbook.Worksheets(1).Cells(15, 3).Text = "7682"
    spreadSheet1.Workbook.Worksheets(1).Cells(16, 3).Text = "3293"
    spreadSheet1.Workbook.Worksheets(1).Cells(0, 4).Text = "Product:"
    spreadSheet1.Workbook.Worksheets(1).Cells(0, 4).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(1).Cells(0, 5).Text = "Apple"
    spreadSheet1.Workbook.Worksheets(1).Cells(1, 4).Text = "Region:"
    spreadSheet1.Workbook.Worksheets(1).Cells(1, 4).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(1).Cells(1, 5).Text = "East"
    spreadSheet1.Workbook.Worksheets(1).Cells(2, 5).Text = "Filtering performed on one Criteria"
    spreadSheet1.Workbook.Worksheets(1).Cells(2, 5).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightBlue)
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 5).Text = "Region"
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 5).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 6).Text = "Sales Rep"
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 6).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 7).Text = "Product"
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 7).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 8).Text = "Units"
    spreadSheet1.Workbook.Worksheets(1).Cells(3, 8).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    ' Setting "Filter" Formula( with one condition) wherein we filter range A5:D17 based upon criteria wherein range C5:C17 is equal to value in cell F1 
    spreadSheet1.Workbook.Worksheets(1).Cells(4, 5).Formula2 = "FILTER(A5:D17, C5:C17=F1)"
    spreadSheet1.Workbook.Worksheets(1).Cells(12, 5).Text = "Region"
    spreadSheet1.Workbook.Worksheets(1).Cells(12, 5).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(1).Cells(12, 6).Text = "Sales Rep"
    spreadSheet1.Workbook.Worksheets(1).Cells(12, 6).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(1).Cells(12, 7).Text = "Product"
    spreadSheet1.Workbook.Worksheets(1).Cells(12, 7).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(1).Cells(12, 8).Text = "Units"
    spreadSheet1.Workbook.Worksheets(1).Cells(12, 8).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightGray)
    spreadSheet1.Workbook.Worksheets(1).Cells(11, 5).Text = "Filtering performed on two Criteria"
    spreadSheet1.Workbook.Worksheets(1).Cells(11, 5).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LightBlue)

    ' Setting "Filter" Formula( with two conditions) wherein we filter range A5:D17 based upon criteria wherein range C5:C17 is equal to value in cell F1 and range A5:A17 is equal to value in cell F2 
    spreadSheet1.Workbook.Worksheets(1).Cells(13, 5).Formula2 = "FILTER(A5:D17, (C5:C17=F1)*(A5:A17=F2))"
    Dim worksheet2 As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.Worksheets.Add()
    ' Setting "Sequence" Formula 
    spreadSheet1.Workbook.Worksheets(2).Columns(0, 7).ColumnWidth = 130
    spreadSheet1.Workbook.Worksheets(2).Cells(0, 0).Text = "SEQUENCE(4,5) Function"
    spreadSheet1.Workbook.Worksheets(2).Cells(0, 0).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.SkyBlue)
    spreadSheet1.Workbook.Worksheets(2).Cells(1, 0).Formula2 = "SEQUENCE(4,5)"
    ' Setting "RandArray" Formula 
    spreadSheet1.Workbook.Worksheets(2).Cells(6, 0).Text = "RANDARRAY(5,3) Function"
    spreadSheet1.Workbook.Worksheets(2).Cells(6, 0).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.SkyBlue)
    spreadSheet1.Workbook.Worksheets(2).Cells(7, 0).Formula2 = "RANDARRAY(5,3)"
    ' Setting "Single" Formula 
    spreadSheet1.Workbook.Worksheets(2).Cells(13, 0).Text = "SINGLE(A15:E15) Function"
    spreadSheet1.Workbook.Worksheets(2).Cells(13, 0).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.SkyBlue)
    spreadSheet1.Workbook.Worksheets(2).Cells(14, 0).Value = "A"
    spreadSheet1.Workbook.Worksheets(2).Cells(14, 1).Value = "B"
    spreadSheet1.Workbook.Worksheets(2).Cells(14, 2).Value = "C"
    spreadSheet1.Workbook.Worksheets(2).Cells(14, 3).Value = "D"
    spreadSheet1.Workbook.Worksheets(2).Cells(14, 4).Value = "E"
    spreadSheet1.Workbook.Worksheets(2).Cells(15, 2).Formula = "SINGLE(A15:E15)"