Excel provides two methods for transposing data. Whether you need a live link might be the condition that helps you choose the right one.
Editor's note: Instructions and figures are for Office 2010. Instructions for 2003, 2007, and 2013 are given when significantly different.
There are two ways to transpose data. You can use the Paste feature, which is easy or the TRANSPOSE() function, which is more complex. The big difference is that there's no link between the two ranges in the pasted version; if you change values in one, the other doesn't update accordingly. If you need a live exchange between the two ranges, you'll want to use the more complex function solution.
When a quick look is all you need, rely on the Paste option. To illustrate this easy route, we'll use it to transpose the simple data range shown below as follows:
- Select the data range that you want to transpose and copy it to the Clipboard by pressing [Ctrl]+c. For this example, select A2:F15. You can include the titles (in row 1) if you like.
- Select the top-left cell of the destination range. In this case, select A18.
- Click the Home tab (if necessary) and then click the Paste dropdown in the Clipboard group. In Excel 2003, choose Paste Special from the Edit menu.
- Click Transpose (the last icon on the second row). In Excel 2003, check the Transpose option in the resulting dialog.
It's an incredibly simple process considering the results.
Paste offers a quick and easy way to flip your data, but as I mentioned, it isn't live. If you change a value in either range, Excel won't update the other range accordingly. If that matters, use Excel's TRANSPOSE() function. Doing so requires a bit more work, but the result will be linked ranges.
First, you need to know how many rows and columns the transposed data will require. That's easy with a relatively small range. If a visual count isn't practical, you can try a couple of easy counting methods:
- COLUMNS() and ROWS() will return the number of columns and rows, respectively, in a reference.
- Use VBA's COUNT method, as shown in Counting Excel rows, columns, and sheets.
Our example data range has 6 columns and 14 rows. To transpose the data, you'll select a range that's 14 columns and 6 rows - A17:N22. In the selection's active cell (the top-left cell) type the following function but do not press [Enter]:
To enter this function as an array, press [Shift]+[Ctrl]+[Enter]. Doing so transposes the range, but the result is an array - notice the curly brackets around the function in the Formula bar.
Unfortunately, this process won't copy formats, but that's a small price to pay for the linked ranges. For instance, when I copied the SUM() function to the month of August in the original data, Excel updated the respective cell in the transposed range. Once you have the transposed range, you can format it, or not, as needed.
To delete the fixed range you created using Paste, you simply select the range and press [Delete] as you would any other data range. Deleting an array isn't quite as simple. You must select the entire array to delete it. In this example, that's not particularly difficult because it's small, but that won't always be the case.
An easy way to select an array when you don't know its exact dimensions is to click anywhere inside the array and press [F5] to display the Go To dialog. Click Special, and in the resulting dialog, click Current Region or Current Array. Or, even simpler, press [Ctrl]+[Shift]+8. Either route will select the array for you. Then, you can press [Delete].