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.
Leave a Reply