# How to Convert Columns to Rows in Excel Based On Cell Value

Get FREE Advanced Excel Exercises with Solutions!

Often, we need to convert columns to rows in excel based on cell value. This based on cell values conversion demands a criterion to be imposed on a cell or cells. In normal scenarios, transposing based on cell value or column to rows conversion (or vice versa) based on cell value is rarely required in any situation. However, the IF function converts columns to rows based on cell value. In this article, we demonstrate the conversion of columns to rows based on cell value. As well as we use a different dataset to transpose unique cell values to other columns.

## 2 Ways to Convert Columns to Rows in Excel Based On Cell Value

### Method 1: IF and TRANSPOSE Functions to Convert Columns to Rows in Excel Based On Cell Value

Let’s say, we have a dataset where Employee Name, Salary, Allowance, and Total Salary are stored. For some reason, we need Employee Names in Columns and Salary and Allowance amounts in Rows. To do so, we use the Total Salary amount of an Employee to insert the range in transpose. Therefore, the IF and TRANSPOSE function is used to convert columns into rows based on a Total Salary value. Step 1: Enter a criterion in any blank cell (i.e., G5). Paste the following array formula in cell B15.

`=IF(E5>G5,TRANSPOSE(B4:D13),"")`

The formula runs a logical_test (i.e., E5>G5) using the IF function. Then, if the logical_test returns TRUE, the formula executes the TRANSPOSE function for the range (i.e., B4:D13). Otherwise, the inserted formula displays a blank cell. You can put any amounts (i.e., Individual Total Salary or Range Total Salary) as criteria to run the logical_test depending on cell value.

Step 2: Press CTRL+SHIFT+ENTER (for Excel 365, Use only the ENTER key) altogether to display the columns as rows as depicted in the following picture. Transposing columns to rows depending on cell value is a seldom-used operation. As far as we know, only conditional functions such as IF allow Excel to do this.

### Method 2: Transpose Columns in Rows with Multiple Columns Based on Unique Values

Sometimes, we need values in different columns depending on their unique cell values. Though this is not the conventional transposing columns to rows, we demonstrate it to clarify the scenario: what are the ways we have to transpose values based on cell values.

For this method, we use a different dataset. In this dataset, we have Order Date, Product, and their sold Quantity. From the dataset, we can easily find out that the products have different sold quantities. And we want each unique sold quantity in columns. #### Method 2.1: Formula to Transpose Columns Based on Unique Values

A formula combined with the INDEX, MATCH, COUNTIF, and IF function can transpose the cell in another column based on unique values.

Step 1: Type the below array formula in any blank cell (i.e., F5).

`=INDEX(\$C\$5:\$C\$17, MATCH(0, COUNTIF(\$F\$4:\$F4, \$C\$5:\$C\$17), 0))`

In the formula, the COUNTIF function counts the cell imposing criteria (i.e., \$C\$5:\$C\$17) in the range (i.e., \$F\$4:\$F4). Then passes the count as lookup_array for the MATCH function. Finally, the outcome of the MATCH function is assigned as the row_num to the INDEX function.

Dollar Signs (\$) before the cell references are there for absolute references. Step 2: As the formula is an array formula, Hit CTRL+SHIFT+ENTER (for Excel 365, Use only the ENTER key) altogether to display the 1st product as shown in the image. Step 3: Write the below formula in cell G5.

`=IFERROR(INDEX(\$D\$5:\$D\$17, MATCH(0, COUNTIF(\$F5:F5,\$D\$5:\$D\$17)+IF(\$C\$5:\$C\$17<>\$F5, 1,0), 0)), 0)`

The formula declares the same arguments as Step 1 but imposes an extra condition using the IF function. The IF function executes a logical_test (i.e., \$C\$5:\$C\$17<>\$F5) to make up the lookup_array for the MATCH function. Step 4: Again, Press CTRL+SHIFT+ENTER (for Excel 365, Use only the ENTER key) to appear the unique value for product Bran. Step 5: Drag the Fill Handle side way to display all the unique sole quantities for the product in F5. Step 6: 2 different formulas are used to insert unique values in different columns. Use the Fill Handle to copy and insert formulas in other cells as depicted in the below picture. From the above image, you see unique sold quantities of different unique products in different columns. This method is useful when you want to see the individual unique cell entries for each unique entry.

#### Method 2.2: VBA Macro Code to Transpose Cells to Columns Based on Unique Cell Values

VBA Macros are efficient in achieving criteria-based outcomes. Since our sought outcomes are unique values of unique products, we can use VBA Macros.

Step 1: To open the Microsoft Visual Basic window, press ALT+F11 altogether. The Microsoft Visual Basic window opens. In the Microsoft Visual Basic window, Select Insert (from the Toolbar) > Choose Module (from the options). Step 2: In the Module, Paste the following macro code.

``````Sub Transpose_Unique()
Dim mrfRow As Long
Dim i As Long
Dim mrfCrt As String
Dim mrfCol As New Collection
Dim mrfRng As Range
Dim mrfOutRng As Range
Dim mrfTxt As String
Dim mrfCount As Long
Dim mrfVRng As Range
On Error Resume Next
Set mrfRng = Application.InputBox("please select data range(maximum two columns):", "Select the range", mrfTxt, , , , , 8)
Set mrfRng = Application.Intersect(mrfRng, mrfRng.Worksheet.UsedRange)
If mrfRng Is Nothing Then Exit Sub
If (mrfRng.Columns.Count <> 2) Or _
(mrfRng.Areas.Count > 1) Then
MsgBox "the range an area with two columns ", , "Select the Cell"
Exit Sub
End If
Set mrfOutRng = Application.InputBox("please select output range(specify one cell):", "Select the cell", mrfTxt, , , , , 8)
If mrfOutRng Is Nothing Then Exit Sub
Set mrfOutRng = mrfOutRng.Range(1)
mrfRow = mrfRng.Rows.Count
For i = 2 To mrfRow
Next
Application.ScreenUpdating = False
For i = 1 To mrfCol.Count
mrfCrt = mrfCol.Item(i)
mrfOutRng.Offset(i, 0) = mrfCrt
mrfRng.AutoFilter Field:=1, Criteria1:=mrfCrt
Set mrfVRng = mrfRng.Range("B2:B" & mrfRow).SpecialCells(xlCellTypeVisible)
If mrfVRng.Count > mrfCount Then mrfCount = mrfVRng.Count
mrfRng.Range("B2:B" & mrfRow).SpecialCells(xlCellTypeVisible).Copy
mrfOutRng.Offset(i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
Next
mrfOutRng = mrfRng.Cells(1, 1)
mrfOutRng.Offset(0, 1).Resize(1, mrfCount) = mrfRng.Cells(1, 2)
mrfRng.Rows(1).Copy
mrfOutRng.Resize(1, mrfCount + 1).PasteSpecial Paste:=xlPasteFormats
mrfRng.AutoFilter
Application.ScreenUpdating = True
End Sub``````

The macro declares multiple variables in their required types. Then displays a dialog box saying Select the range if the range contains the column number that does not equal 2. Again, bring out a dialog box to select the cell where you want to insert the transposed columns. At last, the macro copy and paste the cell entries changing their offsets using the VBA OFFSET function.

Step 3: Use the F5 key to run the macro. In a moment a dialog box named Select the range appears. Insert the range (i.e., C4:D17). Click on OK. Step 4: Afterward another dialog box named Select the cell appears. Select the cell (i.e., F4) where you want your transpose column to start. Click on OK. Step 5: In a moment, all the unique entries appear in different columns of respective unique products. ## Conclusion

In this article, we demonstrate how the IF and TRANSPOSE function convert columns to rows in excel based on cell value. However, transposing cell values in another column based on unique values is also described due to the similarities in data type. Hope these methods guide you in the direction that your data type requires. Comment if you have further inquiries or have anything to add.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  