Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
Excel Rows to Column (Table of Contents)
- Convert Rows to Columns in Excel
Convert Rows to Columns in Excel
Usually, converting of rows to columns is performed when the user realizes that the data entered in their spreadsheet or worksheet would look more presentable or is easy to be analyzed when the rows and columns are reversed in excel.
There are various methods of performing the conversion of rows to columns in Excel, which are as follows:
- Transposing Rows to Columns in Excel Using Paste Special.
- Transposing Rows to Columns in Excel Using TRANSPOSE Formula.
Method #1- Using Paste Special
This is an efficient method, as it is easy to implement and saves time as well. Also, if in case you want your original formatting to be copied then this method would be the ideal method for you.
For example, if you have below data of key industry players from various regions and also it shows about their job role as shown in figure1.
Here in example 1, the name of the players is written in the column form, but the list becomes too long and the data entered also seems to look unorganized. Hence, we would change the rows to columns using paste special method.
The steps to convert rows to columns in excel using paste special method are as follows:
- Select the entire data which needs to convert or transposed then, Copy the selected data by simply pressing ‘Ctrl + C’.
- Select a blank cell and make sure it is outside of the original data range. Staring at the first blank space or cell, paste the data by simply pressing ‘Ctrl +V’ and then from the toolbar, select the down arrow which is under ‘Paste’ option and select ‘Transpose’. But the easy way is right click the blank which you had selected first to paste your data. Then select’ Paste Special’ from it.
- Then a ‘Paste Special’ dialog box will appear from which select ‘Transpose’ option. And Click OK.
In the above figure, copy the data which needs to be transposed and then click on the down arrow of ‘Paste’ option and then form the options select ‘Transpose’. (Paste->Transpose)
It shows how the transpose function is performed to convert rows to columns.
Method #2- Using TRANSPOSE Formula
When a user wants to keep the same connections of the rotating table with the original table, it can use a transposing method using formula. This method is also a quicker way to perform conversion of rows to columns in excel. But, it does not keep the source formatting and links it to a transposed table.
Consider the below example as shown in figure6, where it shows the name of the companies, its location and also a number of employees.
The steps to transpose rows to columns in excel using formula are as follows:
- First of all, count the number of rows and columns present in your original excel table. And then, select the same number of blank cells but make sure you have to transpose the rows to columns so you will select the blank cells in the opposite direction. Like in example 2, the number of rows is 3 and the number of columns is 6. So you will select 6 blank rows and 3 blank columns.
- In the first empty cell, enter the TRANSPOSE formula ‘=TRANSPOSE($A$1:$F$3)’.
- Since this formula needs to be applied for other blank cells so press ‘Ctrl+Shift+Enter’.
The rows are converted to columns.
Advantages of Converting Rows to Columns in Excel
- The paste special method is useful when the user needs the original formatting to be continued.
- In the transpose method by using the transpose function, the converted table will change its data automatically i.e. whenever the user changes the data in the original table it will automatically change the data in the transposed table, as it is linked to it.
- Converting the rows to columns in excel sometimes helps the user to make the audience understand their data easily and so that the audience interprets it without many efforts.
Disadvantages of Converting Rows to Columns in Excel
- If the number of rows and columns increases then the transpose method by using paste special option cannot perform efficiently. And in this case, when there are a lot of rows to be converted to columns, the ‘Transpose’ function will be disabled.
- The transpose paste special option does not link the original data to the transposed data, therefore if in case you need to change the data then you need to carry out all the steps again.
- In the transpose method by using formula the source formatting is not kept the same for the transposed data.
- After converting the rows to columns in excel by using formula, the user cannot edit the data in the transposed table as it is linked to the source data.
Things to Remember
- Rows can be transposed to columns by using paste special method and the data can be linked to the original data by simply selecting ‘Paste Link’ form the paste special dialog box.
- INDIRECT formula and ADDRESS functions in Excel can also be used to convert rows to columns and vice-versa.
This has been a guide to Convert Rows to Columns in Excel. Here we discuss how to Switch Rows to Columns in Excel using Paste special and TRANSPOSE Formula along with practical examples and downloadable excel template. You may learn more about excel from the following articles –