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
Spread the Word!
Share
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.

22 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
  6. This saved me hours and hours of work! Thank you so much!

    ReplyDelete
  7. 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
  8. Alt+D+P is not working in Excel 2016. Please guide in this issue.

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

    ReplyDelete
  10. Instructions are very unclear

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

    ReplyDelete
  12. Alt + D + P does not work on excel 2016. What would the command name be please?

    ReplyDelete
  13. I do accept as true with all the ideas youíve offered in your post. They are very convincing and can definitely work.

    ReplyDelete
  14. nice work bro, thanks for sharing very helpful information

    ReplyDelete
  15. Still working perfectly, thanks!

    ReplyDelete
  16. great, your ideas and info is really very helpfull

    ReplyDelete
  17. Packaging may be a process by which the various industries are suitably packed in order that they ought to retain their therapeutic effectiveness from the time of packaging till they're consumed. Packaging could also be defined because the art and science which involves preparing the articles for transport, storage display and use. Pharmaceutical packaging is the means of providing protection, presentation, identification, information and convenience to encourage compliance with a course of therapy.
    While some importers obsess about the look and feel of their packaging, many struggle just to deliver their products to customers unharmed. A late 2016 survey of 2,000 online shoppers found that one in ten have received damaged items.
    Many factors can impact the condition of your packaging and the products inside. Everything from packaging materials used, to regulations in your market of sale and even the climate throughout your supply chain can hinder your ability to deliver a quality product to your customers.
    Pre-shipment inspection can go a long way in helping you verify both product and packaging quality. But when serious packaging problems persist you may need greater precision with packaging testing in a qualified lab. Any one of these nine lab tests on our list could help you solve these quality problems and save your products.

    ReplyDelete

Next → ← Prev