Transposing data in Excel
Q. One of our clients sent me some data that is horizontal, but it needs to be vertical to load into our system. Is there an easy way to convert the data to the required format?
A. You can easily convert horizontal data to vertical and vertical data to horizontal by using the Transpose feature in Excel. Here’s a simple example of how Transpose works.
Let’s say that we want to take the vertical data in column E in the screenshot below and transpose it so it runs horizontally. To do that, copy cells E2:E11, place the cursor in the first cell where the data should be pasted horizontally, and then click the drop-down arrow under Paste in the Clipboard group on the Ribbon’s Home tab (or you can simply right-click on the destination cell). Either way, the next step is to choose Paste Special, which opens the dialog box shown on the right side of the screenshot.
Check the box beside Transpose, and click OK. The vertical data is now pasted horizontally, as shown in the screenshot at the bottom of the page.
You can download the workbook shown above. You can view an accompanying video with the online version of this article below.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org.We regret being unable to individually answer all submitted questions.
