Transpose data in excel in 10 simple steps

Transpose Data in Excel

A new year has just begun. It’s time to prepare a new budget forecast report for my boss. Last Friday, I received two Excel files from Finance department and Sales department respectively. My goodness, I couldn’t help frowning when I was reading the table from Sales department. Thanks god that there’s a little trick called transpose data in excel to save me.

File from Sales department: which salesman handles which client accounts for which product categories:

file from sales department

File from Finance department: new year budget forecast by clients by product categories:

budget file from finance department

My challenge was to make the new budget forecast report by salesmen by clients by product categories like this:

budget forecast file

First thing to tackle is how to make the data from Sales department “lookup friendly” for me to combine it with the data from Finance department. To do it, I need to transpose data from columns to rows in the excel file of sales department. Here are my 10 simple steps to transpose data in excel within five minutes:

If you are not interested in watching the video, please continue reading.

Original data: The table from Sales department contains 53 columns and 215 rows.

First five columns : Office | Manager | Product Family |Product Sub Family | Salesman
The rest columns: Different clients
√ = The salesman handles that client account for that product category
Transpose-data-column-explanation

Goal: Transpose data in excel about clients from columns to rows

Transpose-data-result

Step 1: Insert a new column just right before the client columns

Transpose-data-step1

Step 2: Combine the first five columns and use | as the separator for each row.
This time, I used the sign & in the formula:
=A2&”|”&”B2&”|”&C2&”|”&D2&”|”&E2

If you prefer “concatenate” formula:
=concatenate(A2,”|”,B2,”|”,C2,”|”,D2,”|”,E2)

💡 Copy the formula down by a shortcut : Position the mouse in the lower right-hand corner of the formula cell until you see the black plus sign “+”, and double-click.
Beware that this trick copies the formula down only as far as Excel finds data to the left, i.e. the copying down does not continue across the first empty cell encountered in the column to the left

Transpose-data-step2

Step 3: Press Alt then D and P to invoke pivot table wizard.
Select “Multiple consolidation ranges”, click “Next”
Select “I will create the page fields” and “Next”

Transpose-data-step3a

Choose the range : From the “Concatenate” column to the last column and the last row of the table.
💡 Shortcut: select the first cell, press Ctrl + Shift + + at the same time.
Click “Add” and “Next” in the wizard.
Click “New sheet” and “Finish”
A new pivot table is created. Drag the column field from column labels area to row labels area.

Transpose-data-step3b

Step 4: Click on any cell in the pivot table
Select “Design” tab from the ribbon on top
Select “Report layout” and choose “Show in tabular form”
Select “Subtotals” and choose “Do not show subtotals”
Select “Grand Totals” and choose “Off for rows and columns”

Transpose-data-step4

Step 5: Select the entire columns of the pivot table. Copy and paste value on any blank cell out of the pivot table.

Transpose-data-step5

Step 6: fill in the blank for each row of the first column.
💡 Shortcut: Start off with the second cell of the 2nd column. Press Ctrl + Shift + . Hold it and press . Select “Find & Select”, click “Go to special”, then “Blanks”, finally “Ok”

Transpose-data-step6

Step 7: Type =, hold Ctrl, press + Enter. Fill in all the blank cells with the same value as their cells above. Copy and paste value.
💡 Range selection shortcut: start off with the first cell, press Ctrl + Shift +
Transpose-data-step7a
Double click the column to show all the values of the cells

Transpose-data-step7b

Step 8: Insert four new columns after the 1st column (4 separators = 4 new columns).
Transpose-data-step8a
Select the cells of the first column except header
Choose “Data” tab on ribbon then select “Text to column”
Select “Delimited”, then “Next”, click “Other”, Type |, then “Next” and “Finish”
Select all the new columns, double click to show all the values of the cells

Transpose-data-step8b

Step 9: Provide an appropriate header for each column. The last column is the
information about if the salesman takes care of the client: 1 = Yes, blank cell = No

Step 10: Change the color of the headers. Select all columns and double click to display all the values.

Transpose-data-step10

Share your thoughts: if you have any other ways to transpose data in excel or questions about my method, I’d be happy to hear it out. Cheers

Leave a Reply

Your email address will not be published. Required fields are marked *