Excel transpose

Transpose

Paste Special Transpose | Transpose Function | Transpose Table without Zeros | Transpose Magic

Use the 'Paste Special Transpose' option to switch rows to columns or columns to rows in Excel. You can also use the TRANSPOSE function.

Paste Special Transpose

To transpose data, execute the following steps.

1. Select the range A1:C1.

2. Right click, and then click Copy.

Right Click Copy

3. Select cell E2.

4. Right click, and then click Paste Special.

5. Check Transpose.

Check Transpose

6. Click OK.

Paste Special Transpose Result

Transpose Function

To insert the TRANSPOSE function, execute the following steps.

1. First, select the new range of cells.

Select the New Range

2. Type in =TRANSPOSE(

3. Select the range A1:C1 and close with a parenthesis.

Transpose Function

4. Finish by pressing CTRL + SHIFT + ENTER.

Transpose Function in Excel

Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. To delete this array formula, select the range E2:E4 and press Delete.

Transpose Table without Zeros

The TRANSPOSE function in Excel converts blank cells to zeros. Simply use the IF function to fix this problem.

1. For example, cell B4 below is blank. The TRANSPOSE function converts this blank cell to a zero (cell G3).

Transpose Table

2. If blank, the IF function below returns an empty string (two double quotes with nothing in between) to transpose.

Transpose Table without Zeros

Transpose Magic

The 'Paste Special Transpose' option is a great way to transpose data but if you want to link the source cells to the target cells, you need a few magic tricks.

1. Select the range A1:E2.

2. Right click, and then click Copy.

Copy Data

3. Select cell A4.

4. Right click, and then click Paste Special.

5. Click Paste Link.

Click Paste Link

Result.

Paste Link Result

6. Select the range A4:E5 and replace all equal signs with xxx.

Replace All

Result.

Ready to Transpose

7. Use 'Paste Special Transpose' to transpose this data.

Transposed Data

8. Select the range G1:H5 and replace all occurrences of 'xxx' with equal signs (the exact opposite of step 6).

Transpose Magic

Note: for example, change the value in cell C2 from 16 to 36. The value in cell H3 will also change from 16 to 36.

Sours: https://www.excel-easy.com/examples/transpose.html

Excel TRANSPOSE Function

The TRANSPOSE function converts a vertical range of cells to a horizontal range of cells, or a horizontal range of cells to a vertical range of cells. In other words, TRANSPOSE "flips" the orientation of a given range or array:

  1. When given a vertical range, TRANSPOSE converts it to a horizontal range
  2. When given a horizontal range, TRANSPOSE converts it to a vertical range

When array is transposed, the first row becomes the first column of the new array, the second row becomes the second column of the new array, the third row becomes the third column of the new array, and so on.

TRANSPOSE can be used with both ranges and arrays. Transposed ranges are dynamic. If data in the source range changes, TRANSPOSE will immediately update data in the target range.

Examples

When given a vertical array, TRANSPOSE returns a horizontal array:

=TRANSPOSE({"a";"b";"c"})// returns {"a","b","c"}

To transpose the vertical range A1:A5 into a horizontal array:

To transpose the horizontal range A1:E1 to a vertical array:

In the example shown above, the formulas in I5 and F12 are:

Note: TRANSPOSE does not carry over formatting. In the example shown, the target ranges have been formatted in a separate step.

TRANSPOSE with other functions

TRANSPOSE can be used to "catch" and transpose the output from another function. The formula below changes the result from XLOOKUP from a horizontal configuration to a vertical configuration:

Read more: XLOOKUP wildcard example.

Excel 365

In Excel 365, which supports dynamic array formulas, no special syntax is required, TRANSPOSE simply works and results spill into destination cells automatically. However, in other versions of of Excel, TRANSPOSE must be entered as a multi-cell array formula with control + shift + enter:

  1. First select the target range, which should have the same number of rows as the source range has columns, and the same number of columns as the source range has rows.
  2. Enter the TRANSPOSE function, and select the source range as the array argument.
  3. Confirm the formula as an array formula with control + shift + enter.

Paste special

The TRANSPOSE function makes sense when you need a dynamic solution that will continue to update when source data changes. However if you only need a one-time conversion, you can use Paste Special with the Transpose option. This video covers the basics of Paste Special.

Sours: https://exceljet.net/excel-functions/excel-transpose-function
  1. Wrinkle quotes funny
  2. Samsung i9220
  3. Revit 360
  4. Minecraft ingot

Sometimes you need to switch or rotate cells. You can do this by copying, pasting, and using the Transpose option. But doing that creates duplicated data. If you don't want that, you can type a formula instead using the TRANSPOSE function. For example, in the following picture the formula =TRANSPOSE(A1:B4) takes the cells A1 through B4 and arranges them horizontally.

Original cells above, cells with TRANSPOSE function below

Note: If you have a current version of Microsoft 365 , then you can input the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, input the formula in the top-left-cell of the output range, then press Ctrl+Shift+Enter to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array formulas, see Guidelines and examples of array formulas.

Step 1: Select blank cells

First select some blank cells. But make sure to select the same number of cells as the original set of cells, but in the other direction. For example, there are 8 cells here that are arranged vertically:

Cells in A1:B4

So, we need to select eight horizontal cells, like this:

Cells A6:D7 selected

This is where the new, transposed cells will end up.

Step 2: Type =TRANSPOSE(

With those blank cells still selected, type: =TRANSPOSE(

Excel will look similar to this:

=TRANSPOSE(

Notice that the eight cells are still selected even though we have started typing a formula.

Step 3: Type the range of the original cells.

Now type the range of the cells you want to transpose. In this example, we want to transpose cells from A1 to B4. So the formula for this example would be: =TRANSPOSE(A1:B4) -- but don't press ENTER yet! Just stop typing, and go to the next step.

Excel will look similar to this:

=TRANSPOSE(A1:B4)

Step 4: Finally, press CTRL+SHIFT+ENTER

Now press CTRL+SHIFT+ENTER. Why? Because the TRANSPOSE function is only used in array formulas, and that's how you finish an array formula. An array formula, in short, is a formula that gets applied to more than one cell. Because you selected more than one cell in step 1 (you did, didn't you?), the formula will get applied to more than one cell. Here's the result after pressing CTRL+SHIFT+ENTER:

Result of formula with cells A1:B4 transposed into cells A6:D7

Tips

  • You don't have to type the range by hand. After typing =TRANSPOSE( you can use your mouse to select the range. Just click and drag from the beginning of the range to the end. But remember: press CTRL+SHIFT+ENTER when you are done, not ENTER by itself.

  • Need text and cell formatting to be transposed as well? Try copying, pasting, and using the Transpose option. But keep in mind that this creates duplicates. So if your original cells change, the copies will not get updated.

  • There's more to learn about array formulas. Create an array formula or, you can read about detailed guidelines and examples of them here.

Technical details

The TRANSPOSE function returns a vertical range of cells as a horizontal range, or vice versa. The TRANSPOSE function must be entered as an array formula in a range that has the same number of rows and columns, respectively, as the source range has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array or range on a worksheet.

Syntax

TRANSPOSE(array)

The TRANSPOSE function syntax has the following argument:

  • array    Required. An array or range of cells on a worksheet that you want to transpose. The transpose of an array is created by using the first row of the array as the first column of the new array, the second row of the array as the second column of the new array, and so on. If you're not sure of how to enter an array formula, see Create an array formula.

See Also

Transpose (rotate) data from rows to columns or vice versa

Create an array formula

Rotate or align cell data

Guidelines and examples of array formulas

Sours: https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027

If you have a worksheet with data in columns that you want to rotate so it’s rearranged in rows, you can use the Transpose feature. It lets you rotate the data from columns to rows, or vice versa.

For example, if your data looks like this, with sales regions listed along the top and quarters along the left side:

Regional data in columns

You can rotate the columns and rows to show quarters along the top and regions along the side, like this:

Regional data in rows

Here’s how:

  1. Select the range of data you want to rearrange, including any row or column labels, and either select CopyCopy icon on the Home tab, or press CONTROL+C.

    Note:  Make sure you copy the data to do this. Using the Cut command or CONTROL+X won’t work.

  2. Select the first cell where you want to paste the data, and on the Home tab, click the arrow next to Paste, and then click Transpose.

    On the Home tab, select Paste

    Pick a spot in the worksheet that has enough room to paste your data. The data you copied will overwrite any data that’s already there.

  3. After rotating the data successfully, you can delete the original data.

Tips for transposing your data

  • If your data includes formulas, Excel automatically updates them to match the new placement. Verify these formulas use absolute references—if they don’t, you can switch between relative and absolute references before you rotate the data.

  • If your data is in an Excel table, the Transpose feature won’t be available. You can convert the table to a range first by using the Convert to Range button on the Table tab, or you can use the TRANSPOSE function to rotate the rows and columns.

  • If you want to rotate your data frequently to view it from different angles, consider creating a PivotTable so you can quickly pivot your data by dragging fields from the Rows area to the Columns area (or vice versa) in the PivotTable Field List.

Sours: https://support.microsoft.com/en-us/office/transpose-data-from-rows-to-columns-or-vice-versa-in-excel-for-mac-9c16dd55-ed1a-4aa2-8b74-b1b9211e2ede

Transpose excel

If you have a worksheet with data in columns that you need to rotate to rearrange it in rows, use the Transpose feature. With it, you can quickly switch data from columns to rows, or vice versa.

For example, if your data looks like this, with Sales Regions in the column headings and and Quarters along the left side:

Regional data in columns

The Transpose feature will rearrange the table such that the Quarters are showing in the column headings and the Sales Regions can be seen on the left, like this:

Regional data in rows

Here’s how to do it:

  1. Select the range of data you want to rearrange, including any row or column labels, and press Ctrl+C.

    Note: Ensure that you copy the data to do this, since using the Cut command or Ctrl+X won’t work.

  2. Choose a new location in the worksheet where you want to paste the transposed table, ensuring that there is plenty of room to paste your data. The new table that you paste there will entirely overwrite any data / formatting that’s already there.

    Right-click over the top-left cell of where you want to paste the transposed table, then choose TransposeTranpose button image.

    Paste Options menu

  3. After rotating the data successfully, you can delete the original table and the data in the new table will remain intact.

Tips for transposing your data

You can paste data as transposed data within your workbook. Transpose reorients the content of copied cells when pasting. Data in rows is pasted into columns and vice versa.

An example of Paste Transpose

Here's how you can transpose cell content:

  1. Copy the cell range.

  2. Select the empty cells where you want to paste the transposed data.

  3. On the Home tab, click the Paste icon, and select Paste Transpose.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Sours: https://support.microsoft.com/en-us/office/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744

Transpose Excel data from rows to columns, or vice versa

Here's a tip that eliminates the need to rekey data. Suppose you've entered your data with three column headings running across Row 1 and four row headings running down Column A, like the ones shown in Figure A.

Figure A

transpose

After working with the data for a while, you decide you'd rather have the current set of row labels (months) running across the columns. Whatever you do, don't even think about rekeying the data.

You'll find the best solution on the Paste Special menu. Start by selecting and copying your entire data range. Click on a new location in your sheet, then go to Edit | Paste Special and select the Transpose check box, as shown in Figure B. Click OK, and Excel will transpose the column and row labels and data, as shown in Figure C.

LEARN MORE: Office 365 Consumer pricing and features

Figure B

paste special

Figure C

insert data

Note

You aren't limited to using the Paste Special | Transpose option to rearrange multiple rows and columns of data. It works just as well when you need to turn a single row of labels into a column, or vice versa.

Affiliate disclosure: TechRepublic may earn a commission from the products and services featured on this page.

Sours: https://www.techrepublic.com/blog/microsoft-office/transpose-excel-data-from-rows-to-columns-or-vice-versa/

You will also like:

.



1589 1590 1591 1592 1593