Lesson#163: 6 new excel formulas must know in 2022

Microsoft Excel has introduced some new functions in 2022. Here are 6 of the new excel formulas among all of them. Today I shall discuss 6 new Excel lookup and reference functions CHOOSECOLS, CHOOSEROWS, HSTACK, VSTACK, DROP and TAKE.

What is the Excel CHOOSECOLS function?

Excel CHOOSECOLS function returns the specified columns from an array.

Syntax  CHOOSECOLS(array,col_num1,[col_num2],…)

array       The array containing the columns to be returned in the new array. Required.

col_num1       The first column to be returned. Required.

col_num2      Additional columns to be returned. Optional.

Examples of Excel CHOOSECOLS function

Note: – It will show a #VALUE! error if you put column number 0 or more than in the specified array.

What is the Excel CHOOSEROWS function?

Excel CHOOSEROWS function returns the specified rows from an array.

Syntax  CHOOSEROWS(array,row_num1,[row_num2],…)

array       The array containing the columns to be returned in the new array. Required.

row_num1    The first-row number to be returned. Required.

[row_num2]    Additional row numbers to be returned. Optional.

Examples of Excel CHOOSEROWS function

Note: – It will show a #VALUE! error if you put row number 0 or more than in the specified array.

What is the Excel HSTACK function?

Excel HSTACK function horizontally and sequentially appends arrays to produce a larger array.

Syntax  HSTACK(array1,[array2],…)

array  Mandatory. A reference to a cell or range of cells or an array.

Examples of the Excel HSTACK function

In the above picture, I have shown simple examples of two arrays.

Now I have implemented the HSTACK formula on this sheet.

See both arrays are arranged in a horizontal manner.

What is the Excel VSTACK function?

Excel VSTACK function vertically and sequentially appends arrays to produce a larger array.

Description        Excel VSTACK function vertically and sequentially appends arrays to produce a larger array.

Syntax  VSTACK(array1,[array2],…)

array  Mandatory. A reference to a cell or range of cells or an array.

Examples of the Excel VSTACK function

In the above picture, I have shown simple examples of two arrays.

Now I have implemented the VSTACK formula on this sheet.

See both arrays are arranged in a vertical manner.

What is the Excel DROP function?

Excludes a number of rows or columns from the beginning or end of an array. This function may be useful for removing headers and footers from an Excel report in order to return only the data.

Syntax: DROP(array, rows,[columns])

array    The array from which to drop rows or columns.

rows    The number of rows to drop. A negative value drops from the end of the array.

columns  The number of columns to exclude. A negative value drops from the end of the array.

Examples of Excel DROP functions

In the above pictures, you can see that the DROP function has excluded the first 2 columns and the first 2 rows.

In the above pictures, you can see that the DROP function has excluded the first 2 columns and the last 2 rows.

What is the Excel TAKE function?

Returns a number of adjoining rows or columns from the beginning or end of an array.

Syntax: TAKE(array, rows,[columns])

array    The array from which to take rows or columns.

rows    The number of rows to take. A negative value takes from the end of the array.

columns  The number of columns to take. A negative value takes from the end of the array.

Examples of Excel TAKE function

TAKE function only return the values between the intersection of the first 2 rows and the first 2 columns.

TAKE function only return the values between the intersection of the first 2 rows and the last 2 columns.

Note: Excel returns a #VALUE! error to indicate an empty array when either rows or columns are 0.

Excel returns a #NUM when the array is too large.

How can you get these new excel formulas?

Currently, CHOOSECOLS, CHOOSEROWS, HSTACK, VSTACK, DROP, and TAKE are available on Excel for Microsoft 365 (Windows), Excel for Microsoft 365 (Mac), and  Excel for the web only. Only from there, you can get these new excel formulas.

Join our Telegram channel https://t.me/excelabcd

Here you shall be regularly updated with Excel tips and tricks.