Excel: Transposing Columns into Rows (Examples).

Excel Table of Contents: Converting Columns to Rows

In Excel, Convert Columns to Rows

Select the column you want to convert and then copy the cells or columns into the active row in Excel. Step 2: Locate the cell into which you want to paste the information. After that, choose Transpose from the Paste menu under the Home tab. The selected columns will be transformed into rows. Alternatively, we can access the TRANSPOSE feature by pressing the Alt + H + V + T keys.

Copy and Paste with Extras

The hotkey for the enhanced paste function:

The transpose option will appear in a pop-up window when you copy the required range of cells and then click Alt E S in a cell where you want to paste special.

Transpose in Excel: A Guide for Converting Columns to Rows

Excel makes it effortless to transform data from columns to rows. Let's use some examples to see how the process of converting columns to rows works.

Example #1

You'll find the medicine's product code in column C (C10 to C16), the quantity sold in column D (D10 to D16), and the total sales value in column E (E10 to E16) of the sales table provided below.

Here, we'll use Excel's paste special feature to change the columnar data to a horizontal format.

• Choose all of the cells in the table, i e spreadsheet rows and columns containing data
• Pressing Ctrl + C will copy the currently selected cells.

• Choose the cell into which you'll paste the data, i e G10

• The paste option appears when you right-click the mouse. Then, go with the fourth choice, i. e The below screenshot describes the transpose function.

• When you choose the transpose option, your dataset is duplicated in that specific cell range, beginning at cell G10 and ending at cell M112.

• In addition to the aforementioned steps, you can also use a shortcut key in Excel to transform columns into rows.
• Pressing Ctrl + C will copy the currently selected cells.
• Find the cell that contains the information you wish to copy, i e G10
• When you press Alt + Shift + S, Excel's Paste Special window will appear, and from there you can choose the Transpose option to transform your columnar data into rows.

Source data refers to the original data from which a copy is made, and when this data is copied to other ranges, i e data that has been copied and pasted (also known as transposed data)

It's important to remember that when copying raw or source data in the opposite direction, you need to pick exactly as many cells as there were in the original set. Otherwise, your new data will overlap with the old. In the aforementioned illustration, the 7 rows of cells are arranged vertically. To prevent overlap with the original dataset, we need to ensure that the horizontal copy includes 7 column cells in the opposite direction.

Example #2

When there are many columns, the data at the end of the last column may be cut off because it is too long to fit on the screen. In this case, you can use Excel's paste special feature to switch the range from its current horizontal orientation to a vertical one.

• Columns C through W of the table list the months and their respective sequence numbers.

• Pick up everything in that table, i e a spreadsheet's worth of data cells
• Simply press Ctrl + C to copy the current selection of cells.
• Find the cell that contains the information you wish to copy, i e C24
• The fourth option in the paste menu that appears when you right-click the mouse is the one you need to use. e Change the order of (see screenshot below)

• When you choose to transpose your dataset, a duplicate is made beginning at cell C24.

All of the original data values are preserved and easily readable in the transposed data.

Consequences of the Paste special feature:

• The copy/paste function can produce duplicates in some cases.
• The transposed cells (or arrays) are not linked to each other with source data and are not updated when you try to change data values in source data if you have used the Paste special option in Excel to convert columns to rows.

Example #3

Excel's transpose function flips rows into columns and columns into rows.

Transpose's syntax, or formula, looks like this:

• Array: a group of cells whose orientation must be altered or converted.

Below is an example of a pharmaceutical company's sales data table, with columns for product code (P7–P12) and sales (Q7–Q12).

Verify the row and column counts of the data you wish to transform.

i e To begin, take a look at the vertical orientation of your original data source (7 rows and 2 columns). Choose the same number of empty rows and columns in the opposite orientation (Horizontal), i.e. e Two rows and seven columns

• Choose the blank cells where you intend to enter the formula before you do so. e across P15 and V16

• Select the blank cells and enter the transpose formula into cell P15. i e = SWITCH POSITIONS (P6:Q12)

• Since the formula should be used for the entire range, you can convert it to an array formula by pressing Ctrl+Shift+Enter.

You can now examine the dataset; in this case, the columns have been transformed into rows.

One of TRANSPOSE's benefits is that:

• The primary benefit of the TRANSPOSE function is that any changes made to the source data are reflected in the transposed or rotated data without any additional work being required on the user's part.

Tips for Excel's Columns to Rows Conversion

• The Paste Special Menu Icon can be used for a Variety of Purposes e Multiply, divide, add, and subtract between data sets
• Selecting the cells results in a #VALUE error, i e The transposed data may have inaccuracies if the number of columns and rows chosen does not match that of the original data.

Suggested Readings

This has been a tutorial on how to convert Excel columns to rows. In this article, we will go over the transpose function in Excel and how to use it to convert columns to rows. We will also provide some examples and a downloadable template. Anyone can use Transpose to quickly and easily transform Excel tables with multiple columns into tables with multiple rows. We also encourage you to read the following articles: