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
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.
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:
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.
- 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
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 :
As you can see there is no value in the new blank cell.
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.
- 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