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.
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),"")
- Then press Enter. After that, data will be transposed into a new array.
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
- How to Transpose Duplicate Rows to Columns in Excel (4 Ways)
- Transpose Multiple Columns into One Column in Excel (3 Handy Methods)
- How to Reverse Transpose in Excel (3 Simple Methods)
- Convert Columns to Rows in Excel Using Power Query
- How to Change Vertical Column to Horizontal in Excel
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))
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
- Data clean-up techniques in Excel: Changing vertical data to horizontal data
- How to Swap Rows in Excel (2 Methods)
- Excel VBA: Transpose Multiple Rows in Group to Columns
- How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)
- Excel Transpose Formulas Without Changing References (4 Easy Ways)
- How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)
- How to Convert Column to Comma Separated List With Single Quotes
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.