» » Excel Formula : Convert data from wide to long format

Excel Formula : Convert data from wide to long format

15 Comments
Suppose you have data that is stored in wide format in excel. You want to reshape it to long format.

Reshape data from wide to long format

Steps
  1. Insert a helper column right to column named "Project"
  2. Concatenates "Name", "Dept", "Project" values and specify "|" delimiter between them. For example, =A2&"|"&B2&"|"&C2
  3. Press ALT + D + P shortcut key
  4. Select "Multiple consolidation ranges"
  5. Press Next
  6. Select "I will create the page fields"
  7. Add range selecting helper column you inserted in the first step along with 6 date columns (total 7 columns excluding first three columns)
  8. Press Next
  9. Go to the last cell of the pivot table
  10. Right click and select Show Details
  11. 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 -

Post Transformation Data Format
Related Posts
About Author:

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 has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.

15 Responses to "Excel Formula : Convert data from wide to long format"
  1. Anonymous27 October 2015 at 21:02

    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!

    ReplyDelete
    Replies
    1. Sartaj Raja Alam31 March 2016 at 21:24

      Yes it does; just apply vlookup command.
      Cheers, Sartaj

      Delete
  2. Anonymous6 November 2015 at 13:52

    Really really helpful!!!! Nice jobs!

    ReplyDelete
  3. Unknown4 March 2016 at 03:21

    good

    ReplyDelete
  4. Sartaj Raja Alam31 March 2016 at 21:23

    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.

    ReplyDelete
  5. anon1 September 2016 at 11:32

    This worked perfectly and saved me a lot of time. Thank you!

    ReplyDelete
  6. Gero13 April 2017 at 06:20

    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?

    ReplyDelete
    Replies
    1. Deepanshu Bhalla13 April 2017 at 06:39

      It's hard to anticipate what went wrong for you. Please save your file on cloud drive and post file location here.

      Delete
  7. Anonymous2 August 2017 at 12:22

    This saved me hours and hours of work! Thank you so much!

    ReplyDelete
  8. Unknown26 June 2018 at 09:26

    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.

    ReplyDelete
  9. The Stylite12 March 2019 at 06:59

    BRILLIANT

    ReplyDelete
  10. Unknown17 March 2019 at 11:11

    Alt+D+P is not working in Excel 2016. Please guide in this issue.

    ReplyDelete
  11. Unknown24 March 2019 at 04:43

    long Press Alt and make sure you release d before pressing p

    ReplyDelete
  12. Unknown6 September 2020 at 00:39

    Instructions are very unclear

    ReplyDelete
  13. Unknown6 September 2020 at 00:41

    Steps 9-11 does not exist. After step 8 you can only finish creating the table.

    ReplyDelete

Next → ← Prev
Subscribe to: Post Comments (Atom)
Love this Post? Spread the Word!
Share