How to easily transpose data in Excel

Occasionally you may have a worksheet with data in columns that you want to rearrange into rows. Instead of reentering the data, you can easily rotate the data using the Transpose feature. The Transpose feature allows you to rotate data from columns to rows and vice versa from rows to columns without reentering any of the data.

For example, suppose you have the following data with Tax Return Items listed on the left-hand side and the tax return years listed across the top:

You would like your data to show the Tax Return Items across the top and the tax return years on the left-hand side, like this:

How to transpose

To transpose the data, start by selecting all the data you would like to transpose. Right-click with the data selected and click Copy. Alternatively, you can use the Ctrl+C shortcut (for Mac users it’s Command C) on your keyboard.

Right-click into the cell where you would like to copy your data. Click on the arrow next to Paste Special and then click on the Transpose icon.

Your data has now been transposed and is ready to work with.

An alternative method to transpose

Instead of using the shortcut menu, as shown above, you can also use the Paste option on the Home tab as follows. After copying the data, select the cell where you would like your copied data to appear. On the Home tab, click the arrow below Paste, and then click on the Transpose icon.

— Wendy Tietz, CPA, CGMA, Ph.D., is a professor of accounting at Kent State University in Kent, Ohio; Jennifer Cainas, CPA, DBA, is a clinical professor at the University of South Florida in Tampa; and Tracie Miller-Nobles, CPA, is an associate professor of accounting at Austin Community College in Austin, Texas. Their website, Accounting is Analytics, has resources they have developed for teaching data analytics in introductory accounting. To comment on this article or to suggest an idea for another article, contact Courtney Vien at Courtney.Vien@aicpa-cima.com.