How to transpose a table and link it to the original data No one would want to waste their time on converting the same rows and columns over and over again, right? Whenever the source data change, you'd need to repeat the process and rotate the table anew.
Paste Special > Transpose does not link the new table with the original data, so it is well suited only for one-time conversions. In this case, you need either to copy the table without column headers or convert it to a range first. If you copy the whole table and then open the Paste Special dialog, you will find the Transpose option disabled. 
It is not well suited for rotating fully-functional Excel tables. However, this approach has two drawbacks that prevent it from being called a perfect solution for transposing data in Excel: This method also copies the formatting of your original data, which adds one more argument in its favor. If your source data contain formulas, be sure to properly use relative and absolute references depending on whether they should be adjusted or remained locked to certain cells.Īs, you have just seen, the Paste Special feature lets you perform row to column (or column to row) transformations literally in a few seconds. Right click the destination cell and choose Paste Special from the context menu, then select Transpose.įor more information, please see How to use Paste Special in Excel. For example, if you currently have 4 columns and 10 rows, the converted table will have 10 columns and 4 rows. Select the first cell of the destination range.īe sure to select a cell that falls outside of the range containing your original data, so that the copy areas and paste areas do not overlap. Copy the selected cells either by right clicking the selection and choosing Copy from the context menu or by pressing Ctrl + C. all the cells with data in a spreadsheet, press Ctrl + Home and then Ctrl + Shift + End. To switch rows to columns, performs these steps: The country names are organized in columns, but the list of countries is too long, so we'd better change columns to rows for the table to fit within the screen: Suppose you have dataset similar to what you see in the upper part of the graphics below. Convert columns to rows using the Transpose toolĬonvert rows to columns in Excel using Paste Special. Transpose a table and link it to the original data. Convert row to column with Paste Special. In this article, you will find several ways to convert rows to columns (or columns to rows), whichever you call it, it's the same thing : ) These solutions work in all versions of Excel 2016, 2013, 2010, 2007 and lower, cover many possible scenarios, and explain most typical mistakes. Quite often you build a complex table only to realize that it makes perfect sense to rotate it for better analysis or presentation of data in graphs. Transposing data in Excel is the task familiar to many users. 
The tutorial shows how to convert row to column in Excel using the Paste Special feature, TRANSPOSE function, VBA code, and a special tool.
0 kommentar(er)
