Using Excel, you can enhance the appearance of your worksheet with a wide range of formatting methods.
This article describes different methods for transposing Data in Excel.
What is Transpose in Excel?
Converting vertical datasets to horizontal ranges or vice versa, helps us sort unformatted data correctly.
Using Transpose, you can reorganize an Excel worksheet with columns into rows when you have data in columns.
Transpose Data in Excel Using Paste Special
Excel Paste Special has multiple formatting options. Transpose is one of those formatting options.
Here in this article, we are using the below dataset and we transpose this dataset in multiple ways or methods.
In this dataset, there are 5 rows and 5 columns.
And we will convert all the rows data into columns and columns data into rows.
We are well aware of Paste Special method in Excel.
So here we will TRANSPOSE our dataset from this paste special formatting option.
The very first step is to copy the dataset which we want to transpose.
Step 1– Select all the datasets.
Step 2 – Press ‘CTRL+C’, the keyboard shortcut command to copy data.
Step 3 – Go to the destination area, where you want to paste it as transpose. Suppose that you want to transpose this dataset at cell “G1”.
Step 4 – Go to cell “G1”, and press the ‘Right-Click’ button on the mouse.
Step 5 – Go to the Paste Special, from the listed options.
Step 6 – And click on it.
Once you click on this option, you will get the Paste Special dialogue box, see the image below:-
This paste special dialogue box contains multiple formatting options.
And one is a ‘Transpose’ option at the bottom (circled) with the check box.
Mark this check box as tick-on and hit the “Ok’ button below.
Once you hit the ‘Ok’ button, you will get your copied dataset converted into the transpose format.
This means your vertical dataset has been converted into horizontal and horizontal data vertically.
See all your dataset is converted into transpose format.
This is our very first method to transpose the Excel dataset.
Using Paste Link Option with Find & Replace Method
In this method, we will use the Find and Replace method to transpose the dataset.
Select all the datasets and copy them by pressing CTRL+C.
And go to the destination area, where you want to transpose it.
In the destination area, right-click from the mouse and again select the Paste Special option from the list.
Click on the ‘Paste Special’ option from the listed options and you will get the Paste Special dialogue box as shown in the below image:-
See that there is a ‘Paste Link’ button (circled) on the left side of the Paste Special dialogue box.
Click on this button.
Once you click on this button, you will get your dataset in the destination area.
See the image below:-
As you can see from the above image, both the dataset are the same.
Which means the data is not transposed.
But the link to the dataset is copied in the destination area.
See that every cell has copied the link of our original dataset.
Cell ‘B9’ is showing a link of ‘B2’ and ‘C9’ is showing a link of ‘C2’ and so on.
You can see in the formula bar, that there is a cell reference of the current cell.
You can check all the cell references by using the keyboard shortcut ‘CTRL+~’ altogether.
See the cell references image below:-
See the image above, the second dataset is containing all the cell addresses of the above dataset.
Again press the ‘CTRL+~’ keyboard shortcut to put data in the normal.
Now to transpose this dataset, we will use the Find and Replace option here.
See the second image data, which we have copied from the above dataset.
Every cell has a (=) sign before the cell address.
Now if we want to convert the original dataset as transpose, then we have to use the following method.
Here we will use the Find and Replace feature, we will replace our ‘=’ sign with the unique combination of characters (!@).
You can use any such unique combination of characters.
To open the ‘Find and Replace’ dialogue box, by using the keyboard shortcut command (CTRL + H) or go Home tab –> Editing group click on the Find & Select the drop-down and click on the ‘Replace’ option from the list.
Once you click on the Replace button, you will get the Find and Replace dialogue box.
Use this dialogue box to change our (=) sing with (!@) unique character, see in the below image.
Or you can use the following way to enter the details below method.
Find What – (=)
Replace with – (!@)
See the image above.
And press the Replace All button below.
Once you will press the Replace All button, you will get the below format.
See that every (=) sign is replaced with our unique combination character (!@).
Now again select this entire dataset and copy it.
Again go to the destination cell, where you want to transpose this dataset.
Use the Right-Click button from the mouse and click the Paste Special option from the listed options.
Click on the Transpose option at the bottom.
And click Ok, and you will get your copied dataset transposed.
See the image below:-
This dataset belongs to our dataset but looks different.
To convert this to our original form, we will use again Find and Replace option here.
Again select the entire dataset, and press the keyboard shortcut CTRL+ H to open the Find and Replace dialogue box again.
And this time it will replace our unique character with the (=) sign, as you can see in the below image.
And press the Replace All button from the bottom.
Once you press the Replace All button, you will get your dataset in the following way.
See that this is our original dataset which we have transposed with the Paste Link method.
Our rows data is converted to columns and columns data into rows.
So this is another way to transpose our dataset.
Using Excel TRANSPOSE Function
In this method, we will use Excel TRANSPOSE Faction. This is an array function and can produce multiple results at one time.
Here, we must know the exact row number and column number of our dataset.
We need to check how many rows and columns our dataset has.
Transpose converts our rows data into columns and columns data into rows.
So if we have 5 rows and 4 columns in a dataset then we need to select 5 columns and 4 rows in our destination area to transpose it.
Here in our dataset, we have 5 rows and 5 columns, so we will select 5 rows and 5 columns at one time.
See the image below:-
Now in the cell which is highlighted in the selected area.
Here we will apply the Excel TRANSPOSE Function, which you can see in the image below:-
Type in the highlighted cell ‘=TRANSPOSE (select the entire range which to transpose).
See below how we enter our function here.
Make sure that you will not press just Enter button only here.
This is an array function and you need to press CTRL+SHIFT+ENTER altogether to get the result.
See below what happens after pressing CTRL+SHIFT+ENTER.
Our rows data is converted into columns and columns data into rows.
If you try to delete any of the cell data, it will not get deleted because this is the part of the array table and does not get deleted.
This one is another useful method to transpose our dataset.
So these are some methods to transpose our dataset.
If you want to learn more about this, please visit Microsoft Office Support.
I hope you find this tutorial useful.
Please feel free to put your comment or suggestion in the comment box below.
Other Useful Posts