Introduction
How do you convert vertical data into horizontal data in Excel? This was a question I was asked this week. A friend of mine was asked to convert an Excel spreadsheet that contained a list of classes and their students from one long column of text to rows of classes and their students.
What is TRANSPOSE()
TRANSPOSE() is a built-in function in Excel that converts a vertical range of cells to a horizontal range or vice versa.
This is the function that I would use to complete this task.
How to Use TRANSPOSE()
The best way to explain this is to use it together. Let’s take a list of classes and their students. In this sample, the classes are the column headers, and the rows are the students. We need to convert these vertical columns into horizontal rows with the class names first, followed by the list of students in the same row.
| Ocean Class | Jungle Class | Mountain Class | Desert Class | River Class |
|---|---|---|---|---|
| Haley | Ernest | Kristi | Michelle | Tyler |
| Kevin | Amanda | Tamara | Anthony | Julie |
| Tina | Courtney | Danielle | Tammy | Denise |
| Mark | Michael | Tony | Dennis | Jon |
| Gregory | Vanessa | Sheila | John | Raymond |
| Stephanie | Nancy | Monica | Guy | Lisa |
The simplest way to do this would be to use the TRANSPOSE()
- Choose an empty cell. I will use A10
- Type
=TRANSPOSE(A1:A7)
When you press return/enter on your keyboard, you will see that we now have our Ocean Class student list in a row.
How It Works
TRANSPOSE() has a set of brackets; we must select starting and ending cells within these brackets. In our example above, we clicked on cell A10. This is where we want to start our new row. Within the brackets, we typed A1:A7. A1 is our starting cell, The first entry in Ocean Class. A7 is the last student in that class, and we selected that as the endpoint.
If you look at the image, you can see that cell A10 has the same content as A1, and cell G10 is the last student in that class. This means it worked!
Convert Data Horizontal to Vertical
What if your list of data is the other way around? Is there a way to convert a horizontal list to a vertical list? Yes, TRANSPOSE() can convert the data both ways. And you do it in the same way by selecting the starting cell and the ending cell. Let’s convert our A10 Ocean Class List back to vertical.
- Select a starting cell A12
- Type
=TRANSPOSE(A10:G10)
When you press return/enter, your list should be in a vertical line from A12 to A18.
=TRANSPOSE(A10:G10)Convert Multiple Rows From Vertical to Horizontal
We have other classes in our table that we need to convert. We could write our formula for each column like this.
| Formulas | Classes |
|---|---|
=TRANSPOSE(A1:A7) | Ocean Class |
=TRANSPOSE(B1:B7) | Jungle Class |
=TRANSPOSE(C1:C7) | Mountain Class |
=TRANSPOSE(D1:D7) | Desert Class |
=TRANSPOSE(E1:E7) | River Class |
A better way to do this for our table would be to add the range of cells we want to convert to our TRANSPOSE() function like this.
- Select cell A12
- Type
=TRANSPOSE(A1:E7) - This will convert our whole table
Conclusion
As you can see from the example above, the TRANSPOSE() function can save you a lot of time when you need to change the orientation of a list. You no longer need to copy and paste everything into a new table.
You can use TRANSPOSE() If you have longer data lists or need to break up the list into smaller rows/columns.
Disclaimer: The data used in this guide is purely fictional and created for illustrative purposes only.
Leave a Reply