But the columns will be flexible.Įxcel Dynamic Array Formula in Cell E3 (Order И) : =XLOOKUP(TRANSPOSE(SEQUENCE(ROUNDUP(ROWS(C4:C15)/4,0),4,4)),ROW($C$4:$C$15),C4:C15," ",0,1)Ĭan you explain what are the changes here in comparison to the Order 1 formula? In this method, when you add more data in the range, we will have fixed rows in the result. Order 2 – Move Data from One Column into Multiple Columns in Excel 365 The formula searches the lookup_values (step 1) in lookup_array (step 2) and returns corresponding values from the return_array (step 3). Final Step (XLOOKUP Formula to Unstack Data) Regarding the other three optional arguments, if_not_found, match_mode, and search_mode, use them as " ", 0, and 1, respectively. =ROW($C$4:$C$15)įor explanation purposes, I have entered this formula in cell B4. Here is the Excel Sequence dynamic array formula and result based on it. Start – 4 (the position of the first record in the range). We will use this as the ‘rows’ in a Sequence function to return a matrix. Syntax: SEQUENCE(rows,, , )įirst, let’s find the total number of rows in the range and divide that number by 3 (number of columns in the unstacked data). It’s a SEQUENCE formula, which acts as the lookup_value in XLOOKUP in Excel. It is an important step in moving data from one column into multiple, here 3, columns in Excel 365. Syntax: XLOOKUP(lookup_value,lookup_array,return_array,)īefore further proceeding with this Excel tutorial, you must have a clear idea of how many columns you want in the unstacked data. Let’s write the XLOOKUP formula that moves data from one column into multiple columns from scratch. In this method, when you add more data in the range, we will have fixed columns in the result. Order 1 – Move Data from One Column into Multiple Columns in Excel 365 We are going to unstack the data in Excel now as per Order # 1 above. Please refer to the Excel screenshot below. The new range will be C4:C15 since C3 contains a header. Then click OK, and your data range has been converted to the table format.Uncheck “My table has headers” as our sample data doesn’t have a header.Click Insert -> Table to open the Create Table dialog box.If you add more records below the range, then follow this. If you want to unstack the data one time, then you can skip the below four steps. Order 2 – Move Data from One Column into Multiple Columns in Excel 365.Final Step (XLOOKUP Formula to Unstack Data).Order 1 – Move Data from One Column into Multiple Columns in Excel 365.Then right-click where you want to to paste the copied cells, go to the Paste Special submenu and click the Keep Source Column Widths button or press W on your keyboard. If you don’t want to copy entire columns, select just the cells you want to copy, right-click on your selection and choose Copy. Everything in the selected columns and all formatting, including columns widths will be copied to the new location. Next, right-click on the column heading where you want to place the copied cells and choose Paste to replace what’s in the destination column or choose Insert Copied Cells to move the selected column to the right and insert the column(s) you just copied. Click on a column heading to select a column, or click and drag to select multiple columns, then right-click on the selected column(s) and choose Copy. When you copy and paste cells on a spreadsheet, do you get tired of having to resize the columns where you pasted the cells? There are two good ways to copy the column widths along with your data.Ĭopying entire columns is the fastest and easiest way to copy contents and formatting at the same time.