How to Make Excel Data Horizontal
By B. Steele
When working with Excel spreadsheets, you might decide that a certain range of data would be more useful to you if it was displayed across a horizontal axis rather than vertically in columns. You don’t need to re-enter all your data or cut and paste each cell individually. Instead, you can convert a vertical range into a horizontal one by using the “Transpose” special paste option or by using the “Transpose” Excel function.
Pasting Data as a Transposed Range
Click and drag to highlight the entire range of vertically-oriented cells you’d like to transpose.
Press “Ctrl-C” to copy the cells to the Windows clipboard.
Highlight the first cell in the destination location. You must choose a location outside of the original array because the “paste” location cannot overlap the original data.
Click the “Paste” drop-down menu and select the “Transpose” option. If you wish, you can delete the original data by highlighting the cells (or the column letter if you want to delete the entire column at once) and pressing “Delete.” In most cases, any formulas in the pasted data will automatically adjust.
Using the Transpose Function
Highlight an empty horizontal range of cells in the destination location equal in number to the vertical cell range you want to transpose. For example, if you want to transpose cells A1-A3 horizontally, you need to highlight three adjacent horizontal cells, such as B1-D1.
Click once in the formula bar and type "=TRANSPOSE(A1:A3)" (without quotation marks), substituting the cell range given for the range applicable to your vertically-oriented data.
Press “Ctrl-Shift-Enter” to paste the vertical data across the horizontal range selected. Delete the original data if desired.
Formulas in transposed ranges may break if they don’t refer to absolute cell locations. Indicated by preceding dollar symbols, an absolute cell reference refers to a location that will not change no matter where the formula is pasted. For example, the formula “=(A1+B1)” is a relative cell reference because it will change to “=(A2+B2)” if you paste it into the next row down. However, “=($A$3+$B$3)” is an absolute cell reference and will only refer to cells A3 and B3 no matter where you paste the formula.
A writer and proofreader since 2006, B. Steele also works as an IT Help Desk analyst, specializing in consumer and business user tech support. She earned a B.A. in English and journalism from Roger Williams University. Steele also holds certifications as a Microsoft-certified desktop support technician, Microsoft-certified IT professional, Windows 7 enterprise support technician and CompTIA A+ IT technician.