This tutorial explains how to convert data from wide to long format in Excel.
Suppose you have data stored in wide format in Excel. You want to reshape it to long format. This may seem like an easy task but it can be challenging if you don't know how to reshape data in Excel.
Sample Data
The table below shows project budgets for three employees, Tom, Dick, and Harry, over a period of six weeks.
Name | Dept | Project | 1/7/2013 | 1/14/2013 | 1/21/2013 | 1/28/2013 | 2/4/2013 | 02/11/2013 |
---|---|---|---|---|---|---|---|---|
Tom | IT | Budget | 10 | 15 | 17 | 35 | 27 | 18 |
Dick | IT | Budget | 15 | 18 | 21 | 18 | 28 | 13 |
Harry | Acct | Budget | 7 | 12 | 5 | 33 | 14 | 9 |
Reshape data from wide to long format |
Steps to Convert Data from Wide to Long Format in Excel
- Insert a helper column right to column named "Project"
Concatenates "Name", "Dept", "Project" values and specify "|" delimiter between them. For example, =A2&"|"&B2&"|"&C2.
Press ALT + D + P shortcut key to activate "Pivot Table and PivotChart Wizard".
- Select "Multiple consolidation ranges"
- Press Next
- Select "I will create the page fields"
- Add range selecting the helper column you inserted in the first step along with 6 date columns (total 7 columns excluding first three columns)
- Press Next
- Go to the last cell of the pivot table
- Right click and select Show Details
- Use Convert Text to Column wizard by pressing ALT + D + E to separate "Name", "Dept","Project" values.
The output is shown in the image below -
Output: Convert data from wide to long format |
This is a simple and working formula. Big thanks for sharing it!
ReplyDeleteI wonder if Excel can match two data sets too by a common column? e.g.: data on species abundances with data on species traits.
Keep on good work Deepanshu Bhalla!
Yes it does; just apply vlookup command.
DeleteCheers, Sartaj
Really really helpful!!!! Nice jobs!
ReplyDeletegood
ReplyDeleteAwesome!
ReplyDeleteVery nice work, needs some explanation to get to know the real engineering behind these commands. For example what it actually does for "Multiple consolidation ranges" etc.
Still its great as it works.
This worked perfectly and saved me a lot of time. Thank you!
ReplyDeleteI have followed all the steps for reshaping data from wide to long format in excel but still wont get my data in long format!! Whats the problem?
ReplyDeleteIt's hard to anticipate what went wrong for you. Please save your file on cloud drive and post file location here.
DeleteThis saved me hours and hours of work! Thank you so much!
ReplyDeleteHey How does this work if we have 6 more dates say end date consider the date column in your example as start date. so basically we have 2 sets ( 6 each) of columns.
ReplyDeleteBRILLIANT
ReplyDeleteAlt+D+P is not working in Excel 2016. Please guide in this issue.
ReplyDeletelong Press Alt and make sure you release d before pressing p
ReplyDeleteInstructions are very unclear
ReplyDeleteSteps 9-11 does not exist. After step 8 you can only finish creating the table.
ReplyDeleteAlt + D + P does not work on excel 2016. What would the command name be please?
ReplyDeleteStill working perfectly, thanks!
ReplyDeleteI have a more complex data
ReplyDelete