How to Convert Data from Wide to Long Format in Excel

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

About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn

Post Comment 18 Responses to "How to Convert Data from Wide to Long Format in Excel"

AnonymousOctober 27, 2015 at 9:02 PM
This is a simple and working formula. Big thanks for sharing it!

I 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!
AnonymousNovember 6, 2015 at 1:52 PM
Really really helpful!!!! Nice jobs!
UnknownMarch 4, 2016 at 3:21 AM
good
Sartaj Raja AlamMarch 31, 2016 at 9:23 PM
Awesome!
Very 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.
anonSeptember 1, 2016 at 11:32 AM
This worked perfectly and saved me a lot of time. Thank you!
GeroApril 13, 2017 at 6:20 AM
I 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?
AnonymousAugust 2, 2017 at 12:22 PM
This saved me hours and hours of work! Thank you so much!
AnonymousJune 26, 2018 at 9:26 AM
Hey 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.
The StyliteMarch 12, 2019 at 6:59 AM
BRILLIANT
UnknownMarch 17, 2019 at 11:11 AM
Alt+D+P is not working in Excel 2016. Please guide in this issue.
UnknownMarch 24, 2019 at 4:43 AM
long Press Alt and make sure you release d before pressing p
UnknownSeptember 6, 2020 at 12:39 AM
Instructions are very unclear
UnknownSeptember 6, 2020 at 12:41 AM
Steps 9-11 does not exist. After step 8 you can only finish creating the table.
UnknownNovember 4, 2020 at 4:10 AM
Alt + D + P does not work on excel 2016. What would the command name be please?
AnonymousMarch 25, 2021 at 6:01 AM
Still working perfectly, thanks!
aayush gadiaJune 16, 2021 at 7:08 PM
I have a more complex data