You can use the TRANSPOSE function with the HSTACK function in Excel to quickly convert a table of data into a vertical list.

For example, you can use the following formula to convert a table with three rows into a vertical list:

=TRANSPOSE(HSTACK(B3:E3, B4:E4, B5:E5))

The following example shows how to use this formula in practice.

Example: Convert a Table to List in Excel

Suppose we have the following dataset in Excel that displays the total sales made by some company during each quarter of three consecutive years:

Suppose we would like to convert this table into a vertical list.

We can type the following formula into cell A7 to do so:

=TRANSPOSE(HSTACK(B3:E3, B4:E4, B5:E5))

The following screenshot shows how to use this formula in practice:

We can see that the formula was able to successfully convert the table of values into a single list that shows the sales values for each quarter and year.

For example:

• The first value in the list shows the sales for Q1 2021.
• The second value in the list shows the sales for Q2 2021.
• The third value in the list shows the sales for Q3 2021.

And so on.

How This Formula Works

Recall the formula that we used to

=TRANSPOSE(HSTACK(B3:E3, B4:E4, B5:E5))

This formula works by first using the HSTACK function to horizontally stack the values in each row into a single row.

Next, we wrap the TRANSPOSE function around the HSTACK function to transpose the horizontal list of values into a vertical list of values.

Note: You can find the complete documentation for the HSTACK function in Excel .

The following tutorials explain how to perform other common operations in Excel: