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])….