Analysts all over the world work with loads of data while working in excel. The reason behind they work with excel without using any other method or software is that excel provides a lot of efficiency, ease, and pace.
When working with a lot of data, you may need to do a lot of repetitive tasks to identify, clean, and sort raw data into a workable format.
For example, you can get data that is horizontally sorted but you need the data to be in a vertical column so that you can work around it and build a convenient model.
The first thought that comes to mind is copying each data and then pasting it vertically, but Excel makes our lives easier.
You can easily and quickly paste horizontal data vertically or paste vertical data horizontally in excel.
So, today let’s see how to paste horizontal data vertically in excel.
What are Horizontal and Vertical Data?
They are just like any other data. It is just how they are displayed or sorted that makes them either vertical or horizontal.
If the data is sorted in a row, then it’s horizontal data.
And when the data is sorted in a column then it’s vertical data.
Our goal is to convert the vertical data into horizontal data or convert the horizontal data into vertical data in excel. Either way, the methods are the same in excel.
So, let’s see how to convert or paste horizontal or vertical data vertically or horizontally in excel.
How to Paste Horizontal Data Vertically in Excel?
There are two very simple and quick ways you can paste horizontal data vertically in excel. The same methods can be followed to paste vertical data horizontally in excel.
The first method is simple copy-pasting and the second method is using the TRANSPOSE function.
Method#1 Using the Paste Special Function to Transpose (Pasting horizontal data vertically or vice versa) the data in excel
To convert the horizontal data into vertical data, first select the data set and then copy the data set by pressing CTRL+C.
Now, go to the cells you want the data to be vertically sorted or placed. Then right-click and got to paste special and select the Transpose option.
You will see that the data has been vertically pasted or converted.
Method#2 Using the TRANSPOSE Function to Transpose (Pasting horizontal data vertically or vice versa) the data in excel
Converting vertical data into horizontal data or horizontal data into vertical data in excel is simply known as transposing the data. There is a built-in function for that in excel named TRANSPOSE.
So, the next method is to use the TRANSPOSE function to convert the horizontal data into vertical data.
To transpose the data, go to the new cells you want the data to be in. Then type the following formula:
=TRANSPOSE(A1:F1)
Now, press CTRL+SHIFT+ENTER to get the result which is the vertical data.
As transpose is an array function you need to press CTRL+SHIFT+ENTER. If you just press enter, Excel will show you the result but you won’t be able to edit the formula if needed later on.
For older versions of Excel, it will show you an error (#value) if you just select one cell to enter the formula. You need to select the whole array equal to the number of cells the original data are in.
So, for example, in this case, you need to select 6 cells to get the result on previous versions of excel.
And if you select less than the required cells, you will need to press CTRL+SHIFT+ENTER, after the formula has run to get the rest of the data that was not picked up by excel due to selecting fewer cells than the original data. However, these rules are all for older versions of Excel, newer versions are smarter and more versatile.
So, there you go now you know how to paste horizontal data vertically in excel.
Conclusion
As stated earlier, excel is really smart and versatile. Transposing the data in excel, or converting horizontal data into vertical data is very fast and efficient.
Now, you don’t have to make up for lost time and you can be fast and efficient while cleaning and sorting raw data because now you know how to paste horizontal data vertically in excel.
Happy Transposing in excel.
Hi there, I am Naimuz Saadat. I am an undergrad studying finance and banking. My academic and professional aspects have led me to revere Microsoft Excel. So, I am here to create a community that respects and loves Microsoft Excel. The community will be fun, helpful, and respectful and will nurture individuals into great excel enthusiasts.