Transposing data in Excel is a useful way to switch the orientation of data in an array. This means the data in vertical portions are shifted to a horizontal format without disrupting the meaning. Therefore, in Excel, there is a transpose feature available that allows the users to rotate data from rows to columns or vice versa.
So, here in this article, we are showing you different ways how to transpose data in Excel by using easy steps.
If you intend in trying to transpose data in Excel manually, this would consume a massive time and in reality, all this requires is a few simple tricks.
How to Transpose Data in Excel?
If you are wondering on transpose row to column in excel, three major steps would help your course. These are,
Method 1: Using Transpose Excel Function
One can transpose cells using the Transpose function available in the MS Excel accordingly. Here are the steps to look forward,
- Choose the range of cells that you intend to transpose
- Since Transpose is an array-based formula, it’s important to select the exact number of cells
- Assuming you have chosen 4×5 as per your table range, where 4 rows and 5 columns are highlighted. In the next step, you should choose 5×4 for setting the transposed data.
- Type the formula “=TRANSPOSE(A1:F4)”
- Press Ctrl+Shift+Enter to execute the formula
- This is done to ensure that the result would be an array of data instead of a sole cell
- Wait for the data to be transposed next up
Method 2 – Using Paste Special Feature
This feature allows you to make sure the format of the content is under your control. Thus, this feature can be used in copying the real content and even past the transposed one. Let’s check how it is done,
- First, choose the range you are looking to transpose.
- Copy the data range
- While doing so, traverse from the top-left of the target range of the same sheet
- Press Ctrl+Alt+V to access the same Paste Special option
- Press the Paste Link option and this ensures the cell references are copied as a single formula
- Choose the range you have pasted in the target range
- Hold up Ctrl+H together to trigger the Find and Replace dialogue box
- Enter the following without quotes “=” in the Find portion
- Enter “AT=” without quotes on the Replace with option and tap on the Replace all option
- The formula gets converted into texts since you have to replace “=” with “AT=” and this ensures everything the conversion is complete from regular formula to ideal texts
Method 3 – Using Power Query
Using this method, it is possible to get repeated data from a similar source and use the technique to transpose. Here is how you transpose data in Excel, using Power Query,
- Firstly we assume you open data inside an excel table. This can help you import the data into a power query and allow the process of transposing.
- Choose the range you want to transpose
- Go to the Data tab option
- Tap on Get & Transform Data option
- Under the “From Table/Range”, click to open the Power Query Editor
- Within the editor, visit the Transformation option and choose the Table Section. Follow this up with First Row as Headers. Follow this up with Use Headers as the First Row option.
- Tap on the Transpose option and can be found in the same section
- This would transpose the selected Data range
- Choose the option “Use First Row as Headers”
- This ensures to make the first row of transposed data is a header
- Tap on the File tab and choose Close & Load
- Check if the entire process of transposing is successful or not
You now know how to transpose data in Excel using three useful steps. Transposing using these tricks can save plenty of time since manual transposing is extremely tiring and time-consuming.
We hope the article is useful for you and help you in solving your queries regarding Excle transpose function.
Besides this, if you end up losing your Excel file data either due to corruption or damage issue. In that case, you can go with the recommended solution Excel repair tool. As it is the best tool to repair corrupt Excel file data.
This tool very efficiently repair corrupt Excel xlsx/xls files and recover objects, formulas, including shared formula and array-entered formula.