# Conditional Transpose in Excel (2 Examples)

This tutorial shows how we can add conditional transpose in excel. In some situations, you might need to re-arrange your rows and columns. If you do it traditionally, it will take a long time. If there are hundreds of rows and columns, it will be quite impossible to re-create your dataset. An easy way to solve this is to use the TRANSPOSE function in Excel.

Our tutorial will focus on conditional transpose in excel. Our formula will transpose a dataset based on conditions.

## How Transpose function Works in Excel

Transpose function converts rows into columns and columns into rows. The Basic Transpose formula is :

=TRANSPOSE(array)

You have to enter the range of cells as an array to perform perfectly. For that, you need to press Ctrl+Shift+Enter

Here array is the range of cells that you want to transpose. It will rotate the cells like the first row of the dataset will become the first column in the new array and the first column will be the first row. The rest of the columns and rows will be rotated like this.

Here is the dataset of some football players’ information. To transpose the data first select a range of cells that you want to paste the formula. But this time you have to re-arrange the orientation. As we said, rows become columns and columns become rows. Here, we have 4 columns. So, in the new array, it will be 4 rows.

We have 5 rows. So, in the new array, there will be 5 columns.

Then enter the TRANSPOSE formula. Press Ctrl+Shift+Enter. Our data is transposed into a new array.

However, for more about the Transpose function, check our article “How to Use TRANSPOSE Function in Excel (5 Ways)“.

## 2 Examples of Conditional Transpose in Excel

In conditional transposing, your data will be transposed into a new array based on conditions. If your conditions matched your data will be transposed or your new transposed data will follow some conditions for some values. Here are our 2 examples that show how we can transpose our data based on conditions in excel.

### 1. Transpose Data to an Equal Specific Number

Here is a dataset that contains some values in a single column starting from cell B2. Now we want to transpose that column into a single row. But our condition is, “transpose the column if there are more than 6 rows”.

First, select a cell that you want your first element to be pasted. Write the formula:

`=IF(COUNTA(\$B\$2:\$B\$8)>6,TRANSPOSE(\$B\$2:\$B\$8),"")`

COUNTA will count the number of rows in that column. Then press Ctrl+Shift+Enter. After that, data will be transposed into a new array. Read More: How to Convert Single Columns to Rows in Excel with Formulas

### 2. Transpose Data Without Zero in Blank Positions

Now, if you looked at our transposed dataset of the “How Transpose Function works in Excel” section, there was a slight problem. There you can see, in our given dataset, Cell E7 was blank. But in the transposed array the value was set to 0. For these types of problems, we need to use conditional transpose in excel.

Similar to the previous one, select a range of cells where you want your new data. Remember to change the orientation. Otherwise, it will show some errors.

Then write the formula :

`=TRANSPOSE(IF(B2:E7="","",B2:E7))` Then,press Ctrl+Shift+Enter. As you can see there is no value in the new blank cell.

Read More: How to Transpose a Table in Excel (5 Suitable Methods)

## Conclusion

These formulas are just to show you how you can add conditions to transpose in excel. Our formulas will work to transpose the data at ease. Make sure you download our practice workbook and also give these formulas a try. Also, make sure to check out our new articles on various Excel problems. 1. Reply  