O Excel tem uma função Transpor incorporada que alterna linhas e colunas, mas isso não funciona para o seu cenário. O único que acho que funcionará é uma função VBA. Meu VB é um pouco enferrujado, mas este deve funcionar. Você precisará modificar o intervalo "A1: E5" para cobrir as células que deseja transformar. E isso copia os valores da primeira planilha chamada "Sheet1" para o segundo chamado "Sheet2". Se você tiver dados na Planilha2 ou tiver planilhas nomeadas diferentes, precisará modificar o código.
Sub Transform()
Dim rowStr As String
Dim rowIndex As Integer
rowIndex = 1
For Each Cell In Sheet1.Range("A1:E5")
If Cell.Column = 1 Then
rowStr = Cell.Value
ElseIf Not IsEmpty(Cell.Value) Then
Sheet2.Cells(rowIndex, 1) = rowStr
Sheet2.Cells(rowIndex, 2) = Cell.Value
rowIndex = rowIndex + 1
End If
Next Cell
End Sub