How to Make Row Data into Column in Excel

Switching your data from rows to columns (or columns to rows) is one of those surprisingly common but frustrating tasks in Excel. You might have downloaded a report that isn't formatted correctly, or perhaps you need to rearrange a table to make it easier to chart. This guide will walk you through a few different ways to transform your data, so you can stop wrestling with your spreadsheet and start analyzing it.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why Would You Need to Transpose Data in Excel?

Flipping rows and columns, also known as "transposing," is something pretty much anyone who uses Excel will need to do eventually. It's not just a formatting trick, it's a fundamental step in data preparation. Here are a few common scenarios where it comes in handy:

• Unpivoting Summarized Data: Reports often come with categories in rows and time periods (like months or quarters) stretched across columns. For a lot of analysis, particularly for creating certain charts or pivot tables, you need those time periods in a single column.
• Preparing for Charts: Some Excel charts work best when data is structured in a specific way. If your chart isn't looking right, transposing the source data is often the quickest fix.
• Fixing Imported Data: When you copy and paste data from websites, PDFs, or other software, it rarely lands in Excel in a usable format. Transposing is a vital first step to clean it up.
• Improving Readability: A table with dozens of columns and just a few rows can be difficult to read and scroll through. Flipping it can make it much more user-friendly.

Whatever your reason, Excel provides several ways to get the job done. Let's look at the three most effective methods, from the quick-and-simple to the powerful-and-repeatable.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Method 1: The Paste Special (Transpose) Feature

This is the fastest and most common way to flip a table. If you just need a one-time conversion and don't care about the new data automatically updating if the original changes, this is the perfect method for you. It's essentially a sophisticated copy-and-paste job.

Step-by-Step Instructions

1. Select Your Data: Click and drag to highlight the entire range of cells you want to flip, including any row or column headers. You can also click any cell within your data range and press Ctrl + A to select it all.
2. Copy the Data: Once your data is selected, copy it to your clipboard. You can do this by right-clicking and selecting "Copy" or by using the keyboard shortcut Ctrl + C. You'll see a dotted line (the "marching ants") around your selection.
3. Choose a Destination: Click on a single, empty cell elsewhere in your worksheet or on a different sheet. This is important! You need to choose a blank space where your new, transposed table can live without overwriting any existing data.
4. Open Paste Special: With the destination cell selected, navigate to the Home tab on the Excel ribbon. In the "Clipboard" group on the far left, click the small arrow underneath the "Paste" icon to open the dropdown menu. From there, select Paste Special....
5. Check the Transpose Box: A dialog box will pop up with many pasting options. Near the bottom-right corner, you'll see a checkbox labeled Transpose. Check that box.
6. Click OK: Click the "OK" button, and Excel will instantly paste your data in the new orientation. Your old rows are now columns, and your old columns are now rows.

Pros and Cons of Paste Special

• Pros: It's incredibly fast and intuitive. For quick, one-off transpositions, you can't beat its simplicity. It requires no formulas or technical setup.
• Cons: The biggest drawback is that the result is static. Pasting this way creates a completely new, disconnected copy of your data. If you update a number in your original table, the transposed table will not change. You would need to repeat the copy-paste process to see the update.

Method 2: The TRANSPOSE Function (The Dynamic Option)

What if you need your transposed data to stay linked to the source? This is a common requirement in dashboards or reports where the original data is frequently updated. This is where the TRANSPOSE function comes in. It creates a live, mirrored version of your source data in the new orientation.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step-by-Step Instructions

The steps for this function depend slightly on what version of Excel you’re using. Modern Excel (Microsoft 365 or Excel 2021) with dynamic arrays makes this incredibly simple. Older versions require an extra step.

For Modern Excel (Dynamic Arrays):

1. Select a Single Destination Cell: Just like before, pick a single empty cell where you want the top-left cell of your transposed data to begin.
2. Type the Formula: In that cell, type: =TRANSPOSE(
3. Select Your Source Range: After the open parenthesis, use your mouse to select the entire range of your original data (e.g., A1:E4). Your formula bar should now look something like this: =TRANSPOSE(A1:E4)
4. Press Enter: Simply press the Enter key. Excel will automatically "spill" the formula across the required number of cells to create your transposed table. Voila!

For Older Excel Versions (using CSE):

If you're using an older version of Excel (2019 or earlier) without dynamic arrays, the process needs a bit more precision.

1. Count Your Rows and Columns: Look at your source data and count how many rows and columns it has. For example, our A1:E4 table has 4 rows and 5 columns.
2. Select the Exact Destination Range: Here's the key step. You need to pre-select a blank grid of cells that has the inverted dimensions. Since our original data is 4 rows by 5 columns, you need to select a blank range that is 5 rows by 4 columns.
3. Enter the Formula: With your destination range highlighted, type the formula in the formula bar: =TRANSPOSE(A1:E4). Do not press Enter yet.
4. Press Ctrl + Shift + Enter: Instead of pressing Enter, press Ctrl + Shift + Enter. This special key combination tells Excel to treat your formula as an array formula. Excel will automatically add curly braces { } around your formula, and it will fill the entire range you selected.

Pros and Cons of the TRANSPOSE Function

• Pros: It's dynamic! Any changes made to the original source table will be reflected instantly in the transposed table. It’s perfect for creating summary tables for reports or dashboards.
• Cons: It can be a little less intuitive, especially the CSE method for older versions. You also cannot change or delete a single cell within the transposed array, you'll get an error. You must edit the original data. In modern Excel, you might run into a #SPILL! error if there isn't enough empty space for the results to spill into.

Method 3: Power Query (The Most Powerful & Repeatable Method)

If you find yourself transposing the same data file every week, or if you're dealing with very large datasets, then using Power Query is your best bet. Power Query is Excel's data transformation engine, designed to build repeatable workflows for cleaning and reshaping data.

This method has a slightly steeper learning curve but is unbelievably efficient once you set it up.

Step-by-Step Instructions

1. Load Data into Power Query: First, format your source data as a table by selecting it and pressing Ctrl + T. Then, with a cell in your table selected, go to the Data tab and click From Table/Range.
2. Open the Power Query Editor: Excel will open a new window - the Power Query Editor - displaying your data in a preview.
3. Find the Transpose Button: In the Power Query Editor, click on the Transform tab. In the "Table" group, you will find a button labeled Transpose.
4. Click and Review: Just click the "Transpose" button. Power Query will immediately flip your table's rows and columns.
5. Promote Headers (if needed): Often, after transposing, your original column headers will end up in the first column of data. If you want these to become the new headers of your transposed table, head back to the Transform tab and click Use First Row as Headers.
6. Close & Load: Once your table looks right, go to the Home tab in Power Query and click the Close & Load button. By default, this will load the transposed data into a new worksheet as a formatted, refreshable table.

Pros and Cons of Power Query

• Pros: It's fully automated and repeatable. Once you set up these steps, you can refresh your transposed data with one click (Data > Refresh All) whenever the source data changes. It’s excellent for handling huge datasets that would cripple normal Excel functions and can pull in data from many different sources, not just your worksheet.
• Cons: It's by far the most complex of the three methods and can feel like overkill for a quick, one-time task. It requires learning a new interface and way of thinking about data transformation.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Final Thoughts

From a lightning-fast copy-paste to a dynamic formula or a repeatable Power Query workflow, you have multiple ways to transpose data in Excel. The best method simply depends on your specific goal. For a one-time adjustment, use Paste Special. For a living report or dashboard, the TRANSPOSE function is your answer. And for complex, repeatable processes, Power Query is the professional's choice.

Of course, this kind of data wrangling is often the first painful step in a long manual reporting process. Many of us spend hours exporting CSVs from tools like Google Analytics, Shopify, or Salesforce, only to perform these exact cleaning steps before we can even begin to build our reports. Using an AI data analyst like Graphed , we can automate that entire manual process. Because we connect directly to your marketing and sales platforms, there’s no need to export or reshape data in spreadsheets. You can just ask a question like "show me website sessions by country for the last 90 days as a bar chart" and get an instantaneous, live-updating dashboard without ever touching a CSV file.