Have you ever needed to change the orientation of a range of cells whilst maintaining links to the source data and end up having to manually recreate the spreadsheet? Here’s a quick and easy way using the Microsoft® Excel® TRANSPOSE function. The TRANSPOSE function returns a vertical range of cells as a horizontal range, or vice versa, whilst maintaining links to source data.
The TRANSPOSE function must be entered as an array formula. An array formula performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between brackets { } and are entered by pressing (CTRL+SHIFT+ENTER) in a range that has the same number of rows and columns as the source range. Use TRANSPOSE to shift the vertical and horizontal orientation of an array or range on a worksheet.
You are welcome to download the workbook to practice.
NB: Applies To: Microsoft® Excel® 2010, 2013 and 2016.
- Starting from cell A7- select the data range A7:C18 (Where the data will be pasted).
- With the data range A7:C18 still selected, type the formula below.
=TRANSPOSE(A2:L4) and press CTRL+SHIFT+ENTER.
- A2:L4 is the source data range arranged horizontally.
- A7:C18 is the target range arranged vertically.
The data will be quickly re-arranged, thus saving you time of re-capturing in the desired orientation. Furthermore, links will be maintained to the source data, making it easy to update both ranges simultaneously.
You can also transpose data by using COPY-PASTE SPECIAL-TRANSPOSE–OK. However the transposed data will have no links to the source data
