Transpose an Excel table

Swapping rows and columns

Transposing a table means turning the rows to columns and visa versa. Excel can transpose a table using either copy and paste; an array function or regular worksheet functions. The best method to choose can be determined by the size of the table and whether this is a one - off or a frequently occuring task.

In the adjacent picture a table (Q5:V8) has been transposed to create a new table (X4:AA9) so that the weekdays move from being columns to rows and the mealtimes move from rows to columns. In this instance the table was copied and then Paste Special | Transpose into cell X4. If the table only contains real values (text and numbers) this presents no problem. If the table contains references and formulas more care is needed. It is essential that any cell references in the original table are absolute (e.g. '=$K$3') rather than relative (e.g. '=K3').

If you have a fixed size table which may contain formulas with relative references, another option is to use an array.

		1. Identify the table to be transposed. This can contain values, relative or absolute references.
2. Highlight the area to receive the transposed table. If the original source is 4 rows x 6 columns the highlighted area must be 6 rows x 4 columns. Type the formula =TRANSPOSE($AC$11:$AH$14) - referring to the range of the source table. The range must be entered as Absolute references. This is an array formula and must be entered using <Ctrl> + <Shift> + <Enter> rather than just the single <Enter> key. Curly braces { } are added around the formula by Excel to indicate that it is an array formula.
		3. Once the formula is entered using the special array keys, the whole selected range is filled with the appropriate transposed formulas. Excel will not allow you to edit or change any part of this new array table. You cannot add or delete rows or columns. It will however update to reflect changes in the original source cells.

A third option is to use regular functions to calculate the references of the original source table. This can appear complicated with apparently duplicated ROW() and COLUMN() components. However, some experimenting will reveal that they are all required. This method has the advantage that it can be modified to accept new rows and columns and unwanted cell formulas can be deleted. I have shown two alternative formulas that should both do the same thing. The INDIRECT() / ADDRESS() method may be more memory efficient than using OFFSET() with very large tables.

This simple source table starts in cell B5. The type of contents in each cell within the table doesn't matter because this will use formulas to refer back to the original source cells. Copy the cells B5:F8 from the table below and paste them into cell B5 in an Excel worksheet.

To create a new transposed table, copy either of the following formulas and paste it into another cell in the Excel worksheet.

In Excel, copy the cell formula down and across to create a transposed table. (Copy the newly entered formula from the cell rather than pasting the same formulas from above.)

The information on this page is not included in the Excel Function Guide

Click here for details about obtaining the file