How to Transpose Data in Microsoft Excel

by
Spread the love

After you add data to your spreadsheet, you may realize that the data would work better in a different layout. You may want to turn columns into rows or vice versa. This guide shows you how to transpose data in Microsoft Excel using two different methods.

Content

Use Copy and Paste Special to Transpose Data

An easy way to transpose data in Excel is by using the Paste Special feature. For example, you can copy data in a column, then paste it as a row without manually moving the column data.

  1. Select the data you want to transpose. Do this easily by dragging your cursor through the cells.

  1. Either right-click one of the selected cells and select “Copy” or click “Copy” in the Clipboard section of the ribbon on the “Home” tab.

  1. Select the starting cell where you want to place the data. You should choose a different spot in your sheet that is not over the top of the existing data. This prevents errors and allows you to confirm that the data transposes correctly without overwriting any data.

  1. Either right-click the cell, select “Paste Special,” and choose “Paste Special” in the pop-out menu or click the “Paste Special” drop-down menu on the “Home” tab and select “Paste Special.”

  1. In the Paste Special box, you can select a particular “Paste” option at the top if you would like. To paste the data as is, choose “All.” You can also select an “Operation” near the bottom of the box if you want to add or subtract the data you paste.

  1. At the bottom of the box, check the box for “Transpose” and click “OK” to paste the data.

  1. You’ll see your data pasted from a column to a row or vice versa depending on your selection. You can then remove the original data if you would like.

Tip: you can further clean up your data by splitting or combining text in Excel.

Use the TRANSPOSE Function in Excel

Another way to transpose data in Excel is by using the TRANSPOSE function. This option works the same as the actions above; however, you can use the function to transpose table data if needed. The Paste Special Transpose feature not doing this is a current limitation.

The syntax for the formula is TRANSPOSE(range), where the cell references are entered for the range or array you want to transpose.

  1. Select the starting cell where you want to place the data, then enter the formula for the TRANSPOSE function. As an example, we are transposing the range A1 through C7 from our table. Here’s the formula:
=TRANSPOSE(A1:C7)
  1. If you’re using Excel for Microsoft 365, press Enter when you see the preview.

  1. If you’re using a different version of Excel, press Ctrl + Shift + Enter to place curly brackets around the formula, as the function uses an array formula that isn’t supported the same way across Excel versions.

  1. You’ll see the column data placed in a row or vice versa. Like the above method, you can remove the original data if you wish.

Note: you can filter tables in Excel using the Slicer feature.

Frequently Asked Questions

Do formulas automatically update when I transpose data?

When you use relative references for your data, the Excel formulas automatically update using both transpose methods above. However, if you use absolute references, the formula may not update as you’d expect or might produce errors.

Can I transpose filtered data in Excel?

If you have data filtered that you want to transpose, you can do so using the Paste Special option. Although you can use the TRANSPOSE function by selecting only the visible, filtered cells for the formula, the result will likely be incorrect.

How to I undo transposing data in Excel?

If you transpose the data using one of these methods and decide the data worked better the original way, you can revert the change. Select the “Undo” button in the Quick Access Toolbar or open the “Undo” button’s drop-down menu to reverse several actions if needed.

Keep in mind that you if you save and close the Excel file, the Undo option is no longer available for actions before you saved and closed it. In this case, you can transpose the data in reverse using the original method.

Image credit: Pixabay. All screenshots by Sandy Writtenhouse.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

Sign up for all newsletters.
By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time. Subscribe


Sandy Writtenhouse

With her BS in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She wanted to help others learn how technology can enrich business and personal lives and has shared her suggestions and how-tos across thousands of articles.

Comments are closed