Step-001-How-To-Turn-A-Table-Into-A-Column-With-A-Formula How To Turn A Table Into A Column Using Formulas

Get The Completed Workbook

In this post we’ll explore how you can transform a table of values into a single column using the INDEX function. There are many ways you could transform a table into a single column but in this post we’ll explore the two ways that make the most sense.

  1. Going down the rows first then across the columns in your table.
  2. Going across the columns first then down the rows in your table.

 

Step-002-How-To-Turn-A-Table-Into-A-Column-With-A-Formula How To Turn A Table Into A Column Using Formulas

 

Here we have a table with 3 columns and 4 rows. If we wanted to transform this into a single column of data we could start in row 1 column 1 and move down the rows until we reach the last row then move onto row 1 column 2 and start the pattern again until we cover the whole table like in the picture above.

 

=INDEX($B$3:$D$6,MOD(ROW()-ROW($F$2)-1,ROWS($B$3:$D$6))+1,INT((ROW()-ROW($F$2)-1)/ROWS($B$3:$D$6))+1)

 

Step-003-How-To-Turn-A-Table-Into-A-Column-With-A-Formula How To Turn A Table Into A Column Using Formulas

 

Another approach could be to start in row 1 column 1 and move across the columns until we reach the last column then move down to row 2 column 1 and start the pattern again until we cover the whole table like in the picture above.

 

=INDEX($B$3:$D$6,INT((ROW()-ROW($H$2)-1)/COLUMNS($B$3:$D$6))+1,MOD(ROW()-ROW($H$2)-1,COLUMNS($B$3:$D$6))+1)