OmniDocExcel transpose formula: functional method of swapping rows and columns
Use the TRANSPOSE function to interchange rows and columns in an Excel table, and explain how to deal with common problems such as target area size, dynamic array overflow, and blank cells. It also introduces two alternatives, paste special and PDF table import.
Key points of this article
- The TRANSPOSE function allows the results to change with the source data, which is suitable for tables that need to be continuously updated. Paste-specific transposition only results in static results.
- Before using the function, you must first calculate the size of the target area: the source table has m rows and n columns, and the target area must have n rows and m columns.
- In the new version of Excel, you only need to enter a formula in the upper left corner cell to automatically overflow; in the old version, you need to select the entire area first and then confirm by array method.
- If the target area already contains content, the formula will return a #SPILL! error, which can be solved by clearing the target area.
- Blank cells in the source table will be displayed as 0 in the transposition result. Merging cells will easily cause errors and needs to be processed in advance.
Many people encounter this situation when making tables: the data in a table is arranged by rows, but subsequent statistics or charts need to be arranged by columns, or vice versa. Manually copying and pasting cell by cell is slow and prone to misalignment. Excel provides the TRANSPOSE function, which can use a formula to swap the rows and columns of an entire range.
This article is written for people who need to adjust the direction of the table, such as changing the month from a horizontal row to a vertical column, or changing the questionnaire results from a vertical table to a horizontal table. After reading this, you'll know when to use the transpose formula, how to calculate the target range, what to do when you encounter #SPILL! or a 0 value, and how to accomplish the same operation without Excel.
How to use formulas to swap rows and columns in Excel? One sentence conclusion
Use the TRANSPOSE function, enter =TRANSPOSE(源区域) in the upper left corner of the target area, the number of rows in the target area is equal to the number of columns in the source area, and the number of columns is equal to the number of rows in the source area, you can get the result after the rows and columns are swapped. If it is just a one-time adjustment and the source data will not be changed later, it is easier to copy and paste and check Transpose.
| your situation | Recommended practices | reason |
|---|---|---|
| The source data will be modified later, and the results need to be updated simultaneously. | TRANSPOSE function | The result is a formula, which will be automatically refreshed after the source table is modified. |
| Organize once and do not change the source data later. | Copy and paste and check transpose | The operation is fast and no formulas are left, but the results are disconnected from the source table. |
| Older versions of Excel, which do not support automatic overflow | First select the target area, and then use the array method to confirm the formula | The old version requires pre-defined result ranges |
| The table is in PDF, there is no editable source file | First export the PDF table to Excel and then transpose | The transpose operation only works on data in Excel |
| The table contains a large number of merged cells | Unmerge and complete data first, then transpose | Merging cells will lead to irregular areas and prone to errors. |
Before you start: Confirm these points first
Confirm the shape of the source area first. Before transposing, count the number of rows and columns in the source table. For example, a table with 12 rows and 5 columns should have 5 rows and 12 columns after transposition. If the size of the target area is calculated incorrectly, rows or columns will be missing as a result.
Second, confirm that the target location is empty. The transposed result will occupy an entire area. If there is already data in the target area, the formula cannot be written and a #SPILL! error will be reported. It is recommended to place the results in a new worksheet or in a blank area to the right or below the source table.
Third, check whether the source table has merged cells. Merging cells will disrupt the corresponding relationship between rows and columns. Cancel the merge before transposing and fill the data into each cell. The result will be cleaner.
Fourth, determine the Excel version. Supports a new version of dynamic arrays. Enter the formula once in the upper left corner to automatically fill the entire area. In the old version, you need to select the area of the correct size first and then confirm it in array mode. Different versions have slightly different steps.
Fifth, confirm the source of the form. If the data comes from PDF, and the PDF contains selectable text or scans, it needs to be converted to Excel format before transposition. The PDF editor of OmniDoc desktop client (download.omnidoc.top) supports exporting PDF to Excel. After exporting, you can use the following method to process it. For tables exported from scanned copies, special numbers must be checked because errors may occur during the recognition process.
Steps to transpose a range using the TRANSPOSE function
- Note the scope of the source area. For example, the source data is from A1 to E12, that is, 12 rows and 5 columns.
- Select the upper left corner of the target location. In a blank worksheet, click A1 as the starting point for the results. The target area should be 5 rows and 12 columns extending from A1 to L5.
- Enter the formula. Enter
=TRANSPOSE(Sheet1!A1:E12)in the upper left corner of the target, and replace the worksheet name and range with your own. - Confirm your input. In the new version of Excel, just press Enter, and the results will automatically overflow to the entire area.
- Old versions require array confirmation. First select the area of 5 rows and 12 columns from A1 to L5, enter the formula and press Ctrl+Shift+Enter instead of pressing Enter. Curly brackets will appear around the formula to indicate that it is an array formula.
- Check the bounds of the result. Take a look to see if the lower right corner is exactly filled, with no extra or missing rows.
- When you need to fix the result, you can copy and selectively paste it as a numerical value. In this way, the results no longer depend on the source table, and are suitable for submission to others or as historical archives.
- Save the file. If the source table will be modified in the future, keep the formula version; if it is only for one-time use, save the numerical version.
Check each item after completion
| Check items | What do you think? | What to do if there is a problem |
|---|---|---|
| Is the number of rows and columns correct? | The number of rows in the target area should be equal to the number of columns in the source area, and vice versa | Re-count the source area, resize the target area before entering the formula |
| Whether or not # SPILL appears! | Is there an error prompt in the target area | Empty the original content in the target area, or change to another starting point |
| Whether the blank cell displays 0 | A blank space in the source table, whether there is a 0 in the result | Fill in the required content in the source table, or use functions such as IF to deal with null values in the outer layer |
| Whether the data is aligned or not | Select several cells at random and compare them with the source table | Check whether the source area contains headers or extra rows |
| Whether the results are synchronized after the source data is changed | Modify a value in the source table to see if the result changes | If it is not synchronized, the numerical version is used and the formula needs to be re-generated. |
| Whether the format of numbers and dates is retained | Check to see if the date, percentage, etc., display normally. | Reformat the cell after transpose |
If the transposed form has a large amount of data, you should pay special attention to the numbers when checking. For data in financial, medical or legal documents, it is recommended to compare it with the source table item by item, rather than just looking at the general shape.
# which method is suitable for you?
- * if you want to make reports that are constantly updated, such as adding data to the source table every month, use the TRANSPOSE function. After the source table is updated, the transposition result will change without having to operate repeatedly.
- if you just hand in a static form *, for example, organize the questionnaire results into a horizontal table and send them to colleagues, copy, paste and check transpose is more direct. After completion, the result is the numerical value, which will not change due to the mischange of the source table.
- if you only have the PDF version of the table *, first export to Excel using the PDF editor of the OmniDoc desktop client, and then follow the above method to transpose it. For scanned pieces, carefully check each number after export.
- if you want to exchange tables between different software *, for example, if you need to put Excel data into an Word document, you can transpose it first, and then copy the results. Note that table styles may vary from software to software. Check the layout again after pasting.
# frequently asked questions (FAQ)
1. Can the transposed data be edited again?
The result generated with TRANSPOSE is a whole formula region, you can't change only one of the cells, it will report an error. If you need to edit it case by case, copy the results first, then paste them selectively as numeric values, and then you are free to modify them.
2. Can the transpose formula be used for text content?
Sure. TRANSPOSE works for numbers, text, and dates, but it only swaps rows and does not change the content itself. Newline characters and formatting in text may need to be checked separately.
3. Why are there so many zeros after transposition?
This is usually a blank cell in the source table. TRANSPOSE returns whitespace as zero. You can fill in the source table, or use the IF function to convert 0 to a blank display.
4. Can you directly use the mouse to complete the transposition without using the formula?
Sure. Copy the source area first, then turn on the paste option at the target location, and select the paste method with only paste values or with transpose function to complete the transposition. This method does not need to remember the formula, but the result is no longer associated with the source table.
5. What if the table copied from PDF is always misplaced when transposed?
When a PDF table is copied to Excel, there are often merged cells or extra empty columns. Import the data into a clean worksheet, delete the extra empty rows and columns, and then transpose it.
# Summary
The core of Excel column-column swap is to calculate the size of the quasi-source area and the target area first, and then use the TRANSPOSE function or copy-paste transpose function to complete the transpose. Use the formula when you need to update continuously, use numerical paste only when you do one-time finishing, and check the rows and rows, null values and number format item by item after finishing, so as to ensure the accuracy of the table.
