How to Perform Conditional Transpose in Excel (2 Easy Examples)

Get FREE Advanced Excel Exercises with Solutions!

This tutorial shows how we can add conditional transpose in excel. In some situations, you might need to rearrange 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

The 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. But in the latest versions like Excel 365 just use 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.

📌 Steps:

  • 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 following formula on Cell B11.
=TRANSPOSE(B4:E9)
  • Press the Enter button.

Use of Transpose function in Excel

We can data has been transposed.

  • Now, add borders and do the necessary modifications to present transposed data properly.

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.

Example 1: Conditional Transpose of Data for More Than a Number of Rows

Here is a dataset that contains some values in a single column starting from Cell B5.

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”.

📌 Steps:

  • First, select a cell that you want your first element to be pasted. Write the formula:
=IF(COUNTA($B$5:$B$11)>6,TRANSPOSE($B$5:$B$11),"")

Transpose a column based on condition

COUNTA will count the number of rows in that column.  We can also use the following formula based on the COUNT function if all the data are numbers.

=IF(COUNT($B$5:$B$11)>6,TRANSPOSE($B$5:$B$11),"")

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


Similar Readings


Example 2: Merge an IF Condition While Transposing Data in Excel

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 E9 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.

📌 Steps:

  • 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(B4:E9="","",B4:E9))

Conditional Transpose without zero in Excel

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)


Things to Remember

  • If you use the old versions of Excel, you should press Ctrl+Shift+Enter instead of the Enter button. Because all the formulas are for arrays.

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 with 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 from ExcelDemy.


Further Readings

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo