Data Import & Export

How to Swap Rows and Columns in Excel (Transpose Data)

Aamir Naveed Akram Sep 28, 2026 870 views

Question details

The user needs to switch the orientation of their data table by converting horizontally arranged data into vertical columns and vice versa, without manually retyping the schedule.

Product: Excel
Device & OS: not provided
Scenario: Reorganizing an existing schedule or data table by switching its horizontal and vertical layout.
Observed behavior: Data that is currently displayed in horizontal rows needs to be rearranged into vertical columns, and vertically arranged numbers into horizontal rows.

Before you start

Ensure you have enough empty cells in the destination area to accommodate the transposed data to prevent accidentally overwriting your existing information.

Solution 1Recommended

Use Paste Special to Transpose Data

The fastest way to swap rows and columns in Excel is by copying the data range and utilizing the Transpose option within the Paste Special menu.

This built-in feature instantly rotates your data by 90 degrees. It converts rows into columns and columns into rows while retaining your original text, values, and cell formatting.

Select and copy the data

Highlight the entire data range you want to swap. Right-click the selection and click 'Copy', or press Ctrl+C on your keyboard.

Select a destination cell

Right-click a new, empty cell where you want the top-left corner of the transposed data to start. Make sure this new location does not overlap with your original data.

Apply Transpose from Paste Special

In the right-click context menu, click on 'Paste Special'. In the dialog box that appears, check the box for 'Transpose' at the bottom, and click 'OK'.

Keep Original Data Intact: By pasting the transposed table into a different blank location, your original worksheet data remains unchanged. Once you confirm the new layout is correct, you can delete the old data.

Efficient Data Management

Easily Transpose Data with WPS Spreadsheet

WPS Spreadsheet offers the exact same intuitive Paste Special and Transpose features as Microsoft Excel, allowing you to instantly swap rows and columns for free.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the worksheet containing the rows and columns you want to swap.
2. Copy your data: Highlight the cells you need to reorganize and press Ctrl+C to copy the original schedule.
3. Paste and transpose: Right-click an empty destination cell, select 'Paste Special', check the 'Transpose' option, and click 'OK' to finish.

One-click Transpose feature in the right-click menu for quick data reorganization.Fully compatible with Microsoft Office Excel formats (.xlsx, .xls).Free, lightweight, and user-friendly interface that requires no learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the Transpose option missing or grayed out?

The Transpose feature only becomes active when you copy the data (Ctrl+C). If you use the Cut command (Ctrl+X) instead of Copy, the Transpose option will not be available in the Paste Special menu.

Can I link the transposed data so it updates automatically?

Yes. Instead of using Paste Special, you can use the TRANSPOSE function. Select a blank range with the inverted dimensions of your original data, type =TRANSPOSE(original_range), and press Ctrl+Shift+Enter.

Does transposing data keep my original cell formatting?

Yes, when you use the Paste Special > Transpose method, your cell formatting, such as background colors, fonts, and borders, will be transposed along with the data values.

Excel & Data Problems