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 Finance department: new year budget forecast by clients by product categories:
My challenge was to make the new budget forecast report by salesmen by clients by product categories like this:
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
Goal: Transpose data in excel about clients from columns to rows
Step 1: Insert a new column just right before the client columns
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
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”
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.
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”
Step 5: Select the entire columns of the pivot table. Copy and paste value on any blank cell out of the pivot table.
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”
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 + ↓
Double click the column to show all the values of the cells
Step 8: Insert four new columns after the 1st column (4 separators = 4 new columns).
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
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.
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