Show Excel Columns to Rows (Table of Contents)
In Excel, to convert any Columns to Rows, first select the column which you want to switch and copy the selected cells or columns. To proceed further, go to the cell where you want to paste the data. Then from the Paste option, which is under the Home menu tab, select the Transpose option. This will convert the selected Columns into Rows. You can also use the shortcut keys Alt + H + V + T, which will directly take us to the TRANSPOSE function. Paste Special Option. The shortcut key for paste special: Once you have copied the range of cells that need to be transposed, click on Alt + E + S in a cell where you want to paste special a dialog box popup window with the transpose option. How to Convert Columns to Rows in Excel using Transpose?Converting data from column to row in excel is very simple and easy. Let’s understand the working of converting columns to rows by using some examples. Example #1The below-mentioned Pharma sales table contains the medicine product code in column C (C10 to C16), quantity sold in column D (D10 to D16) & total sales value in column E (E10 to E16). Here we need to convert its orientation from columns to rows with the help of a paste special option in excel.
Data that is copied is called source data, and when this source data is copied to other ranges, i.e. pasted data, which is referred to as transposed data. Note: In the other direction where you are going to copy raw or source data, you have to ensure to select the same number of cells as the original set of cells so that there is no overlapping with the original dataset. In the above-mentioned example, there are 7-row cells that are arranged vertically. So when we copy this dataset to the horizontal direction, it should contain 7 column cells towards the other direction so that the overlapping does not occur with the original dataset. Example #2Sometimes, when you have huge number of columns, column data at the end is not visible, and it does not fit to screen. In this scenario, you can change its orientation from the current horizontal range to the vertical range with the help of the paste special option in excel.
In the transposed data, all the data values are visible and clear as compared to the source data. Disadvantages of using Paste special option:
Example #3Transpose function: It converts columns to rows and rows to columns in excel. The syntax or formula for the Transpose function is:
In the below example, the Pharma sales table contains medicine product code in column P (P7 to P12), and sales data in column Q (Q7 to Q12). Check out the number of rows & columns in the source data which you want to transpose. i.e. Initially, count the number of rows and columns in your original source data (7 rows & 2 columns) which is vertically positioned. Now select the same number of blank cells, but in the other direction (Horizontal), i.e. (2 rows & 7 columns)
Now you can check the dataset; here, columns are converted to rows with datasets. Advantages of the TRANSPOSE function:
Things to Remember about Convert Columns to Rows in Excel
Recommended ArticlesThis has been a guide to Columns to Rows in Excel. Here we have discussed how to convert columns to rows in excel using transpose along with practical examples and a downloadable excel template. Transpose can help everyone to convert multiple Columns to Rows in excel easily and quickly. You can also go through our other suggested articles –
How do I convert column text to rows in Excel?Here's how you can transpose cell content:. Copy the cell range.. Select the empty cells where you want to paste the transposed data.. On the Home tab, click the Paste icon, and select Paste Transpose.. How do I convert text to multiple rows in Excel?Try it!. Select the cell or column that contains the text you want to split.. Select Data > Text to Columns.. In the Convert Text to Columns Wizard, select Delimited > Next.. Select the Delimiters for your data. ... . Select Next.. Select the Destination in your worksheet which is where you want the split data to appear.. How do I split text into rows?Now, you can split multiple text cells into multiple rows using the same example.. Select and right-click cells containing text (B1:B3) and in the Menu, go to Data > Split text to columns.. Click the Separator button that appears, and choose Semicolon. ... . Now transpose the data from Rows 1–3 to Columns B–D.. |