2 Easy Ways to Transpose Data in Excel

transposing data in

If you want to transpose data in Excel to convert rows to columns or the inverse, then Excel has already made it easier for you to do it.

Transposing vertical data to horizontal data or horizontal data to vertical data can be done using two methods. This article is a step-by-step guide to transpose data in excel.

Recommended read: How to group rows and columns in Excel?

Steps to transpose in Excel

Let’s get started with the steps to transpose data in Excel. There are two ways to transpose data in Excel, one, transpose with paste special, and two, using the TRANSPOSE function.

Transposing data with Paste Special

First, we take an example of a vertical database to further transpose this data-

Sample Database Transpose Data in Excel
Sample Database
  • Select the entire database to transpose.
  • Press CTRL + C on your keyboard or select the Copy option in the Home tab to copy the database.
  • Select a blank cell in an empty area in the worksheet to paste the database.
  • Pull down on Paste in Home tab under Clipboard section.
  • Select Paste Special at the bottom.
Paste Special
Paste Special

A new window opens showing Paste Special options –

Paste Special Options
Paste Special Options
  • Check Transpose at the bottom.
  • Hit OK.

Your data is now transposed. This is how it should look like-

Transposed Database Transpose Data in Excel
Transposed Database

Transposing data with TRANSPOSE formula

If you don’t want to transpose your database using Paste Special options, then you can use the TRANSPOSE function in Excel to do the same for you, but it is quite a lengthy process.

Here is how you can do it-

We take the same database to transpose using TRANSPOSE formula

Sample Database Transpose Data in Excel
Sample Database
  • Select the same number of blank cells that are taken by your database, but horizontally, like this-
Horizontal Replica of Vertical Database Transpose Data in Excel
Horizontal Replica of Vertical Database
  • Without clicking anywhere else, start typing the TRANSPOSE formula i.e., =TRANSPOSE(
  • Press Tab on your keyboard or double-click on the formula when it is suggested by Excel, to use the formula.
  • Select the database after typing =TRANSPOSE( when asked for the array.
  • Close brackets after selecting the database, but don’t press ENTER yet!
Transpose Array Selection Transpose Data in Excel
Transpose Array Selection
  • Press CTRL + SHIFT + ENTER to apply the TRANSPOSE function with the array formula. This is because we have selected more than one cell to apply a formula and ending a function with an array formula (CTRL + SHIFT + ENTER) will enable the function to be applied on more than one cell.
  • Do NOT press ENTER right away after completing the TRANSPOSE function or you will see a #VALUE! an error like this-
#VALUE! Error Transpose Data in Excel
#VALUE! Error
  • After applying the function with array formula, your data will now be transposed, like this-
Transposed Database Transpose Data in Excel
Transposed Database


This was a detailed and step-by-step guide on two ways to transpose your data in Excel. If you have any doubts regarding any of the steps mentioned above, drop your queries below and we will answer them!