How To Turn A Table Into A Column Using Formulas

2017-02-21

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.

 

 

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)

 

 

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)

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

Comments

0 Comments

Get The Latest News

Follow Us

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

Pin It on Pinterest

Share This