# 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.

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.

### 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

Subscribe for awesome Microsoft Excel videos 😃

## Related Posts

#### 8 Ways to Apply a Formula to an Entire Column in Microsoft Excel

Are you wondering how to apply a formula to an entire column in Excel? Read...

#### 5 Ways to Lock and Unlock Formulas in Microsoft Excel

Do you want to lock the formulas in your Excel sheet to prevent other users...

#### 9 Ways to Show Formulas in Microsoft Excel

This post is going to show you all the different ways you can show the...