Excel convert columns to one row

How to Transpose Excel Columns to Rows

We all look at data differently. For example, some people create Microsoft Excel spreadsheets with the main fields horizontal. Others prefer data flipped vertically in columns. Sometimes these preferences lead to a scenario where you want to switch data. In this tutorial, I’ll show how to switch rows and columns using the Transpose feature.

The Layout Problem

I was recently given a large Microsoft Excel spreadsheet that contained vendor evaluation information. The information was useful, but I couldn’t use tools like Auto Filter because of the way the data was organized. I would also have issues if I needed to import the original source table into a database. A simple example is shown below.

Excel Spreadsheet Needing to Swap Columns & Rows

Instead, I want to have the Company names displayed vertically in Column A and the Data Attributes displayed horizontally in Row 1. This would make it easier for me to do the analysis. For example, I can’t easily filter for California vendors with the original table.

What is Excel’s Transpose Function

In simple terms, the transpose feature changes your columns’ orientation (vertical range) and rows (horizontal range). My original data rows would become columns, and my columns would become rows. So this fixes my layout problem above without me having to retype my original data. And the system would adjust for column labels.

The transpose function is pretty versatile. For example, you can use the feature in Excel formulas or with paste options. However, there are some differences based on which method you use.

Convert Columns to Rows Using Paste Special

The steps outlined below were done using Microsoft Office 365, but recent Microsoft Excel versions will work.

1. Open the spreadsheet you need to change. You may also download the example sheet at the end of this tutorial.
2. Click the first cell in your cell range such as A1.
3. Shift-click the last cell of the range. Your data set should highlight.
4. From the Home tab, select Copy or type Ctrl + c .

Copying the current Excel data

1. Select the new cell where you would like to copy your transposed data.
2. Right-click in that cell and select the Transpose icon from the Paste Options.

Choose the Transpose option

✪ As you hover over the Paste options, you can see the data layout change.

You should now see Excel switched the columns and rows. You can resize your columns to suit your needs.

These two data sets are independent. You can delete cells from the top set and it will not impact the transposed set.

Transposed data under original.

When using this method, your original formatting is maintained. For example, if I added a yellow background to my original cells B1:G1, the same background color would be applied. The same is true if I used red text on cell E:5.

Use Transpose Function in a Formula

As I mentioned, Excel has multiple ways for you to switch columns to rows or vice versa. This second way utilizes a formula and array. The result is the same except your original data, and the new transposed data are linked. As a result, you may lose some of your original formatting. For example, colored text and styles came across, but not cell fill colors.

1. Open your Excel sheet.
2. Click a blank cell where you want your converted data. I’m using A7.
3. Type =transpose.

Typing transpose formula

✪ Notice how Excel provides a tooltip showing for Transpose – “Converts a vertical range of cells to a horizontal range and vice versa“.

1. Finish the formula by adding a ( and highlighting the cell range we wish to swap.
2. Type ) to close the range.

Transpose formula and range

1. Press Enter .

✪ If you’re not using Microsoft 365, you’ll probably need to press CTRL + SHIFT + Enter

Differences in Transpose Options

While these two methods produce similar results, there are differences. In the first paste method, whatever action I take on a cell is independent of the transposed version. I could delete the original values, and nothing would happen to the columns I swapped.

In contrast, the Transpose formula version is tethered to the original data. So, for example, if I change the value in B2 from 1200 to 1500, the new value will automatically update in B8. However, the reverse isn’t true. If I change any transposed cells, the original set will not change. Instead, I will get a #SPILL error, and my transposed data will disappear.

Error when changing transposed cell

Transpose Formula & Blank Cells

Another difference with using the Transpose formula is it will convert blank cells to “0”s.

Transpose converted blanks to 0’s

The fix for this quirk is to use an IF statement to the formula that keeps the blanks cells as blanks.

Alternatively, you could do a search and replace on the zeros.

Now that we’ve shown you how to transpose data in Excel, try playing around with the practice worksheet below. You’ll be swapping your rows and columns in Excel in no time. And while you likely work with multiple rows, you can convert one Excel column to row.

Show Me How Video

Click the image below to see a short video showing how to switch columns and rows without a formula.

Источник

Excel convert data from column to row

This Excel VBA tutorial explains how to convert data from column to row (transform one column data to one row).

You may also want to read:

Excel convert data from column to row

Different database have different structure. Some database put similar data in the same column with different rows, while some database put data in different columns. .Let’s take an example.

Type 1: all payment types are arranged in the same column

Employee ID	Payment Type	Amount
1	Salary	10000
1	Allowance	1000
2	Salary	10000
3	Salary	10000
4	Salary	10000

Type 2: each column for each payment type

Employee ID	Salary	Allowance
1	10000	1000
2	10000
3	10000
4	10000

In this tutorial, I will demonstrate how to use VBA to convert data from column to row (from type 2 to type 1).

VBA Code – convert data from column to row

Please note that when I wrote this Macro, I only tested the scenarios in my below examples. For any other exceptional scenarios that I am not aware, my Macro may not function probably (I don’t know what I don’t know).

Press ALT+F11 and then paste the below code in a new Module

Convert all columns to a single column

A little explanation for this code with an example. In the below Table, my goal is to move each salary and bonus column from column D to I to the same column (column D). For each newly added row, I want column A to C to repeat.

In the parameter line of code, I need to set the followings

Run the Macro and get the below result.

Column E is a new column created to indicate which column the data in column D originally came from. Column D header has to be manually change as it is the original header before conversion, so it is more appropriate to change it to “Amount”.

Convert all columns to a multiple columns

Suppose I want to convert all data columns to fit into column D (Salary) and column E (Bonus). Use the same Macro as above but update the below parameter to 2.

Run the Macro and get the below result.

Similar to the above example, column F is the new column to indicate where column D and E data originally came from. Column D and E headers should be renamed appropriately, such as “Salary” and “Bonus”.

Источник

Convert Columns to Rows in Excel

How to Convert Columns to Rows in Excel?

There are two methods of doing this:

1. Excel Ribbon Method
2. Mouse Method

Let us take the below example to understand this process.

Table of contents

#1 Using Excel Ribbon – Convert Columns to Rows with copy and paste

We have the sales data location-wise.

This data is very useful for us, but we want to see this data in vertical order so that it will be easy to compare.

We must follow the below steps for converting columns to rows:

1. We must select the whole data and go to the “HOME” tab.

Then, we must click on the “Copy” option under the “Clipboard” section. You may refer to the below screenshot. Else, press “CTRL +C” keys to copy the data.

Then, we must click on any blank cell where we want to see the data.

Next, we must click on the “Paste” option under the “Clipboard” section. You may refer to the below screenshot.

As a result, this will open a “Paste” dialog box. Then, choose the option “Transpose,” as shown below.

Consequently, it will convert the columns to rows and display the data as we want.

The result is shown below:

Now, we can put the filter and see the data differently.

#2 Using Mouse – Converting Columns to Rows (Or Vise-Versa)

Let us take another example to understand this process.

We have some student score data subject-wise.

Now, we have to convert this data from columns to rows.

Follow the below steps for doing this:

• Step 1: We must first select the whole data and right-click. As a result, it will open a list of items. Now, we must click on the “Copy” option from the list. You may refer to the below screenshot.

• Step 2: We need to click on any blank cell where we want to paste this data.
• Step 3: Again, right-click and select Paste Special optionPaste Special OptionPaste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such as CTRL+ALT+V or ALT+E+S.read more .

• Step 4: Again, it will open a “Paste Special” dialog box.
• Step 5: Then, click on the “Transpose option,” as shown in the below screenshot.
• Step 6: Lastly, we must click on the “OK” button.

Now our data is converted from columns to rows. The final result is shown below:

In other ways, when we move our cursor or mouse on “Paste Special” option, it will again open a list of options as shown below:

Things to Remember

• Converting columns to rows or vice-versa both methods also work when we want to convert a single column to a row or vice-versa.
• This option is very handy and saves a lot of time while working.

Recommended Articles

This article is a guide to Convert Columns to Rows in Excel. We discuss switching columns to rows in Excel using the ribbon and mouse method, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

Источник