HOW TO TRANSPOSE DATA IN EXCEL
What is Transposing data? • Transposing data means moving the row data to a column and a column data to a row.
• It is very useful for data analysis. • At times, we pull data from various files with different formats for analysis and report preparation. • In such circumstances, we may have to transpose some data from one file to the other as all files may not be in the required order. Email: Support@skillfinlearning.com
Mobile: 9650010925
Data can be transposed in multiple ways
Static method using Paste special
Dynamic method using the Transpose function
Dynamic method using a simple trick
Email: Support@skillfinlearning.com
Mobile: 9650010925
Static method using Paste special (1/3)
• In this method, the data is transposed using Paste Special Transpose feature in Excel.
• We have a list of Companies with Revenues, Profits, and margins in a horizontal format. • We want to transpose this data in a vertical format.
• Here the transposed data is static. That means, it does not change with the change in the source data.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Static method using Paste special (2/3) • • •
To transpose this data, select the data and Press Control + C to copy it. Then go to the cell where you want to paste this data and right click your mouse. You get a list of options. Select Paste Special.
Email: Support@skillfinlearning.com
Mobile: 9650010925
•
Once you select Paste Special, You get the following screen
Static method using Paste special (3/3) •
•
Select the option Values and Transpose.
Then Click OK
Email: Support@skillfinlearning.com
Mobile: 9650010925
•
You get the following table.
• •
The data is pasted in a vertical table format. You can now format the data and proceed with the analysis required.
Dynamic method using the Transpose function (1/3)
• There is an inbuilt Transpose function in Excel. • This function can transpose data dynamically, meaning that whenever there is a change in the original table, the transposed data gets updated automatically.
Email: Support@skillfinlearning.com
• To Transpose the data, select the range where you want to transpose the data. • In our example, we have 4 rows and 10 columns in our table.
Mobile: 9650010925
Dynamic method using the Transpose function (2/3) •
•
To transpose the data, select the range with 4 columns and 10 rows. Then type the transpose function. = Transpose(B5:K8)
•
Then Press Enter
Email: Support@skillfinlearning.com
Mobile: 9650010925
• • •
In Office 365 version of Microsoft excel, you get the transposed data (as shown below). The formula is in cell B11. The data has spilled over to the other cells. There is no formula in those cells. In case you want to change the formula, it can only be done by going back to the same cell (B11).
Dynamic method using the Transpose function (3/3) • In earlier versions of the excel, you get an Error when you press enter, as the transpose function is an array function. • Excel can’t hold multiple values in a single cell. • So, to get the transposed data you should type, = Transpose(B5:K8)
• Then Press Control & Shift & Enter.
•
• When you press these key’s you get the data in transposed format. Email: Support@skillfinlearning.com
Mobile: 9650010925
This is a dynamic table. When we make any change to the original data source. This table gets updated automatically.
Dynamic method using a simple trick • There is a third trick to transpose the data. This is by linking cells to the original table (as shown below). • The problem with this method is that this trick works for small data sets, but not for large tables. • We can use a simple trick to link the full table (whether small or large) in just a few steps.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Dynamic method using a simple trick •
•
Link a first few cells to the table. Then replace the equal to sign with some other alphabet. We are using “sl” instead of = See below.
•
Select the edited cells, place the mouse at the bottom right of the selection and drag horizontally. Email: Support@skillfinlearning.com
Mobile: 9650010925
Excel automatically fills the cell numbers slB5, slC5 etc.
Dynamic method using a simple trick •
Now select the cells in the range (G4:P7).
•
Press Control and H for Find and replace. •
•
Then in Find with type sl and in Replace with type the equal to sign (=).
•
Then Press Replace All
Email: Support@skillfinlearning.com
Mobile: 9650010925
Your data gets transposed horizontally.
Want to learn more?
Explore our “Data Analysts Skills Training” (DAST) course Go from beginner to an Expert in Data analytics Skills in Excel. Join this program to build your Excel analytical skill quickly through hands on project Skills you will master: Data analytics functionalities in Excel Data visualization through charts Logical analysis driven decision-making Data cleaning and improvisation methods Pivot Table analysis Professionally format datasheets Excel tips and tricks using shortcuts Excel skills for business purposes Apply data analysis on real life datasets
Coaching includes: Annual (1 year) Subscription 100+ Live Interactive Sessions Unlimited Access to Video Content Weekly Mentorship Assistance Individual Assignments Evaluation Access on Mobile and Tablet also Certificate of Completion For more details please visit Data Analyst Skills Training (DAST)
Email: Support@skillfinlearning.com
Mobile: 9650010925
Our Training approach ensures You Learn Effectively in Less Time
Email: Support@skillfinlearning.com
Mobile: 9650010925
THANK YOU
Email: Support@skillfinlearning.com
Mobile: 9650010925