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 convert the table into a vertical list:
=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 here.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Count Number of Occurrences in Excel
How to Count Frequency of Text in Excel
How to Calculate Relative Frequency in Excel
