How To Transpose Every N Rows of Data into Muliptle Columns in Excel
This post will guide you how to transpose data from rows to column with a formula in Excel. How do I transpose every N rows from one column to multiple columns in Excel.
Assuming that you have a list of data in range A1:A10 in column A, and you want to transpose every 2 rows from column A to Mulitple columns. for example, you want to transpose range A1:A2 to C1:D1, A3:A4 to C2:D2. How to do it. And you can user an formula based on the INDEX function, the Row function and the Column Function. Like as below:
=INDEX($A:$A,ROW(A1)*2-2+COLUMN(A1))
You need to type this formula into cell C1, and press Enter key on your keyboard, and then drag the AutoFill Handle to CEll D1.
Then you need to drag the AutoFill Handle in cell D1 down to other cells until value 0 is displayed in cells.
Related Functions
- Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])… - Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])…. - Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….
AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average simply for some complex situations. In this article, we will introduce you to calculate average of the last ...
Sometimes we may want to sum the largest N numbers or top N numbers in a range. In this article, we will show you the method of “SUM the Largest N Numbers” by a simple formula which consist of SUMPRODUCT ...
Sometimes we may want to sum the first smallest N numbers in a range in Excel. In this article, we will show you the method of “SUM the Smallest N Numbers” by a simple formula which consist of SUMPRODUCT and ...
Sometimes we may want to enter a sequence of days in excel for some purpose, and we can press Ctrl+; to insert current date into cell, and then drag the cell down to attach following days into other cells, then ...
In our daily work we may need to create a dynamical dropdown list and sort all values by alphabetical order. To create a dropdown list like this, we need to apply some built-in features like ‘Define Name’ and ‘Data Validation’ ...
Sometimes we may want to highlight every other row or every Nth row in a spreadsheet to make data in different rows looks more clearly. Though we can apply table styles to highlight table rows automatically, we can also highlight ...
When counting the number of times for objects appear in a list or a range, we usually record the duplicate value only once and ignore the redundant ones. We cannot apply formula with only one function to implement this in ...
This post will guide you how to create dynamic drop down list without blank cells in Microsoft Excel. In Excel, and you can use Data Validation feature to improve the efficiency of data entry in excel, and it also be ...
In previous article, I have shown you the method to split data from one long column to multiple columns by VBA and Index function. This time if we want to stack data from multiple columns to one column, how can ...
We enter a list of numbers or products and there are some duplicates in the list, if we want to just do count for the unique values and exclude the duplicates, how can we do? Now you can follow the ...
