How to switch columns and rows in Excel [Swap / Transpose]

Vlad Turiceanu
by Vlad Turiceanu
Editor-in-Chief
Passionate about technology, Windows, and everything that has a power button, he spent most of his time developing new skills and learning more about the tech world. Coming from a solid background in PC... Read more
Affiliate Disclosure
  • Microsoft Excel is the foremost program for creating, editing, and viewing spreadsheets.
  • You can switch data organized in rows to columns and vice versa using the Transpose function.
  • For more useful guides on a variety of topics visit our dedicated How to section.
  • Don't hesitate to visit our Microsoft Excel webpage to discover more details on this topic.
how to switch columns and rows in excel
To fix various PC problems, we recommend DriverFix:
This software will keep your drivers up and running, thus keeping you safe from common computer errors and hardware failure. Check all your drivers now in 3 easy steps:

  1. Download DriverFix (verified download file).
  2. Click Start Scan to find all problematic drivers.
  3. Click Update Drivers to get new versions and avoid system malfunctionings.
  • DriverFix has been downloaded by 0 readers this month.

People interpret data differently; some insert data horizontally and others prefer to do it vertically. For flexibility, Microsoft Excel comes with the Transpose feature, which lets you switch columns and rows in Excel.

The Transpose function in Excel changes your columns and rows’ orientation. It allows you to perform this operation in two different ways – either with the paste options or by using a formula.

What is the best practice to switch the columns and rows in a range in Excel?

  1. If you have formulas on the sheet, the data on the table must be absolute references in order to switch columns and rows in Excel.
  2. To perform this operation regularly and view the data from diverse angles, you may want to create a PivotTable. It simplifies the process of pivoting your data by letting you drag and drog data between fields and columns.

If you are working with an Excel file with lots of headings stacked horizontally, it gets clumsy to read or edit.

To switch the columns and rows in Excel, you have to either use paste options in the program or go with a formula as explained in this guide.

How can I change columns to rows in Excel?

1. Use paste and Transpose

switch-columns-and-rows-in-Excel-switch-columns-and-rows-in-Excel

  1. Open the Excel sheet that you want to work on.
  2. Highlight the data range that you want to transpose, as well as the labels of the column or row.
  3. Press the CTRL + C combination to copy the selection.
  4. Navigate to the destination row or column where you wish to apply the copied values.
  5. Right-click the topmost left cell and select the Transpose icon from PASTE OPTIONS.
  6. Return to the origin cell and delete the data.

2. Use the Transpose function in a formula

transpose-formula-switch-columns-and-rows-in-Excel

  1. Open the spreadsheet.
  2. Select an empty cell to insert the converted data.
  3. Type =transpose into the selected cell.
  4. Add the range of cells that you want to transpose into a bracket after =transpose. So, it looks like this – =transpose(A1:F7).

Note: In the above formula, A1 is the starting cell and F7 is the ending one in the range. This technique makes use of an array and formula. Unlike the first method, switching columns and rows with this method can ruin the formatting.


This article covers:Topics: