{"id":2808,"date":"2023-01-13T16:02:03","date_gmt":"2023-01-13T16:02:03","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=2808"},"modified":"2023-02-28T14:38:47","modified_gmt":"2023-02-28T14:38:47","slug":"new-excel-formulas-for-microsoft-365-in-2022-and-2021-part-1","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/new-excel-formulas-for-microsoft-365-in-2022-and-2021-part-1\/","title":{"rendered":"New Excel Formulas for Microsoft 365 in 2022 and 2021: Part 1"},"content":{"rendered":"<p>Microsoft 365 is the latest and upgraded version of Microsoft Excel. This new and upgraded version has a large number and variety of functions and formulas added in it for better use of its users. In this tutorial, we will see and know about some of the new formulas of Microsoft 365, their functions, and how to use them.<\/p>\n<p><strong>\u00a0<\/strong><strong>This Content Covers:<\/strong><\/p>\n<ol>\n<li><a href=\"#arraytotext-formula\"><strong>ARRAYTOTEXT Formula<\/strong><\/a><\/li>\n<li><a href=\"#bycol-formula\"><strong>BYCOL Formula<\/strong><\/a><\/li>\n<li><a href=\"#byrow-formula\"><strong>BYROW Formula<\/strong><\/a><\/li>\n<li><a href=\"#choosecols-formula\"><strong>CHOOSECOLS Formula<\/strong><\/a><\/li>\n<li><a href=\"#chooserows-formula\"><strong>CHOOSEROWS Formula<\/strong><\/a><\/li>\n<li><a href=\"#drop-formula\"><strong>DROP Formula<\/strong><\/a><\/li>\n<li><a href=\"#expand-formula\"><strong>EXPAND Formula<\/strong><\/a><\/li>\n<li><a href=\"#fieldvalue-formula\"><strong>FIELDVALUE Formula<\/strong><\/a><\/li>\n<li><a href=\"#hstack-formula\"><strong>HSTACK Formula<\/strong><\/a><\/li>\n<li><a href=\"#isomitted-formula\"><strong>ISOMITTED Formula<\/strong><\/a><\/li>\n<\/ol>\n<ol>\n<li><strong id=\"arraytotext-formula\">\u00a0ARRAYTOTEXT Formula<\/strong><\/li>\n<\/ol>\n<p><strong>Purpose of the Function<\/strong><\/p>\n<p>The purpose of <strong>ARRAYTOTEXT<\/strong> function is to return an array of text values from a specified range of cells and columns. While returning an array of texts, it keeps the text values unchanged and converts the not-text values into texts.<\/p>\n<p><strong>\u00a0<\/strong><strong>Function Syntax and Arguments<\/strong><\/p>\n<p><strong>Syntax: <\/strong><\/p>\n<p>ARRAYTOTEXT(array,[format])<\/p>\n<p><strong>\u00a0<\/strong><strong>Arguments<\/strong><\/p>\n<ul>\n<li>The formula has to return the array as text.<\/li>\n<li>The format of the returned data can be of two values either 0 or 1. Here 0 is for concise and 1 for strict.<\/li>\n<\/ul>\n<p><strong>\u00a0<\/strong><strong>Examples and Output Result (including suitable data set and table) <\/strong><\/p>\n<p>Both the format values will be applied in this dataset to see if they return as text values or not.<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" class=\"aligncenter size-full wp-image-2809\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-1.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"580\" height=\"185\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-1.png 580w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-1-300x96.png 300w\" sizes=\"(max-width: 580px) 100vw, 580px\" \/><\/p>\n<p><strong>0\/Concise: <\/strong>Select cell C1 and insert the formula given in the picture below and click ENTER.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter size-full wp-image-2810\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-2.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"578\" height=\"168\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-2.png 578w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-2-300x87.png 300w\" sizes=\"(max-width: 578px) 100vw, 578px\" \/><\/p>\n<p><strong>1\/Strict: <\/strong>Enter this formula in cell C1 and press ENTER.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter size-full wp-image-2811\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-3.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"587\" height=\"163\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-3.png 587w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-3-300x83.png 300w\" sizes=\"(max-width: 587px) 100vw, 587px\" \/><\/p>\n<p><strong>Explanation of Output Result <\/strong><\/p>\n<p><strong>The concise (0)<\/strong> formula returned the text values unchanged from the selected range.<\/p>\n<p><strong>The<\/strong> <strong>Strict (1)<\/strong> formula returned the text values with quotation marks applied on each of the values separately from the selected range.<\/p>\n<p>Both the formulas changed the value of cell A3 from a non-text value to text value.<\/p>\n<ol start=\"2\">\n<li><strong id=\"bycol-formula\"> BYCOL Formula<\/strong><\/li>\n<\/ol>\n<p><strong>Purpose of the Function<\/strong><\/p>\n<p>The purpose of <strong>BYCOL<\/strong> function is to return one result per column as a single array after applying a<strong> LAMBDA<\/strong> function to each column in the specified array.<\/p>\n<p><strong>\u00a0<\/strong><strong>Function Syntax and Arguments<\/strong><\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<p>=BYCOL(array, lambda(column))<\/p>\n<p><strong>Arguments:<\/strong><\/p>\n<ul>\n<li>An array with column separation.<\/li>\n<li>The <strong>LAMBDA <\/strong>function to be applied in each column but will calculate only one result.<\/li>\n<li>A column from an array.<\/li>\n<\/ul>\n<p><strong>\u00a0<\/strong><strong>Examples and Output Result (including suitable data set and table)<\/strong><\/p>\n<p>Here we have a range of values in which we have to apply the <strong>BYCOL<\/strong> formula.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2812\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-4.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"344\" height=\"188\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-4.png 344w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-4-300x164.png 300w\" sizes=\"(max-width: 344px) 100vw, 344px\" \/><\/p>\n<p><strong>Example 1: <\/strong>Select cell E1&gt;&gt;insert the formula in the picture and hit ENTER.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2813\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-5.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"520\" height=\"180\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-5.png 520w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-5-300x104.png 300w\" sizes=\"(max-width: 520px) 100vw, 520px\" \/><\/p>\n<p><strong>Example 2: <\/strong>Type the formula in E1 and press ENTER key.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2814\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-6.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"583\" height=\"187\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-6.png 583w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-6-300x96.png 300w\" sizes=\"(max-width: 583px) 100vw, 583px\" \/><\/p>\n<p><strong>Example 3: <\/strong>Inset the formula in cell E1 and click ENTER.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2815\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-7.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"591\" height=\"211\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-7.png 591w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-7-300x107.png 300w\" sizes=\"(max-width: 591px) 100vw, 591px\" \/><\/p>\n<p><strong>Explanation of Output Result <\/strong><\/p>\n<p>The formula in <strong>Example 1<\/strong> returns the maximum\/highest value of each column using <strong>MAX <\/strong>function and <strong>Example 2<\/strong> returns the minimum value of each column using <strong>MIN <\/strong>function.<\/p>\n<p>The formula in <strong>Example 3 <\/strong>uses<strong> SUMSQ<\/strong> function and returns the sum of the squared value of each array element in each column.<\/p>\n<ol start=\"3\">\n<li><strong id=\"byrow-formula\"> BYROW Formula<\/strong><\/li>\n<\/ol>\n<p><strong>Purpose of the Function<\/strong><\/p>\n<p>The purpose of <strong>BYROW<\/strong> function is to return one result per row as a single array after applying a<strong> LAMBDA<\/strong> function to each row in the specified array.<\/p>\n<p><strong>Function Syntax and Arguments<\/strong><\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<p>=BYROW(array, lambda(row))<\/p>\n<p><strong>Arguments:<\/strong><\/p>\n<ul>\n<li>An array with row separation.<\/li>\n<li>The <strong>LAMBDA <\/strong>function to be applied in each row but will calculate only one result.<\/li>\n<li>A row from an array.<\/li>\n<\/ul>\n<p><strong>\u00a0<\/strong><strong>Examples and Output Result (including suitable data set and table)<\/strong><\/p>\n<p>In this case, we will use the<strong> BYROW<\/strong> formula on this set of values.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2816\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-8.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"404\" height=\"219\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-8.png 404w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-8-300x163.png 300w\" sizes=\"(max-width: 404px) 100vw, 404px\" \/><\/p>\n<p><strong>Example 1: <\/strong>Type the formula in E1 and press ENTER key.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2817\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-9.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"545\" height=\"186\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-9.png 545w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-9-300x102.png 300w\" sizes=\"(max-width: 545px) 100vw, 545px\" \/><\/p>\n<p><strong>Example 2: <\/strong>Select cell E1&gt;&gt;insert the formula in the picture and hit ENTER.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2818\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-10.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"553\" height=\"192\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-10.png 553w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-10-300x104.png 300w\" sizes=\"(max-width: 553px) 100vw, 553px\" \/><\/p>\n<p><strong>Explanation of Output Result <\/strong><\/p>\n<p><strong>Example 1<\/strong> returns the maximum\/highest value of each row using <strong>MAX<\/strong> function.<\/p>\n<p>The formula in <strong>Example 2<\/strong> returns the sum of the squared value of each array element in each row using <strong>SUMSQ<\/strong> function.<\/p>\n<ol start=\"4\">\n<li><strong id=\"choosecols-formula\"> CHOOSECOLS Formula<\/strong><\/li>\n<\/ol>\n<p><strong>Purpose of the Function<\/strong><\/p>\n<p>The purpose of <strong>CHOOSECOLS<\/strong> function is to return specified columns from an array.<\/p>\n<p><strong>\u00a0<\/strong><strong>Function Syntax and Arguments<\/strong><\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<p>=CHOOSECOLS(array,col_num1,col_num2\u2026.)<\/p>\n<p><strong>Arguments:<\/strong><\/p>\n<ul>\n<li>The array that contains the columns will be returned in the new array.<\/li>\n<li>The first column has to be returned as this is compulsory.<\/li>\n<li>Additional columns also will be returned, not compulsory.<\/li>\n<\/ul>\n<p><strong>\u00a0<\/strong><strong>Examples and Output Result (including suitable data set and table)<\/strong><\/p>\n<p><strong>Example 1: <\/strong>Insert the formula in the picture below in cell E1 and click ENTER.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2819\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-11.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"494\" height=\"225\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-11.png 494w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-11-300x137.png 300w\" sizes=\"(max-width: 494px) 100vw, 494px\" \/><\/p>\n<p><strong>Example 2: <\/strong>Choose cell E1 and type the formula inside this cell. Hit ENTER.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2820\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-12.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"594\" height=\"195\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-12.png 594w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-12-300x98.png 300w\" sizes=\"(max-width: 594px) 100vw, 594px\" \/><\/p>\n<p><strong>Explanation of Output Result <\/strong><\/p>\n<p>The formula in <strong>Example 1<\/strong> returns the chosen column (col 3) in cell E1. The formula returns only one column here as we put the serial number of only column 3.<\/p>\n<p>In <strong>Example 2, <\/strong>we have selected the columns serial number as 2,1,3,1. The formula returns these specified columns in this order. We also can see column 1 appearing twice as we have chosen column 1 in two occasions in the formula.<\/p>\n<ol start=\"5\">\n<li><strong id=\"chooserows-formula\"> CHOOSEROWS Formula<\/strong><\/li>\n<\/ol>\n<p><strong>Purpose of the Function<\/strong><\/p>\n<p>The purpose of <strong>CHOOSEROWS<\/strong> function is to return specified rows from an array.<\/p>\n<p><strong>\u00a0<\/strong><strong>Function Syntax and Arguments<\/strong><\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<p>=CHOOSEROWS(array,row_num1,row_num2\u2026..)<\/p>\n<p><strong>Arguments:<\/strong><\/p>\n<ul>\n<li>The array that contains the rows will be returned in the new array.<\/li>\n<li>The first row has to be returned as this is compulsory.<\/li>\n<li>Additional rows also will return, not compulsory.<\/li>\n<\/ul>\n<p><strong>\u00a0<\/strong><strong>Examples and Output Result (including suitable data set and table)<\/strong><\/p>\n<p><strong>Example 1:<\/strong> Insert the formula given in below picture in cell E1 after selecting it. Press ENTER key.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2821\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-13.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"518\" height=\"204\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-13.png 518w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-13-300x118.png 300w\" sizes=\"(max-width: 518px) 100vw, 518px\" \/><\/p>\n<p><strong>Explanation of Output Result <\/strong><\/p>\n<p>In <strong>Example 1 <\/strong>we have selected the serial of rows as 4,2,1 so the formula has returned the specified rows according to this serial. Row 3 is not returned here because we did not specify it inside the formula.<\/p>\n<ol start=\"6\">\n<li><strong id=\"drop-formula\"> DROP Formula<\/strong><\/li>\n<\/ol>\n<p><strong>Purpose of the Function<\/strong><\/p>\n<p>The DROP formula\u2019s purpose is to prevent a certain number of rows or columns from being included at the beginning or end of an array.<\/p>\n<p><strong>\u00a0<\/strong><strong>Function Syntax and Arguments<\/strong><\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<p>=DROP(array, rows,[columns])<\/p>\n<p><strong>\u00a0<\/strong><strong>Arguments:<\/strong><\/p>\n<ul>\n<li>The array or range from which the rows and columns will be dropped.<\/li>\n<li>The quantity of rows to remove. A negative value will drop rows from the end of an array.<\/li>\n<li>The number of columns to drop. A negative value will drop columns from the end of an array.<\/li>\n<\/ul>\n<p><strong>Examples and Output Result (including suitable data set and table)<\/strong><\/p>\n<p>We will apply the DROP formula in this datasheet to see how the formula works.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2822\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-14.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"451\" height=\"238\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-14.png 451w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-14-300x158.png 300w\" sizes=\"(max-width: 451px) 100vw, 451px\" \/><\/p>\n<p><strong>Example 1: <\/strong>Select cell F1 and insert the formula given in the below picture. Click ENTER<strong>.<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2823\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-15.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"646\" height=\"183\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-15.png 646w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-15-300x85.png 300w\" sizes=\"(max-width: 646px) 100vw, 646px\" \/><\/p>\n<p><strong>Example 2: <\/strong>Insert the formula from the below given picture in cell F1 and hit ENTER key.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2825\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-17.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"638\" height=\"209\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-17.png 638w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-17-300x98.png 300w\" sizes=\"(max-width: 638px) 100vw, 638px\" \/><\/p>\n<p><strong>Example 3: <\/strong>Type the formula in F1 after selecting this cell and press ENTER.<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2827\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-17-1.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"595\" height=\"212\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-17-1.png 595w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-17-1-300x107.png 300w\" sizes=\"(max-width: 595px) 100vw, 595px\" \/><\/p>\n<p><strong>Example 4: <\/strong>Click on cell F1&gt;&gt;insert the formula&gt;&gt;press ENTER.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2828\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-18-1.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"579\" height=\"215\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-18-1.png 579w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-18-1-300x111.png 300w\" sizes=\"(max-width: 579px) 100vw, 579px\" \/><\/p>\n<p><strong>Explanation of Output Result <\/strong><\/p>\n<p><strong>Example 1<\/strong> drops or removes the first three rows as we selected 3 in the formula which indicates the number of rows that will be dropped.<\/p>\n<p>In <strong>Example 2 <\/strong>we used two comas ( ,, ) and select 2 as the number of columns that should be removed or dropped. These\u00a0\u00a0 double comas enable the formula to be applied for columns instead of rows. As we selected 2 inside the formula, the first two columns have been dropped.<\/p>\n<p>We used a minus ( &#8211; ) value in <strong>Example 3<\/strong> which dropped the last two columns from the array.<\/p>\n<p><strong>Example 4<\/strong> drops first two rows and last two columns as we selected a positive and a negative value inside the formula ( 2,-2 ).<\/p>\n<ol start=\"7\">\n<li><strong id=\"expand-formula\"> EXPAND Formula<\/strong><\/li>\n<\/ol>\n<p><strong>Purpose of the Function<\/strong><\/p>\n<p>The purpose of EXPAND formula is to expand or pad an array to the given number of rows and columns.<\/p>\n<p><strong>Function Syntax and Arguments<\/strong><\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<p>=Expand(array, rows, [columns], [pad_with])<\/p>\n<p><strong>\u00a0<\/strong><strong>Arguments:<\/strong><\/p>\n<ul>\n<li>An array or a range to expand.<\/li>\n<li>The expanded array&#8217;s total number of rows. Rows won&#8217;t be expanded if they are missing.<\/li>\n<li>The expanded array&#8217;s total number of columns. Columns won&#8217;t be expanded if they are missing.<\/li>\n<li>The default value to pad with is <strong>#N\/A<\/strong> which is changeable.<\/li>\n<\/ul>\n<p><strong>\u00a0<\/strong><strong>Examples and Output Result (including suitable data set and table)<\/strong><\/p>\n<p><strong>Example 1: <\/strong>Select cell F1 and insert the formula in that cell. Hit ENTER.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2829\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-19.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"635\" height=\"174\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-19.png 635w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-19-300x82.png 300w\" sizes=\"(max-width: 635px) 100vw, 635px\" \/><\/p>\n<p><strong>Example 2: <\/strong>Type the formula in cell F1 after selecting it and press ENTER key.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2830\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-20.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"641\" height=\"195\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-20.png 641w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-20-300x91.png 300w\" sizes=\"(max-width: 641px) 100vw, 641px\" \/><\/p>\n<p><strong>Explanation of Output Result <\/strong><\/p>\n<p><strong>Example 1<\/strong> resizes a 5X4 array to a 6X6 array and pad empty elements with <strong>#N\/A.<\/strong><\/p>\n<p><strong>Example 2<\/strong> resizes a 5X4 array to a 6X5 array and pads empty elements with <strong>&#8220;-&#8221; <\/strong>as we selected <strong>(\u201c-\u201c) <\/strong>inside the formula.<strong>\u00a0<\/strong><\/p>\n<ol start=\"8\">\n<li><strong id=\"fieldvalue-formula\"> FIELDVALUE Formula<\/strong><\/li>\n<\/ol>\n<p><strong>Purpose of the Function<\/strong><\/p>\n<p>The purpose of<strong> FIELDVALUE<\/strong> formula is to obtain field data from linked data types like Stocks or Geography data types etc. The <strong>FIELDVALUE<\/strong> function should primarily be used to create conditional calculations using linked data types.<\/p>\n<p><strong>\u00a0<\/strong><strong>Function Syntax and Arguments<\/strong><\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<p>=FIELDVALUE(value, field_name)<\/p>\n<p><strong>\u00a0<\/strong><strong>Arguments:<\/strong><\/p>\n<ul>\n<li>When given a linked data type as the value parameter, the function returns any matching fields from that data type.<\/li>\n<li>The name(s) of the field(s) from the linked data type that you want to extract.<\/li>\n<\/ul>\n<p><strong style=\"font-size: 16px;\">Examples and Output Result (including suitable data set and table)<\/strong><\/p>\n<p><strong>Example 1: <\/strong>Select cell C3 and insert the formula from the picture below, hit ENTER. Click and drag the lower right corner of C3 to D3 to apply the formula in that cell too.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2831\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-21.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"632\" height=\"172\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-21.png 632w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-21-300x82.png 300w\" sizes=\"(max-width: 632px) 100vw, 632px\" \/><\/p>\n<p><strong>Example 2: <\/strong>Insert the formula shown below in cell C3&gt;&gt;press ENTER&gt;&gt;click and drag C3 to D3.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2832\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-22.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"642\" height=\"180\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-22.png 642w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-22-300x84.png 300w\" sizes=\"(max-width: 642px) 100vw, 642px\" \/><\/p>\n<p><strong>Explanation of Output Result <\/strong><\/p>\n<p>The formula =FIELDVALUE(A2,&#8221;Price&#8221;) in Example 1 extracts the Price Field for <strong>MICROSOFT CORPORATION (XNAS:MSFT)<\/strong>. The same formula returns a <strong>#FIELD<\/strong> error in cell D3 as it could not find the field value.<\/p>\n<p>To get rid of the <strong>#FIELD <\/strong>error from <strong>Example 1<\/strong>, we have used<strong> IFERROR<\/strong> with <strong>FIELDVALUE<\/strong> formula in <strong>Example 2<\/strong> to change the error text to <strong>( &#8211; ) <\/strong>if the formula doesn\u2019t find the field value.<\/p>\n<ol start=\"9\">\n<li><strong id=\"hstack-formula\">\u00a0HSTACK Formula<\/strong><\/li>\n<\/ol>\n<p><strong>Purpose of the Function<\/strong><\/p>\n<p>The purpose of <strong>HSTACK<\/strong> formula is to combine many smaller arrays into one bigger array by appending them horizontally and in order.<\/p>\n<p><strong>\u00a0<\/strong><strong>Function Syntax and Arguments<\/strong><\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<p>=HSTACK(array1,[array2],&#8230;)<\/p>\n<p><strong>\u00a0<\/strong><strong>Arguments:<\/strong><\/p>\n<ul>\n<li>The highest number of rows from each array parameter.<\/li>\n<li>The total number of columns from every array parameter.<\/li>\n<\/ul>\n<p><strong>\u00a0<\/strong><strong>Examples and Output Result (including suitable data set and table)<\/strong><\/p>\n<p><strong>Example 1: <\/strong>Insert the formula in J2 after selecting it and press ENTER.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2833\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-23.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"642\" height=\"132\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-23.png 642w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-23-300x62.png 300w\" sizes=\"(max-width: 642px) 100vw, 642px\" \/><\/p>\n<p><strong>Example 2: <\/strong>Click on ENTER button after selecting and inserting the formula from the below picture in cell J1.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2834\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-24.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"642\" height=\"122\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-24.png 642w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-24-300x57.png 300w\" sizes=\"(max-width: 642px) 100vw, 642px\" \/><\/p>\n<p><strong>Explanation of Output Result <\/strong><\/p>\n<p>The formula in <strong>Example 1<\/strong> horizontally\u00a0appends two simple arrays in sequence so that the results are one array.<\/p>\n<p>In <strong>Example 2<\/strong>, the formula horizontally\u00a0appends three simple arrays in sequence so that the results are one array. Cell G2 is empty so the formula returns a 0 in the combined array in cell N2 instead of a blank cell.<\/p>\n<ol start=\"10\">\n<li><strong id=\"isomitted-formula\"> ISOMITTED Formula<\/strong><\/li>\n<\/ol>\n<p><strong>Purpose of the Function<\/strong><\/p>\n<p>The purpose of ISOMITTED formula is to check whether the value in a\u00a0<a href=\"about:blank\"><strong>LAMBDA<\/strong>\u00a0<\/a>is missing and returns TRUE or FALSE.<\/p>\n<p><strong>Function Syntax and Arguments<\/strong><\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<p>=ISOMITTED(argument)<\/p>\n<p><strong>\u00a0<\/strong><strong>Arguments:<\/strong><\/p>\n<ul>\n<li>The value you wish to test, like a LAMBDA parameter.<\/li>\n<\/ul>\n<p><strong>\u00a0<\/strong><strong>Examples and Output Result (including suitable data set and table)<\/strong><\/p>\n<p><strong>Example 1: <\/strong>Select cell A1 and insert the formula shown in the picture. Press ENTER key.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2835\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-25.png\" alt=\"Microsoft 365 Excel New Formula in 2022 and 2021 - Part 1\" width=\"642\" height=\"171\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-25.png 642w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Microsoft-365-Excel-New-Formula-in-2022-and-2021-Part-1-25-300x80.png 300w\" sizes=\"(max-width: 642px) 100vw, 642px\" \/><\/p>\n<p><strong>Explanation of Output Result <\/strong><\/p>\n<p>The formula in <strong>Example 1<\/strong> has checked for a missing parameter and returned a friendly string.<\/p>\n<p>&nbsp;<\/p>\n<p>You may be interested:<\/p>\n<ol>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Finance\" target=\"_blank\" rel=\"noopener\">Financial Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Sales\" target=\"_blank\" rel=\"noopener\">Sales Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Human-Resource\" target=\"_blank\" rel=\"noopener\">HR Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.executiveknowledge.org\/dashboard-templates\/35\" target=\"_blank\" rel=\"noopener\">Data Visualization Charts<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>Microsoft 365 is the latest and upgraded version of Microsoft Excel. This new and upgraded version has a large number and variety of functions and formulas added in it for better use of its users. In this tutorial, we will see and know about some of the new formulas of Microsoft 365, their functions, and &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/www.bizinfograph.com\/resource\/new-excel-formulas-for-microsoft-365-in-2022-and-2021-part-1\/\"> <span class=\"screen-reader-text\">New Excel Formulas for Microsoft 365 in 2022 and 2021: Part 1<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"categories":[390],"tags":[384,391],"class_list":["post-2808","post","type-post","status-publish","format-standard","hentry","category-new-excel-formulas","tag-formulas-for-microsoft-365-in-2022-and-2021-part-1","tag-new-excel-formulas"],"_links":{"self":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/2808","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/comments?post=2808"}],"version-history":[{"count":4,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/2808\/revisions"}],"predecessor-version":[{"id":4892,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/2808\/revisions\/4892"}],"wp:attachment":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/media?parent=2808"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/categories?post=2808"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/tags?post=2808"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}