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.

Download Practice Workbook

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.

Players Data to transpose

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.

Transpose formula for Players Data

Press Ctrl+Shift+Enter.

Transposed Players Data

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.

Dataset of example 1 to transpose

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.

conditional transpose in excel

Then press Ctrl+Shift+Enter. After that, data will be transposed into a new array.

Result of example 1 after transposing

Read More: How to Convert Single Columns to Rows in Excel with Formulas


Similar Readings


 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.

problems with basic transpose function

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))

conditional transpose in excel

Then,press Ctrl+Shift+Enter.

Result of example 2 after transposing

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.


Further Readings

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

2 Comments
  1. I have a query i have list in column i want to transpose that list in multiple column list is having a blank column so that next transpose set should be in next column if blank cell arises. Very helpfull if you resolve my query.

    • Hi Akhil,

      Unfortunately, there are no direct ways or simple formulas to do that. One way to work around this is to remove the columns as the entirety of them is empty. And then try transposing. Or you can try out VBA to remove the empty columns/rows then try using transpose commands.

Leave a reply

ExcelDemy
Logo