Transposing data in Excel

Kelly L. Williams, CPA, Ph.D.

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 regret being unable to individually answer all submitted questions.

Where to find March’s flipbook issue

The Journal of Accountancy is now completely digital. 





Get Clients Ready for Tax Season

This comprehensive report looks at the changes to the child tax credit, earned income tax credit, and child and dependent care credit caused by the expiration of provisions in the American Rescue Plan Act; the ability e-file more returns in the Form 1040 series; automobile mileage deductions; the alternative minimum tax; gift tax exemptions; strategies for accelerating or postponing income and deductions; and retirement and estate planning.