{"id":495062,"date":"2022-09-21T21:00:06","date_gmt":"2022-09-21T18:00:06","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-combine-reshape-and-resize-arrays-in-excel\/"},"modified":"2022-09-21T21:00:06","modified_gmt":"2022-09-21T18:00:06","slug":"how-to-combine-reshape-and-resize-arrays-in-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-combine-reshape-and-resize-arrays-in-excel\/","title":{"rendered":"#How to Combine, Reshape, and Resize Arrays in Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a3e7765cd0b2\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a3e7765cd0b2\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-combine-reshape-and-resize-arrays-in-excel\/#%E2%80%9CHow_to_Combine_Reshape_and_Resize_Arrays_in_Excel%E2%80%9D\" >&#8220;How to Combine, Reshape, and Resize Arrays in Excel&#8221;<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-combine-reshape-and-resize-arrays-in-excel\/#Combine_Arrays\" >Combine Arrays<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-combine-reshape-and-resize-arrays-in-excel\/#Reshape_Arrays\" >Reshape Arrays<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-combine-reshape-and-resize-arrays-in-excel\/#Convert_an_Array_to_a_Row_or_Column\" >Convert an Array to a Row or Column<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-combine-reshape-and-resize-arrays-in-excel\/#Convert_a_Row_or_Column_to_an_Array\" >Convert a Row or Column to an Array<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-combine-reshape-and-resize-arrays-in-excel\/#Resize_Arrays\" >Resize Arrays<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-combine-reshape-and-resize-arrays-in-excel\/#Take_or_Drop_Rows_or_Columns\" >Take or Drop Rows or Columns<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-combine-reshape-and-resize-arrays-in-excel\/#Keep_a_Certain_Number_of_Rows_or_Columns\" >Keep a Certain Number of Rows or Columns<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-combine-reshape-and-resize-arrays-in-excel\/#Expand_an_Array_to_Specific_Dimensions\" >Expand an Array to Specific Dimensions<\/a><\/li><\/ul><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Combine_Reshape_and_Resize_Arrays_in_Excel%E2%80%9D\"><\/span>&#8220;How to Combine, Reshape, and Resize Arrays in Excel&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-757715\" data-pagespeed-no-defer=\"\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel logo on a green background\" width=\"1200\" height=\"675\"\/><\/p>\n<p>Working with arrays, or adjacent cell ranges, in Microsoft Excel can be challenging at times. If you\u2019d like to combine, reshape, or resize an array, you can choose from a collection of functions that can cover many situations.<\/p>\n<blockquote class=\"admonishment_note\"><p><strong>Note:<\/strong> These 11 functions are new to Excel as of August 2022. They\u2019re rolling out to Excel users over time beginning with Office Insiders.<\/p><\/blockquote>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Combine_Arrays\"><\/span>Combine Arrays<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/769382\/how-to-combine-data-from-spreadsheets-in-microsoft-excel\/\">Combining data in a spreadsheet can be difficult. With the VSTACK and HSTACK functions, you can stack arrays vertically and horizontally.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Combine Data From Spreadsheets in Microsoft Excel<\/em><\/strong><\/p>\n<p>The syntax for each function is the same as <code>VSTACK(array1, array2,...)<\/code> and <code>HSTACK(array1, array2,...)<\/code> with only one required array and others optional.<\/p>\n<p>To combine the arrays in cells B2 through F3 and H2 through L3 vertically, use this formula for the VSTACK function:<\/p>\n<pre>=VSTACK(B2:F3,H2:L3)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-825533\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/08\/VSTACK-ExcelArrayFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"VSTACK function in Excel\" width=\"650\" height=\"189\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To combine those same arrays horizontally instead, use this formula for the HSTACK function:<\/p>\n<pre>=HSTACK(B2:F3,H2:L3)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-825532\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/08\/HSTACK-ExcelArrayFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"HSTACK function in Excel\" width=\"650\" height=\"157\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Reshape_Arrays\"><\/span>Reshape Arrays<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If it\u2019s not combining arrays that you want to do but reshaping them instead, there are four <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/765624\/basic-excel-functions-everybody-should-know\/\">functions you can use.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>12 Basic Excel Functions Everybody Should Know<\/em><\/strong><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Convert_an_Array_to_a_Row_or_Column\"><\/span>Convert an Array to a Row or Column<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>First, the TOROW and TOCOL functions let you shape the array as a row or a column. The syntax for each is <code>TOROW(array, ignore, by_column)<\/code> and <code>TOCOL(array, ignore, by_column)<\/code>.<\/p>\n<ul>\n<li><b>Ignore<\/b>: To ignore certain types of data, enter 1 for blanks, 2 for errors, or 3 for blanks and errors. The default is 0 to ignore no values.<\/li>\n<li><b>By_column<\/b>: Use this argument to scan the array by column using TRUE. If no argument is included, FALSE is the default, which scans the array by row. This determines how the values are ordered.<\/li>\n<\/ul>\n<p>To convert the array B2 through F3 to a row, use this formula with the TOROW function:<\/p>\n<pre>=TOROW(B2:F3)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-825534\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/08\/TOROW-ExcelArrayFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"TOROW function in Excel\" width=\"650\" height=\"154\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To convert that same array to a column instead, use the TOCOL function with this formula:<\/p>\n<pre>=TOCOL(B2:F3)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-825535\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/08\/TOCOL-ExcelArrayFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"TOCOL function in Excel\" width=\"515\" height=\"435\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Convert_a_Row_or_Column_to_an_Array\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"autotoc_anchor_3\">Convert a Row or Column to an Array<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To do the opposite of the above and convert a row or column to an array, you can use WRAPROWS and WRAPCOLS. The syntax for each is <code>WRAPROWS(reference, wrap_count, pad)<\/code> and <code>WRAPCOLS(reference, wrap_count, pad)<\/code> with the <code>reference<\/code> being a group of cells.<\/p>\n<ul>\n<li><b>Wrap_count<\/b>: The number of values for each row or column.<\/li>\n<li><b>Pad<\/b>: The value to display for the pad (empty cell).<\/li>\n<\/ul>\n<p>To convert the cells B2 through K2 to a two-dimensional array by wrapping rows, use the WRAPROWS function. With this formula, the cells are wrapped using three values per row with \u201cempty\u201d as the <code>pad<\/code>.<\/p>\n<pre>=WRAPROWS(B2:K2,3,\"empty\")<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-825537\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/08\/WRAPROWS-ExcelArrayFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"WRAPROWS function in Excel\" width=\"650\" height=\"187\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To convert the same cells into a two-dimensional array by wrapping columns, use the WRAPCOLS function. With this formula, the cells are wrapped using three values per column with \u201cempty\u201d as the <code>pad<\/code>.<\/p>\n<pre>=WRAPCOLS(B2:K2,3,\"empty\")<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-825536\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/08\/WRAPCOLS-ExcelArrayFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"WRAPCOLS function in Excel\" width=\"650\" height=\"173\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Resize_Arrays\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"autotoc_anchor_4\">Resize Arrays<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Maybe you want to adjust the size of an array by adding some data or dropping unnecessary cells. There are five <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/791838\/excel-functions-data-entry\/\">functions to help you do this depending on the result you want.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>13 Essential Excel Functions for Data Entry<\/em><\/strong><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Take_or_Drop_Rows_or_Columns\"><\/span>Take or Drop Rows or Columns<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>With the TAKE function, you keep the number of rows or columns you specify. With the DROP function, you do the opposite and remove the number of rows or columns you specify. You\u2019ll use positive numbers to take or drop from the start of the array and negative numbers to take or drop from the end.<\/p>\n<p>The syntax for each is <code>TAKE(array, rows, columns)<\/code> and <code>DROP(array, rows, columns)<\/code> where you need at least one of the second two arguments; <code>rows<\/code> or <code>columns<\/code>.<\/p>\n<p>To keep the first two rows in the array B2 through F5, use TAKE with the <code>rows<\/code> argument. Here\u2019s the formula:<\/p>\n<pre>=TAKE(B2:F5,2)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-825541\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/08\/TAKErows-ExcelArrayFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"TAKE function for rows\" width=\"540\" height=\"290\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To keep the first two columns in that same array, use the <code>columns<\/code> argument instead:<\/p>\n<pre>=TAKE(B2:F5,,2)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-825540\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/08\/TAKEcolumns-ExcelArrayFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"TAKE function for columns\" width=\"520\" height=\"340\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To remove the first two rows in the array B2 through F5, use DROP with the <code>rows<\/code> argument and this formula:<\/p>\n<pre>=DROP(B2:F5,2)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-825542\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/08\/DROProws-ExcelArrayFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"DROP function for rows\" width=\"540\" height=\"295\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To remove the first two columns in that same array, use the <code>columns<\/code> argument instead and this formula:<\/p>\n<pre>=DROP(B2:F5,,2)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-825543\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/08\/DROPcolumns-ExcelArrayFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"DROP function for columns\" width=\"520\" height=\"340\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Keep_a_Certain_Number_of_Rows_or_Columns\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"autotoc_anchor_6\">Keep a Certain Number of Rows or Columns<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To select the exact row and column numbers you want to keep from an array, you\u2019d use the CHOOSEROWS and CHOOSECOLS functions.<\/p>\n<p>The syntax for each is <code>CHOOSEROWS(array, row_num1, row_num2,...)<\/code> and <code>CHOOSECOLS(array, column_num1, column_num2,...)<\/code> where the first two arguments are required. You can add more row and column numbers if you like.<\/p>\n<p>To return rows 2 and 4 from the array B2 through F5, you\u2019d use the CHOOSEROWS function and this formula:<\/p>\n<pre>=CHOOSEROWS(B2:F5,2,4)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-825545\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/08\/CHOOSEROWS-ExcelArrayFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"CHOOSEROWS function in Excel\" width=\"545\" height=\"290\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To return columns 3 and 5 from the same array, you\u2019d use the CHOOSECOLS function with this formula:<\/p>\n<pre>=CHOOSECOLS(B2:F5,3,5)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-825544\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/08\/CHOOSECOLS-ExcelArrayFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"CHOOSECOLS function in Excel\" width=\"570\" height=\"340\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<blockquote class=\"admonishment_note\"><p><strong>Note:<\/strong> Remember to use the row or column numbers for the array, <i>not<\/i> for the sheet.<\/p><\/blockquote>\n<h3><span class=\"ez-toc-section\" id=\"Expand_an_Array_to_Specific_Dimensions\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"autotoc_anchor_7\">Expand an Array to Specific Dimensions<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Maybe you plan to add more data to your array, so you want to make it a specific size to <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/344407\/how-to-add-and-change-cell-borders-in-excel\/\">add a border or use\u00a0conditional formatting. With the EXPAND function, you enter the number of rows and columns your array should cover.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How To Add and Change Cell Borders In Excel<\/em><\/strong><\/p>\n<p>The syntax for the function is <code>EXPAND(array, rows, columns, pad)<\/code> where a missing <code>rows<\/code> or <code>columns<\/code> argument means those will not expand. Optionally, you can include the <code>pad<\/code> value for the empty cells.<\/p>\n<p>To expand the array B2 through F5 to cover 10 rows and 10 columns, you\u2019d use this formula:<\/p>\n<pre>=EXPAND(B2:F5,10,10)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-825547\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/08\/EXPAND-ExcelArrayFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"EXPAND function in Excel\" width=\"650\" height=\"328\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To expand that same array to the same dimensions and include the <code>pad<\/code> \u201cempty,\u201d use this formula:<\/p>\n<pre>=EXPAND(B2:F5,10,10,\"empty\")<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-825548\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/08\/EXPANDpad-ExcelArrayFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"EXPAND function with a pad value\" width=\"650\" height=\"328\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<blockquote class=\"admonishment_tip\"><p><strong>Tip:<\/strong> Although the <code>pad<\/code> argument is optional, you may prefer it over seeing an error as shown above.<\/p><\/blockquote>\n<p>These 11 functions give you more control than ever over your arrays in Microsoft Excel. Give them a try and see if they accomplish what you need.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Fix Common Formula Errors in Microsoft Excel<\/em><\/strong><\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/825518\/combine-reshape-resize-arrays-in-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Combine, Reshape, and Resize Arrays in Excel&#8221; Working with arrays, or adjacent cell ranges, in Microsoft Excel can be challenging at times. If you\u2019d like to combine, reshape, or resize an array, you can choose from a collection of functions that can cover many situations. Note: These 11 functions are new to Excel&#8230;<\/p>\n","protected":false},"author":1,"featured_media":495063,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-495062","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/495062","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=495062"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/495062\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/495063"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=495062"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=495062"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=495062"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}