How To Turn A Table Into A Column Using Formulas

This post will explore how you can transform a table of values into a single column using the INDEX function.

Get your copy of the example workbook with the above link.

There are many ways you could transform a table into a single column but this post will 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.

Here you have a table with 3 columns and 4 rows.

If you wanted to transform this into a single column of data you could start in row 1 column 1 and move down the rows until you reach the last row then move onto row 1 column 2 and start the pattern again until you 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)

The above formula will produce a single column based on the top to bottom then right pattern.

Another approach could be to start in row 1 column 1 and move across the columns until you reach the last column then move down to row 2 column 1 and start the pattern again until you 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)

The above formula will produce a single column based on the left to right then down pattern.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Subscribe

Related Posts

Comments

0 Comments

Get the Latest Microsoft Excel Tips

Follow Us

Follow us on social media to stay up to date with the latest in Microsoft Excel!