Uma macro fará isso facilmente. A macro abaixo colocará o resultado em uma nova planilha.
Sub TransposeStuff()
Dim lLastRow As Long, lColLoop As Long, lLastCol As Long
Dim shtOrg As Worksheet, shtDest As Worksheet
'turn off updates to speed up code execution
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
Set shtOrg = ActiveSheet
Set shtDest = Sheets.Add
shtDest.[a1] = "Category"
shtDest.[B1] = "Item"
lLastCol = shtOrg.Cells(1, Columns.Count).End(xlToLeft).Column
For lColLoop = 1 To lLastCol
lLastRow = shtOrg.Cells(Rows.Count, lColLoop).End(xlUp).Row
shtOrg.Range(shtOrg.Cells(2, lColLoop), shtOrg.Cells(lLastRow, lColLoop)).Copy
shtDest.Cells(Rows.Count, 2).End(xlUp).Offset (1)
shtDest.Range(shtDest.Cells(Rows.Count, 1).End(xlUp).Offset(1), _
shtDest.Cells(Rows.Count, 2).End(xlUp).Offset(, -1)).Value = shtOrg.Cells(1, lColLoop)
Next lColLoop
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub