How to Convert Excel Columns to Rows: A Practical Guide to Transposing Data

Key takeaways

Converting columns to rows in Excel is a way to rearrange your data without changing the values themselves.

• Transposing is another term for converting columns to rows, which means rotating your data 90 degrees.

• To build your Excel basics, you can start with a few simple calculations and tasks, including entering your data, formatting cells, and applying basic functions. 

• You can transpose your data in Excel using Paste Special, Power Query, the TRANSPOSE function, and keyboard shortcuts.

Learn how to transpose your data in Excel with several methods to find the right one for your use case. Then, when you're ready, start building more skills with the Excel Skills for Data Analytics and Visualization Specialization. In as little as four weeks, you can learn to bring data to life using advanced Excel functions, creative visualizations, and powerful automation features. Plus, you can explore a new dimension in Excel with PowerPivot, Get and Transform, and DAX. Upon completion, you’ll have a shareable certificate to add to your professional profile. 

What does converting columns to rows mean in Excel?

Converting Excel columns to rows is a process called transposing, which means rotating your data 90 degrees. When you transpose your data from columns to rows, your column headers will become row headers, and the values that run vertically down your columns will instead run horizontally across your rows.

For example, imagine you’re tracking your sales. Your data before might look something like:

If you were to transport your data and convert your columns to rows, it would look like:

You might choose to convert your columns to rows depending on how you prefer to analyze and represent your data.

How to transpose data in Excel using Paste Special

One of the most common ways to convert your data from columns to rows in Excel is by using the Paste Special option. To use this option, follow these steps:

1. Select your data, including any column labels.

2. Copy your information by pressing Ctrl+C on your keyboard. 

3. Choose an empty space in your Excel worksheet where you want to paste your transposed data. Make sure to remove all information from these cells, as your transposed data will overwrite any existing text. 

4. In the top-left cell where you want to paste your transposed table, right-click and choose Paste Special. You may see a Transpose shortcut option, or you might need to open the Paste Special pop-up. If you have the pop-up block, check the Transpose option and click Okay. 

5. Now you will have successfully transposed your data. The original table will still be present, and you can delete it if you no longer need it.

Tips for when your data set contains formulas or formatting

If you have formulas or complex formatting, you may have to take additional steps when transposing your data. When transposing cells containing formulas, the cells containing formulas try to adjust references to the new orientation. While this may retain your information effectively, sometimes more complex changes can break the calculation. In this scenario, you can choose to copy values directly, rather than copying the formula. To do this, copy your data, then choose Paste Special > Values to replace the formulas with numbers before transposing. 

Additional tip: Using PivotTable

If you need to retain formulas, you may want to play around with absolute, relative, and mixed references to find the best way to retain your information. In some cases, you can create a PivotTable to quickly transpose your data and view both orientations. If you need to copy formatting, you’ll typically want to use the Paste Special transpose option rather than the =TRANSPOSE() formula.

How to use the Excel TRANSPOSE function for dynamic columns-to-rows conversions

Another way you can transpose your data is directly with the TRANSPOSE function. To use this function:

1. Select the cells where you want your transposed data to appear. Make sure it’s the right size. For example, if your original data includes three columns and four rows, your selected empty cells should be four columns and three rows. 

2. Next, while you have the blank cells selected, type =TRANSPOSE(array), where the “array” represents your set of original cells [e.g., =TRANSPOSE(A1:B3)]. If you highlight the cells you want to transpose, Excel will automatically populate the array indicators (cell location).

3. Finally, press Ctrl+Shift+Enter to finish the array formula and apply it to your cells. 

How to convert Excel columns to rows using Power Query

If you’re looking for a repeatable way to transpose your data, Power Query is a great option. Power Query works with a 90-degree table rotation operation that turns rows into columns and columns into rows. This Power Query method goes a step further than Paste Special or the TRANSPOSE function by remembering your transformation steps. Because of this, it’s ideal if you have data that regularly needs to be refreshed.

1. To use this method, you’ll first select your data range.

2. Next, click the Data tab and choose Get Data. 

3. Following this, click From Table/Range. At this point, if your data isn’t currently in a table format, Excel will prompt you to create a table.

4. Next, in the Power Query Editor window, click the Transform tab in the ribbon and select the Transpose button.

5. After you click Transpose, the table content will rotate, and the original column headers will be lost. Instead, new columns will be automatically named “Column” followed by sequential numbers. 

6. If you want to convert your former column headers—which are now the first row—back to proper headers, you can click the Table icon in the upper-left corner of the data preview and choose Use first row as headers or unclick Header row, depending on your version of Excel.

What is the shortcut to convert a column to a row in Excel?

To efficiently convert a column to a row, you can follow a few steps on your keyboard. 

1. Start by selecting your data range and pressing Ctrl+C to copy the data. 

2. Next, click the destination cell and press Ctrl+Alt+V (Windows) or Cmd+Ctrl+V (Mac) to open Paste Special. Select Transpose, and Enter or OK to execute the action.

Learn more: How to Convert Excel to Google Sheets

Why does my Excel transpose not work?

If your Excel transpose operations aren’t working, you might be working with a legacy version of Excel, you may be trying to cut instead of copy your data, or it may be due to a variety of other reasons. A few of the most common issues include the following:

• You’re working with a legacy Excel and didn’t enter TRANSPOSE as an array formula. In these versions, you need to press Ctrl+Shift+Enter to implement the array formula.

• You want to copy formatting but are trying to use the =TRANSPOSE() function. In these cases, using the Paste Special transpose function typically works more effectively. 

• You’re trying to “cut” your data rather than “copy” it when using the transpose feature. In general, the transpose feature only works with copy, typically as Ctrl+C.

• Your data is in an Excel table, and you’re trying to use the Transpose feature. Typically, you need to convert your table to a range first before you can use the TRANSPOSE function. Otherwise, opt for other transpose methods.

How to start learning Excel fundamentals

To build your Excel basics before exploring transposition and other data manipulation tasks, you can start with a few simple calculations and tasks. To begin, learn how to create a new workbook. Next, learn how to enter your data and apply cell borders and shading. Once you’ve mastered basic formatting, you can explore how to create a simple formula, apply number format, and put data in a table.

To go one step further with your data, you can practice basic functions and operations such as sorting your data, filtering your data, summing your data, and performing quick analyses. Over time, you can progress to more advanced formatting and functions, such as activating add-ins and applying templates.

Build your Excel and data analytics skills with more free resources

If you’re interested in expert guidance and no-fluff tips to help you build your analytical skills, subscribe to our . You can also explore free resources to learn new topics, explore emerging industries, and discover new opportunities:

• Learn new skills: How to Convert Excel to Google Sheets

• Build your vocabulary: Excel Terms and Definitions

• Learn from experts: 7 Questions with a Data Analytics Professor

Whether you want to develop a new skill, get comfortable with an in-demand technology, or advance your abilities, keep growing with a subscription. You’ll get access to over 10,000 flexible courses.

Build job-ready skills with Coursera Plus

Start 7-day free trial

• 
• 
• 
• 

Start 7-day free trial