Transpose
By Richard Rost
4 years ago
Transpose Row and Column Data in Microsoft Excel
If you have ever wanted to swap your data rows and columns in Microsoft Excel, this video is for you. I'm going to show you two different ways to transpose your data. We can use the TRANSPOSE function, or Paste Special method. This basically flips the horizontal and vertical. Kinda like putting your TV on it's side. Kinda. Sorta. We'll also discuss why this is important if you want to get your data into a database like Microsoft Access.
Recommended Course
Links
 Keywords
excel 2021, excel 2016, excel 2019, excel 365, microsoft excel, ms excel, ms excel tutorial, #msexcel, #microsoftexcel, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, what is transpose in microsoft excel, TRANSPOSE function, What does it mean to transpose data, ctrl shift enter, rotate data, horizontal, vertical
Subscribe to Transpose
Get notifications when this page is updated
Intro In this video, I will show you how to transpose data in Microsoft Excel using two methods: the static copy and paste special technique, and the dynamic TRANSPOSE function. You will learn when and why it is useful to flip rows and columns, especially for organizing budget sheets or preparing your data for import into Microsoft Access. I will also discuss the limitations of each approach and offer insights on best practices for database compatibility with transposed data.Transcript Welcome to another fast tips video brought to you by ExcelLearningZone.com. I am your instructor Richard Rost.
In today's video, I am going to show you how to transpose data in Microsoft Excel between rows and columns.
Now, I know some of you have probably seen sheets like this before. You get a sheet from someone else because you would not build it this way, obviously, but someone else sends you a sheet. They have the years, for example, here going across in the columns and the budget categories going down like this when you really want to have it like this.
You want to have the columns be the budget categories and the rows be the years. We will talk about why in a little bit, but first, let me show you how to actually transpose these two so you can flip back and forth.
All right, so here is my sheet someone else sent to me. No, actually, I set this one up, but I set it up wrong on purpose. I have the years going across the columns across the top here, and I have my budget categories over here on the left. What I want to do now is flip these, transpose them. This rotates the horizontal and the vertical. It is kind of like when you take your TV and just put it on the side - portrait and landscape.
So, how do we do that? There are two different ways we can do it. I have seen some other tricks online and there are different ways, but basically, there are two ways to do this. One is a static method where we just copy and paste it, and it is a copy of the data. The other one uses a function. The benefit of the function is if the original data changes, if you want to keep this one this way, then the new data will change as well. Let me show you both methods.
All right, so the first method is to basically copy and paste. We are going to select this whole area, copy it (Control+C), come over where you want it to go, right click, choose Paste Special, Transpose right down there, and hit OK. Boom. It is that easy.
You can add some colors and do whatever you want, but you can see here I have got the years going down the rows now, and these categories over here are the columns. That is the static method. Now, these are actual numbers here. If this value changes to five, this one does not change because that is just a copy.
Let me undo that (Control+Z, Control+Z). All right, let me show you the other way to do it now.
This way involves a function. I can come over here and I can say =TRANSPOSE. Open parenthesis, and now it wants an array. The array is basically your area over here that you want to transpose. Select it like that with a mouse or type it in if you want, and then press Enter and boom, there it goes. The benefit of this method is that if you want to keep this alive, you want to keep working with data like this, you want to type in 100 over here - watch that, see, it changed over here now. That is all right there.
You can still add some formatting and make this different and whatever you want to do with it. What I usually do in this case is I will take this and put it on a different sheet. Let me cut that out (Control+X), I will go over here to Sheet2, and I will paste it in here. This is still transposed. If you click in here, you can see up here it is still transposed.
You do not want to come in here and start typing because you will break stuff. Undo. We will leave this guy alone and come over here and change stuff over here.
Keep in mind, if you are using an older version of Excel, you cannot just do what I just did. This started working in Excel 2019, I think. You had to press Control+Shift+Enter and do a bunch of weird stuff if you have an older version. This is good for 2019, 2021, or if you have a 365 subscription. If you do not have a subscription, get a subscription. It is a low monthly fee and you just get the updates and everything is new and fresh.
So, that is how you transpose stuff. Now, the big question is why would you want to do this? What does it matter? Well, aside from aesthetics and the way it looks, the big difference is if you want to put this in a database.
As most of you know, I do Excel, but I am really the Microsoft Access guy. Access is my thing. I am all about Access databases and this does not import well into a database table.
These are your records, and these are your fields. You do not want a field called 2020, 2021. No. When this imports into the database, no, that is just not right. I have seen people who have sent me databases where they had sale summary fields, January, February, March, going across. Do not do that. This is much, much better and much easier to read when you import this into Access because now at least you have a record that represents 2020, a record that represents 2021, and you can have these as fields.
These can be budget fields in your table, although there is a better way, and I will show you how to set that up in Microsoft Access. For those of you who are Access nerds like me, look for my transpose data video for Access Learning Zone. It is a TechHelp video. I will put a link down below you can click on if you want to watch this one. We will import the file right in as a table.
Here is your year and each category goes across like this. But this is not the best way to do it because now you are limited to these. If you want to add a new category you have to change the table design. We try to avoid that, so the categories should themselves be in a category table, so the user can quickly and easily add categories.
Let me take this and transpose this with some query work into this budget table right here. See, this is properly normalized now. Now we have got each category ID, which represents this guy, then the year, and then the amount. Now we can put all this together into the budget form, which is this guy. This is how the data should look. This is properly normalized in the database the way it should be.
If we want to get back to the summary information that we had before, that is what crosstab queries are for. See, we have come full circle now. It looks the way it originally looked, but at least the data is saved in the database in the right way.
In the extended cut for members, we will do some things like this - we will add little filter boxes here. So you see, show me all 2022, show me just the electric, you want to add the next year on here, click add next year. Are you sure you want to add 2023? And now you can just fill in your amounts. That will be covered in the extended cut. The first part is free so check it out. When I am in TechHelp videos, transpose data, I will put a link down below that you can click on.
Let me switch hats now, back to Excel. I hope you learned something today. If you want to learn more about transpose in Microsoft Excel, I cover it in my Excel Expert Level 8 class, which you will find on my website. I will put a link down below that you can click on.
Of course, if you have any questions, comments, thoughts, or ideas, feel free to post them in the comments section down below. I hope you learned something today and we will see you next time.
That is it. Want to learn more Excel? Be sure to like this video and subscribe to my channel. Stop by my website to watch my free Excel Level 1 course. It is over 90 minutes long and it covers all the basics.
If you want me to post more Excel videos, I need to hear from you. About 90 percent of what I do is Microsoft Access, but I am also a published Excel author and I love Excel.
If you want to see me post more free Excel videos, post a comment below and let me know. Say hey, I want more Excel.Quiz Q1. What does it mean to transpose data in Microsoft Excel?
A. To rotate data by switching rows and columns
B. To convert numbers into text
C. To sort data alphabetically
D. To summarize data with formulas
Q2. Why might you want to transpose data from columns representing years and rows representing categories?
A. So it imports more easily into a database
B. To make it look more colorful
C. To combine duplicate values
D. So Excel will automatically send it in emails
Q3. What is the static method for transposing data in Excel?
A. Using copy, Paste Special, and then Transpose
B. Typing each value by hand in the new arrangement
C. Applying a custom view
D. Using the SUM function
Q4. What is the main limitation of the static transpose method?
A. The new area does not update if the original data changes
B. You cannot format cells afterward
C. It increases the file size greatly
D. It only works for numbers, not text
Q5. Which function can be used to dynamically transpose data in Excel?
A. =TRANSPOSE()
B. =SWITCH()
C. =REVERSE()
D. =ARRAY()
Q6. What is the benefit of using the TRANSPOSE function over the static method?
A. The transposed data updates if the original data changes
B. The formula works in all versions of Excel
C. It converts currencies automatically
D. It locks the sheet for editing
Q7. In which versions of Excel does the dynamic transpose using just Enter work?
A. Excel 2019, Excel 2021, and Excel 365
B. Excel 2013 only
C. Excel 2007 and earlier
D. Only in Mac versions
Q8. What is a potential issue when importing data structured with years as fields into a database?
A. It is not normalized and harder to expand or query
B. The data will lose formatting
C. The import will always fail
D. It is much slower to type in Excel
Q9. What is database normalization in this context?
A. Organizing data so each record represents an entity and each field an attribute
B. Copying data twice for safety
C. Putting all categories in the column headings
D. Sorting all records alphabetically
Q10. What feature in a database allows you to get summary views similar to the original cross-tabbed format?
A. Crosstab queries
B. Filter buttons
C. Conditional formatting
D. Validation rules
Q11. According to the video, if you want to add categories easily in a database, how should you structure your tables?
A. Store categories in a separate category table
B. Add each new category as a new field in the main table
C. Limit to only three categories
D. Do not use tables; use named ranges instead
Q12. What risk do you run if you type directly into a cell that contains a TRANSPOSE formula?
A. You will break the formula
B. The cell will turn red
C. Excel will create a new worksheet
D. It will automatically create a chart
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Excel Learning Zone focuses on how to transpose data in Microsoft Excel, which means switching data between rows and columns. Many times you receive a spreadsheet from someone else, and the data is organized with years across the top as columns and budget categories listed down the side as rows. Frequently, however, you may want the opposite layout, with categories as columns and years as rows. There are a few reasons for preferring this structure, and I will explain why a little later. First, let me walk you through the two main techniques for transposing data in Excel: a static copy-paste method and a dynamic method using the TRANSPOSE function.
Suppose you have a worksheet with years going across the top as columns and budget categories down the left as rows. This arrangement is the reverse of what you want, so you need to switch, or transpose, those axes. Transposing essentially rotates your data layout the same way you might rotate your TV between portrait and landscape orientation.
There are two primary ways to accomplish this in Excel. The first option is a static method using copy and paste. To do this, select the entire range you want to transpose, copy it, and then go to the location where you want the new layout to appear. Use Paste Special, select Transpose, and confirm. Instantly, your data flips so that the years now run down as rows and categories stretch across as columns. This transposed data is static, meaning it is a snapshot of your original data. If you make changes to the original table, the transposed copy will not update. For example, if you change a value in the original sheet, this change will not be reflected in your new transposed table.
If you prefer a solution where the transposed data updates automatically as the source data changes, you should use the TRANSPOSE function. To do this, enter the TRANSPOSE function where you want your data to appear and select the range you wish to transpose as the function's argument. Hitting Enter will generate a transposed version of your data that stays linked to the original. Any updates to your original table will instantly update here as well. You can move this result to another worksheet if you wish, and it will continue to update as long as you do not try to overwrite individual cells within the resulting array.
Be aware that the TRANSPOSE function with this behavior works best in newer versions of Excel, starting with Excel 2019, Excel 2021, and Excel 365. If you are using an older version of Excel, you might have to use Control+Shift+Enter to create an array formula for the TRANSPOSE function. I recommend using one of the more recent Excel versions or subscribing to Microsoft 365 to have access to all the latest features.
Now, you might wonder why it is important to transpose data in the first place. Aside from simply making the data easier to read, there are practical considerations, especially if you plan to use the data in a database. I mainly work with Microsoft Access, and when you import row and column data into Access, the ideal structure is to have each record (each row) represent a year, with categories as fields (columns). You do not want each year to be a separate field, as it is not a normalized or flexible structure. This layout does not import effectively into Access. Instead, it is best to have one record per year and categories as fields, or better yet, to store categories in their own table. This approach makes it easy for users to add new categories without needing to redesign the table.
With some query work, you can transpose data out of a non-normalized format and into a properly structured budget table. This normalized arrangement means each record contains a category, a year, and the corresponding amount, allowing for greater flexibility and easier data management. If you ever want to view summary information in a format similar to the original data, you can use crosstab queries in Access to return that summary layout, but the data in your database remains organized correctly.
In the extended cut for members, I demonstrate additional features like adding filter boxes to show all values for a given year or category and enabling users to add a new year interactively to the dataset. These extras are covered in detail for those who join as members. If you are interested in seeing how to import and normalize this data in Microsoft Access, you can find my dedicated TechHelp video on this topic at Access Learning Zone.
Switching focus back to Excel, I also cover working more extensively with the TRANSPOSE function in my Excel Expert Level 8 class, available on my website. There you will find the course link as well as more information about using transposed and normalized data within Excel.
If you have questions or feedback, please leave a comment. If you want to encourage more Excel content, let me know in the comments section as well, since the majority of my work covers Microsoft Access but I enjoy sharing Excel tips too.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Transposing data between rows and columns in Excel
Copy and paste method for static transposing
Using Paste Special Transpose feature
Limitations of static transposed data
Transposing data dynamically with the TRANSPOSE function
Behavior of dynamic transposed data when source updates
Placing transposed data on a different worksheet
Compatibility of TRANSPOSE in different Excel versions
Importance of transposed data structure for database import
Impacts on importing Excel data into Microsoft Access
Problems with non-normalized data structures
Normalization of budget category data in databases
Benefits of properly structured data for reporting |
