Transposing data means moving the row data to a column and a column data to a row. Transposing data is useful for data analysis. At times, we have to pull data from various files with different formats for analysis and preparing reports. In such circumstances, we may have to transpose some data from one file to the other.

In excel, we can transpose data in multiple ways. Let us discuss these methods one by one.

Static method using Paste special

In this method, the data is transposed using Paste Special Transpose feature in Excel. Here the transposed data is static. It does not change with the change in the source data. We can transpose the selected data using the Paste Special Transpose feature in excel.

Read More UNDERSTANDING XLOOPUP WITH EXAMPLES

Let us quickly understand with the help of a simple example.

We have a list of Companies with Revenues, Profits, and margins in a horizontal format. We want to transpose this data in a vertical format.

To transpose this data, select the data and Press Control + C to copy it. Then go to the cell where you want to paste this data and right click your mouse. You get a list of options. Select Paste Special.

Once you select Paste Special, You get the following screen

Select the option Values and Transpose.

Then Click OK.

You get the following table.

The data is pasted in a vertical table format.

You can now format the data and proceed with the analysis required.

The same operation can be done by using a keyboard shortcut.

First Copy the data by using Control & C

Then Press, Alt & E & S to activate Paste Special Dialogue Box.

Then Press V & E to transpose and paste the copied values.

Combo Courses – EXCEL AND POWERPOINT

Dynamic method using the Transpose function

There is an inbuilt Transpose function in Excel. This function can transpose data dynamically, meaning that whenever there is a change in the original table, the transposed data gets updated automatically.

To Transpose the data, select the range where you want to transpose the data.

In our example, we have 4 rows and 10 columns in our table.

To transpose the data, select the range with 4 columns and 10 rows. Then type the transpose function.

= Transpose(B5:K8)

Then press enter. In office 365 version of Microsoft excel, you get the transposed data (as shown below). The formula is in cell B12. The data has spilled over to the other cells. There is no formula in those cells. In case you want to change the formula, it can only be done by going back to the same cell (B12).

In earlier versions of the excel, you get an error when you press enter, as the transpose function is an array function. Excel can’t hold multiple values in a single cell.

So, to get the transposed data you have to type, = Transpose(B5:K8)

Then Press Control & Shift & Enter.

 

When you press these keys you get the data in transposed format.

This is a dynamic table. When we make any change to the original data source. This table gets updated automatically.

Learn how to create data automation reports quickly, Join DATA ANALYST SKILLS TRAINING WITH EXCEL and avail discounts.

Dynamic method using a simple trick

 

There is a third trick to transpose the data. This is by linking cells to the original table (as shown below).

The problem with this method is that this trick works for small data sets, but not for large tables. We can use a simple trick to link the full table (whether small or large) in just a few steps.

Read More100+ useful excel keyboard shortcuts

Link a first few cells to the table. Then replace the equal to sign with some other alphabet. We are using “sl” instead of =. See below.

Select the cells (as shown above), place the mouse on the bottom right of the selection and drag horizontally. Excel automatically fills the cell numbers slB5, slC5 etc. (see below).

Now select the cells in the range (G4:P7). Press Control and H for find and replace.

 

Then in Find with type sl and in Replace with type the equal to sign (=).

 

Then press Replace All.

Your data gets transposed horizontally.

 

These are the different ways we can transpose data in excel. Let us know which method you found to be the most useful one.

Keep Learning and have fun.

Next Read 10 MOST FINANCIAL FUNCTIONS IN EXCEL