Join Dennis Taylor for an in-depth discussion in this video Transposing row-column layouts into column-row layouts, part of Cleaning Up Your Excel 2013 Data .
In this worksheet called Transpose we see data about employees being laid out on the screen in a columnar fashion here. And if we are going to add more fields, more descriptive fields here in A5, A6, A7, etcetera, this layout might be just fine. But if we're going to add more names I think you can see that at some point maybe this is not going to be so efficient. What if we had double the number of names here? We're either going to have to zoom back, maybe to an uncomfortable level or figure out a better way to view this data.
Now what if we could imagine, for example, this data here in Column A, like this? And then the Building data right here in Column B, like this? Well, without destroying the original data let's create that layout by way of a feature called Transpose. One way to imagine this is after selecting the data, imagine a diagonal line from the upper left corner to the lower right and then rotating the data along that diagonal. Now that's not a vision that everybody picks up on right away but if we transpose this data without in any way destroying what we already have, a different view of the data might be a better choice, so let's transpose the data.
We first select the data and then copy it, either Control + C or right-click and copy, either way. Let's go to the upper left-hand corner of the receiving area and here we'll use Paste + Special. You can either right-click and go to Paste + Special or press Control + Alt + V, either way. This activates the Paste + Special dialogue box choose Transpose, OK and there's the data. I think in this case it's a clear winner, it's a better layout. Sure, we might add a few more fields to the right that shouldn't be a problem.
If we add more names this is going to be much more efficient than the layout that we see in up above in Rows 1 - 4. There certainly will be times when you do the reverse. If we took this data here and transposed it elsewhere, it would have the same layout and appearance as this data here. And there certainly could be times when you want to do this only with a single column or a single row. There's more data off to the right here, it's in a column, nothing wrong with that but on the other hand maybe you're about to create a list and it's going to be more efficient or it's based on something you've got on paper.
You want this list to be horizontal simply copy the data, how about Control + C I'll go to Column N this time right-click there and choose Paste + Special and one of the icons in here actually is transposed. There it is right there and you see the preview, click "OK". What we would also need to do in this case though, and we have to make some adjustments along the line, select the columns, double-click a boundary here to make sure we've got a best fit column layout. Whether that's truly better or not, that's your call but we can easily transpose data.
If it's in a column we can make it go into a row, a row into a column or in the case here as we saw earlier a column row layout into a row column layout. Easy to handle, always do this in a new area and I think it's always best too, to compare the two versions before you proceed.
- Moving or inserting rows and columns of data with a simple drag
- Transposing row-column layouts into column-row layouts
- Replacing data at the character level
- Dealing with special characters and wildcards during search and replace
- Converting dates with text functions
- Converting text data to values/numbers
- Checking and correcting spelling mistakes
- Splitting data into multiple columns via the Text to Columns feature
- Combining data from different columns via concatenation or Flash Fill