EXTRA CREDIT

How to easily transpose data in Excel

By Wendy Tietz, CPA, CGMA, Ph.D.; Jennifer Cainas, CPA, DBA; and Tracie Miller-Nobles, CPA

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:

ec-1-transpose-data-tax-return-items-left

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:

ec-2-transpose-data-tax-return-items-top

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.

ec-3-transpose-data-paste-special

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

ec-4-transpose-data-results

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.

ec-5-transpose-data-home-tab-paste

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.

Where to find October’s flipbook issue

The Journal of Accountancy is now completely digital. 

 

 

 

SPONSORED REPORT

2022 Payroll Update

Employees working remotely have created numerous issues for employers. The 2022 Payroll Update report provides insight on remote workforce tax issues, pandemic payroll issues and employer credits, and worker classification issues in the gig economy.