Excel Formula : Convert data from wide to long format

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

Excel Tutorials : 100 Excel Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

8 Responses to "Excel Formula : Convert data from wide to long format"

  1. 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. Yes it does; just apply vlookup command.
      Cheers, Sartaj

      Delete
  2. Really really helpful!!!! Nice jobs!

    ReplyDelete
  3. 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
  4. This worked perfectly and saved me a lot of time. Thank you!

    ReplyDelete
  5. 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. It's hard to anticipate what went wrong for you. Please save your file on cloud drive and post file location here.

      Delete

Next → ← Prev