Excel Columns to Rows: 3 Easy Ways to Transpose Your Data
Microsoft Excel popularly known as MS Excel, a powerful spreadsheet, is a part of MS Office software package. Excel is a big collection of rows and columns into which you enter numbers, words, pictures and sounds. In simple words it’s a big calculator which makes your life easy. Though many tend to find it intimidating, it is quite easy to learn Excel. You don’t have to be a math whiz to understand or use Excel (but you certainly can learn how to be one with this course!).
When you open the Excel software, you see the grid of rows and columns. The rows are horizontal and are identified by numbers (1,2,3) while the columns are vertical with the letters (A,B,C). The columns beyond 26 are identified by alphabets such as AA, AB, AC , AAA and so on.
The intersection point between a row and column is called as a cell. It is the basic unit where the data in the spreadsheet is stored. These cells are identified by a cell reference. The cell reference is a combination of the row number and column alphabet. Let’s take an example of A3, C6, AA345 and so on. Here A3 is the cell reference or cell address. It means, Column A Row 3. Similarly, AA345 means, Column AA and Row 345. Look it’s so easy. In this tutorial we will show you how to convert columns to rows, and you can learn even more about how to use Excel with this course.
Switching data from columns to rows or from rows to columns is known as transposing the data. When you convert columns into rows, the dimensions of data and the cell address also change. Therefore you should be really careful while transposing the data. There are several ways to transpose columns to rows. We list three simple ways you can do this.
Method 1- Use the Transpose Command in Paste Dropdown Menu
Let’s take the example of regional sales data in a table. Have a look at the images given below. Follow the steps given below to convert the column data to row data and vice versa.
Step 1: Select the table on which you want to apply the transpose command.
Step 2: On the Home tab, in the clipboard group, click copy. You can also use the short cut key- Ctrl+c
Step 3: On the worksheet, select the first cell of the destination rows or columns.
Step 4: On the Home tab, in the clipboard group, click Paste and then click Transpose.
Method 2- Use Transpose Function ()
Say you have 5 rows and 4 columns, that you want to transpose. Another way of doing this is to use the actual Transpose() function.
Step 1: Copy the data. In our example, its 4 rows and 5 columns.
Step 2: Select the destination area. That is now you have to select a target area with 4 rows deep and 5 columns deep.
Step 3: Press F2 function key to enter into edit mode while the target area is still selected.
Step 4: Write “=” (without quotes) followed by the Transpose function and select the columns that you want to transpose.
Step 5: Press CTRL+ SHIFT+ ENTER keys. Remember it’s an array formula; therefore it’s not just the Enter Key but the combination of CTRL+ SHIFT+ ENTER keys.
When you are using Transpose (), when you change the values in the source range, the values in the transposed range will also change accordingly. A disadvantage of using transpose function is that, since it’s an array when you make changes to the values in the source range, it tends to break. Therefore it is not used when you are handling complex data range.
Method 3 – Using Paste Special
If you are using complex data to transpose from columns to rows and vice versa use the option Paste Link in Paste Special dropdown menu.
In the previous method when the source data is changed there is a possibility of the live feed in the destination range being broken. This is rectified by using the Paste Link. Instead of the values, the Paste Link command pastes the cell references. Therefore, if the values in the source range are changed, the linked cells will also change. Though this function sounds complicated it’s simple and fun provided you follow the steps correctly.
Step 1: Copy the data range and choose a destination rage on the same worksheet.
Step 2: Hold down the combination of Alt+ Ctrl+ V to get the paste special dialogue box. Select the paste link button. The result is that the cell references of the range are copied.
Step 3: Select the range you just pasted in the destination range and hold down Ctrl+H to invoke the Replace dialogue box.
Step 4: In this box, enter “=” (without quotes) in the “Find what” field and in the “Replace with” field “HS=” (without quotes) and click Replace all button. Here what happens is, the formula is converted into text as you have just replaced the “=” with “HS=”.
Step 5: Copy this range again. Select the destination range where you want the transposed range. Hold down the Alt+Ctrl+V keys to invoke the paste special dialogue box. Select the Transpose option and click Ok. This will convert the columns to rows.
Step 6: Now to replace the transposed data which is actually the formula with correct and relevant values from the source range, hold down the Ctrl+H again to invoke the Replace dialogue box. This time, in the “Find what” field put “HS=” (without quotes) and in the “Replace With” enter “=” (without quotes) and click Replace All button.
Now you have the live-transposed range of data without using the Transpose function.
Microsoft Excel is an important part of your toolkit, especially if you manage any kind of data. It allows you to store and analyze data easily and with great flexibility. Learning MS Excel can be easy if you put your mind to it. And of course, our fun and comprehensive course on Excel is always available to help you along.
Interested in learning about VBA in Excel and how to create a simple Macro? Instructor Leila Gharani explains everything you need to know in this article.
Top courses in Excel
Excel students also learn
Empower your team. Lead the industry.
Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.