There are three ways to transpose data in Excel: First, use "Copy and Paste Special" to quickly transpose, which is suitable for static data. The operation steps are to select the data area → Copy → Right-click to select "Paste Special" → check "Transpose"; the second is to use the TRANSPOSE function to implement dynamic reference, which is suitable for scenarios that require linkage. The operation steps are to select the target area → Input = TRANSPOSE (original range) → Press Ctrl Shift Enter; the third is to automatically process batches through Power Query, which is suitable for people who often process similar data. The steps include importing data → inverse perspective other columns → perspective columns. Each method has its own applicable scenarios. One-time operation can be copied and pasted. If data linkage is required, use functions. It is recommended to use Power Query for a long time.

Transferring data in Excel (that is, turning rows into columns, columns into rows) is actually quite common, such as when organizing table structures or preparing reports. The key is to know several different methods and choose the most suitable one according to the specific situation.

Quick transpose with Copy Paste Special

This is the most basic and most commonly used method, suitable for processing static data at one time.

Operation steps:

• Select the data area to be transposed
• Press Ctrl C to copy
• Find a blank area and right-click the cell
• Select "Paste Special" → Check "Transfer"

This will directly display the original horizontal data vertically, and vice versa. It should be noted that this method is "one-time". If your original data changes, the transposed ones will not be automatically updated.

Dynamic reference with formulas (suitable for scenarios that require linkage)

If you want the transposed data to be automatically updated as the original data changes, you can use the TRANSPOSE function.

How to use:

1. Look at how many rows and columns are there in the area you want to transpose
2. Select the corresponding number of reverse rows and columns in the blank area (for example, the original data is 3 rows and 4 columns, select 4 rows and 3 columns)
3. Enter =TRANSPOSE(原始范圍) , for example =TRANSPOSE(A1:C4)
4. Press Ctrl Shift Enter (Excel will automatically add braces {} )

Note: This function generates an array formula and cannot modify the content of one of the cells separately.

Power Query Automated batch processing (for people who often want to process similar data)

If you often have to deal with data in similar formats, Power Query is a tricky way to save time.

The steps are briefly as follows:

• Import data into the Power Query Editor
• Select all columns, right-click to select "Inverse Perspective Other Columns"
• The data will become two columns: attributes and values
• Then use the "Perspective Column" function to rearrange the structure you need

Although it is a bit troublesome at the beginning, once the template is made, you only need to refresh it to automatically process the new data.

The above methods have their own applicable scenarios. If you simply process, use copy and paste; if you want to maintain data linkage, use the TRANSPOSE function; if you repeat operations for a long time, you can consider Power Query. Basically all this is it, not complicated but it is easy to ignore details, such as formula input method or options when pasting.

The above is the detailed content of how to transpose data in excel. For more information, please follow other related articles on the PHP Chinese website!