# 3 Ways to Transpose Excel Data

# (Rotate data from Horizontal to Vertical or Vice Versa)

Check out 3 ways to Transpose your data in Excel.

These are useful when you want to switch the orientation of your data from columns to rows or vice versa.

I show you how to:

1. Use a static approach – copy and paste as transpose

2. Use the Transpose function to rotate data

3. Use a simple formula trick to transpose

This way you can transpose without an array formula and also transpose without copying and pasting.

Note: Once you get dynamic arrays, writing the Transpose function becomes super simple. You no longer need to highlight the result area first, nor press control + shift + enter.

Let’s examine three ways to transpose data in Excel.

First, what does it mean to “transpose data”? Transposing data is where the data in the rows are turned into columns, and the data in the columns is turned into rows.

Our first example will result in a static solution (meaning the transposed data will not update if the original data changes). The second and third examples will be dynamic.

Below is an image of our data from the downloadable sample file (see download link below)

## Method #1 – Using Copy/Paste (static)

Step 1: Select the data (**A3:B14**) and press **CTRL-C** or the **Copy** button.

Step 2: Select the destination cell that will serve as the upper-left corner of the transposed data (**D3**) and right mouse click and select **Paste Special**.

Check the box labeled **Transpose** and click **OK**.

NOTE: An alternative to this is to select the data and click **Copy**, click the destination cell, then click the lower part of the **Paste** button and click the **Transpose**.

The downside to this method is that it is not dynamic. If data is changed in the original “vertical” data set, the corresponding entry in the “horizontal” data set will not reflect the change.

## Method #2 – Using the Transpose function (dynamic)

Step 1: Select cell **D3** and enter the following formula:

=TRANSPOSE(A3:B14)

Notice that the formula returns a **#VALUE!** error.

The reason for this is because the formula is attempting to display all the data from the selected range within a single cell. If we edit the TRANSPOSE function and click at the end of the formula, we can press **F9** to see that the results are being collected and stored in the response.

TRANSPOSE is an array formula, and because of this, we need to select beforehand a range of cells that will serve as a landing zone for all the possible answers. This will require a small bit of planning on our part before we write the formula.

Step 1 (revised): Highlight a range of cells that you believe can support the returned values (i.e. **D3:M4**).

Step 2: Enter the formula below (** do NOT hit ENTER!!!**):

=TRANSPOSE(A3:B14)

Step 3: Press **CTRL-Shift-Enter**

As we can see, we did not select enough cells to support the returned data. If we use the Fill Series handle to drag the array formula from column M to column O, the additional cells have data, but they are not arranged properly.

To solve this issue:

- select
**ALL**the original cells that held the TRANSPOSE function (**D3:O4**) and click in the formula bar to return to edit mode. - Press
**CTRL-Shift-Enter**to update the TRANSPOSE array formula.

Because this is a formula, when data changes in the original “vertical” list, we see the same change to data in the “horizontal” list.

## Method #3 – Simple cell references (dynamic)

When you refer (or point) a cell to another cell, the data from the original cell is reflected in the referencing cell (ex: if cell B1 points to cell A1, the contents of A1 will be displayed in cell B1.)

In our sample file, we can have cell **D4** point to cell **A3**, and cell **E4** point to cell **A4**, etc…

This may work in a VERY small data set, but in larger data sets, this becomes impractical to construct in this method.

One idea is to use the Fill Series tool to replicate the original “=A3” formula and have the cell references update relatively. We can’t use the Fill Series handle to fill across, because the references move in the wrong direction.

A clever trick to overcome this Fill Series limitation is to type the formula as text.

Step 1: Instead of typing the equals sign, we will enter some homemade prefix (like your initials) and then follow the prefix with the cell address you want to repeat.

**Cell D4 –> lgA3**

**Cell D5 –> lgB3**

Step 2: Select cells **D4:D5** and drag the Fill Series handle to the right to column O.

Observe that the numbers incremented in the proper direction.

Step 3: We will perform a **Find/Replace** (CTRL-H) and find our initials (“**lg**”) and replace with an equals sign (“**=**”) {no double quotes in either entry}.

The original entries (with prefixes) have been converted to formulas.

We now have a list of correct references that also behave dynamically.

We can change the data in the “vertical” list and see a reflection of that change in the “horizontal” list.

## Practice Workbook

Feel free to Download the Workbook HERE

**Excel Dashboards that Inform & Impress**

## Leave A Comment