Microsoft Excel 365 has introduced several new dynamic array functions that make data handling faster, cleaner, and smarter. As an MIS Executive or Data Analyst, these functions can save hours of manual work. In this article, we’ll explore 20 powerful Excel 365 functions with their syntax, parameter explanations, and two real-life examples each.
1. UNIQUE()
Syntax:
=UNIQUE(array, [by_col], [exactly_once])
Parameters:
- array – The range or array to return unique values from.
- by_col – (Optional) TRUE checks columns, FALSE checks rows. Default is FALSE.
- exactly_once – (Optional) TRUE returns values that appear only once.
Example 1: Extract unique customer names
=UNIQUE(A2:A20)
Returns a list of distinct customers from column A.
Example 2: Get items sold exactly once
=UNIQUE(B2:B20,,TRUE)
Returns only items that appear once in column B.
2. SORT()
Syntax:
=SORT(array, [sort_index], [sort_order], [by_col])
Parameters:
- array – Range/array to sort.
- sort_index – Column/row number to sort by.
- sort_order – 1 = Ascending, -1 = Descending.
- by_col – Sort by row (FALSE) or by column (TRUE).
Example 1: Sort sales data ascending
=SORT(A2:B20,2,1)
Sorts by column 2 (Sales) in ascending order.
Example 2: Sort by names descending
=SORT(A2:A20,1,-1)
Returns names sorted Z → A.
Read More: XLOOKUP in Excel
3. SORTBY()
Syntax:
=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], …)
Parameters:
- array – The data to sort.
- by_array1 – Array to sort by.
- sort_order1 – 1 (Ascending), -1 (Descending).
- by_array2… – Optional additional arrays to sort by.
Example 1: Sort employees by salary
=SORTBY(A2:C20,C2:C20,-1)
Sorts employees based on salary (descending).
Example 2: Sort products by category then sales
=SORTBY(A2:C20,B2:B20,1,C2:C20,-1)
First sorts by category, then within category by sales (descending).
Read More: FILTER Function in Excel
4. SEQUENCE()
Syntax:
=SEQUENCE(rows, [columns], [start], [step])
Parameters:
- rows – Number of rows.
- columns – Number of columns.
- start – Starting number.
- step – Increment.
Example 1: Generate list of dates
=SEQUENCE(10,1,DATE(2025,1,1),1)
Generates 10 dates starting from Jan 1, 2025.
Example 2: Employee IDs
=SEQUENCE(20,1,1001,1)
Creates IDs from 1001 to 1020.
5. RANDARRAY()
Syntax:
=RANDARRAY([rows], [columns], [min], [max], [integer])
Parameters:
- rows – Number of rows.
- columns – Number of columns.
- min – Minimum value.
- max – Maximum value.
- integer – TRUE = integers, FALSE = decimals.
Example 1: Random scores
=RANDARRAY(10,1,50,100,TRUE)
Generates random scores between 50 and 100.
Example 2: Random discount percentages
=RANDARRAY(5,1,0.05,0.3,FALSE)
Generates 5 random discounts between 5% and 30%.
6. CHOOSECOLS()
Syntax:
=CHOOSECOLS(array, col_num1, [col_num2], …)
Parameters:
- array – The source data.
- col_num1, col_num2… – Column numbers to extract.
Example 1: Extract only Name & Salary
=CHOOSECOLS(A2:D20,1,3)
Returns only columns 1 and 3.
Example 2: Extract last column
=CHOOSECOLS(A2:E20,-1)
Returns the last column from the range.
7. CHOOSEROWS()
Syntax:
=CHOOSEROWS(array, row_num1, [row_num2], …)
Parameters:
- array – The source data.
- row_num1, row_num2… – Row numbers to return.
Example 1: Pick 1st and 5th row
=CHOOSEROWS(A2:C20,1,5)
Returns only rows 1 and 5.
Example 2: Get last row
=CHOOSEROWS(A2:C20,-1)
Extracts the last row.
8. WRAPROWS()
Syntax:
=WRAPROWS(vector, wrap_count, [pad_with])
Parameters:
- vector – Single-row/column array.
- wrap_count – Number of values per row.
- pad_with – Value to fill if empty.
Example 1: Wrap into 3 columns
=WRAPROWS(A2:A10,3)
Arranges list into rows of 3.
Example 2: Wrap with padding
=WRAPROWS(SEQUENCE(10),4,"N/A")
Arranges 10 numbers into 4 columns, fills blanks with N/A.
Read More: TEXTBEFORE & TEXTAFTER in Excel
9. WRAPCOLS()
Syntax:
=WRAPCOLS(vector, wrap_count, [pad_with])
Parameters:
- vector – Single-row/column array.
- wrap_count – Number of values per column.
- pad_with – Value to fill.
Example 1: Wrap into 3 rows
=WRAPCOLS(A2:A10,3)
Arranges into columns with 3 values each.
Example 2: Wrap with placeholder
=WRAPCOLS(SEQUENCE(7),3,"-")
Arranges 7 numbers into columns of 3, fills empty with “-“.
10. TOCOL()
Syntax:
=TOCOL(array, [ignore], [scan_by_column])
Parameters:
- array – The source range.
- ignore – 0 = keep blanks, 1 = remove blanks.
- scan_by_column – TRUE = by column, FALSE = by row.
Example 1: Convert to single column
=TOCOL(A2:C5)
Flattens the array into one column.
Example 2: Remove blanks
=TOCOL(A2:C10,1)
Converts range to column excluding blanks.
11. TOROW()
Syntax:
=TOROW(array, [ignore], [scan_by_column])
Parameters:
- array – The source range.
- ignore – 0 = keep blanks, 1 = remove blanks.
- scan_by_column – TRUE = by column, FALSE = by row.
Example 1: Convert to single row
=TOROW(A2:C5)
Flattens into one row.
Example 2: Ignore blanks
=TOROW(A2:C10,1)
Returns one row excluding blank cells.
Read More: TEXTSPLIT & TEXTJOIN in Excel
12. LET()
Syntax:
=LET(name1, value1, [name2, value2], …, calculation)
Parameters:
- name1 – Variable name.
- value1 – Assigned value.
- calculation – Formula that uses the variables.
Example 1: Store and reuse value
=LET(x,10,y,20,x+y)
Defines x=10, y=20, returns 30.
Example 2: Complex calculation
=LET(total,SUM(B2:B20),count,COUNTA(B2:B20),total/count)
Calculates average using stored variables.
13. LAMBDA()
Syntax:
=LAMBDA(parameter1, parameter2,…, calculation)
Parameters:
- parameter1, parameter2… – Arguments to pass.
- calculation – Formula to execute.
Example 1: Simple addition
=LAMBDA(x,y,x+y)(5,10)
Returns 15.
Example 2: Square function
=LAMBDA(n,n^2)(6)
Returns 36.
👉 More about Lambda Function
14. MAP()
Syntax:
=MAP(array1, [array2], …, lambda)
Parameters:
- array1, array2… – Arrays to process.
- lambda – Function applied to each item.
Example 1: Double all numbers
=MAP(A2:A10,LAMBDA(x,x*2))
Returns each value multiplied by 2.
Example 2: Concatenate names
=MAP(A2:A10,B2:B10,LAMBDA(x,y,x&" "&y))
Combines first and last names.
15. BYROW()
Syntax:
=BYROW(array, lambda)
Parameters:
- array – Input range.
- lambda – Function applied to each row.
Example 1: Row sum
=BYROW(A2:C10,LAMBDA(r,SUM(r)))
Returns sum of each row.
Example 2: Row average
=BYROW(A2:C10,LAMBDA(r,AVERAGE(r)))
Returns average per row.
16. BYCOL()
Syntax:
=BYCOL(array, lambda)
Parameters:
- array – Input range.
- lambda – Function per column.
Example 1: Column totals
=BYCOL(A2:D20,LAMBDA(c,SUM(c)))
Returns sum of each column.
Example 2: Column max
=BYCOL(A2:D20,LAMBDA(c,MAX(c)))
Returns maximum value in each column.
17. MAKEARRAY()
Syntax:
=MAKEARRAY(rows, columns, lambda)
Parameters:
- rows – Number of rows.
- columns – Number of columns.
- lambda – Formula to generate values.
Example 1: Generate multiplication table
=MAKEARRAY(5,5,LAMBDA(r,c,r*c))
Creates 5×5 multiplication table.
Example 2: Custom sequence
=MAKEARRAY(3,3,LAMBDA(r,c,r+c))
Each cell shows row+column number.
18. REDUCE()
Syntax:
=REDUCE(initial_value, array, lambda)
Parameters:
- initial_value – Starting value.
- array – Input range.
- lambda – Function applied.
Example 1: Product of numbers
=REDUCE(1,A2:A10,LAMBDA(a,b,a*b))
Multiplies all numbers.
Example 2: Concatenate text
=REDUCE("",A2:A5,LAMBDA(a,b,a&" "&b))
Concatenates text strings.
19. VSTACK()
Syntax:
=VSTACK(array1, [array2], …)
Parameters:
- array1, array2… – Arrays to combine vertically.
Example 1: Combine two lists
=VSTACK(A2:A10,B2:B10)
Stacks lists vertically.
Example 2: Add headers + data
=VSTACK({"Name","Sales"},A2:B10)
Adds headers above data.
20. HSTACK()
Syntax:
=HSTACK(array1, [array2], …)
Parameters:
- array1, array2… – Arrays to combine horizontally.
Example 1: Combine two columns side by side
=HSTACK(A2:A10,B2:B10)
Joins columns horizontally.
Example 2: Add ID with dataset
=HSTACK(SEQUENCE(10),A2:B11)
Adds auto-generated ID column before dataset.
Final Thoughts
These 20 Excel 365 functions unlock new possibilities for MIS reporting, data cleaning, and automation. By combining them, you can create powerful formulas that used to require VBA or manual work. Start with simple functions like UNIQUE and SORT, then gradually explore advanced ones like LET, LAMBDA, and MAKEARRAY.