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

Excel Formula : Convert data from long to wide format

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

The data is shown in the image below : -

Reshape data from long to wide format

Step I : To pull unique values from Column A

Type the following formula in cell D2
=IFERROR(INDEX($A$2:$A$100, MATCH(0,COUNTIF($D1:D$1, $A$2:$A$100), 0)),"")
Press CTRL + SHIFT + ENTER to confirm this formula as it's an array formula. If this formula is entered correctly, you would see the formula inside the curly brackets {}.

 Column A does not necessarily to be in numeric format. Similarly, Column B can take both numeric or character values.

 Step II : Reshape data from long to wide format

Type the following formula in cell E2

=IF(COLUMN(A1)<=COUNTIF($A$1:$A$100,"="&$D2),OFFSET(INDIRECT("$B"&MATCH($D2,$A$1:$A$100,0)),COLUMN(A1)-1,0),"")

Paste the formula to the right and down so that transformation happens correctly. You can use CTRL + R shortcut key to paste the formula to the right and CTRL + D shortcut key to paste the formula down.

Download the workbook
Love this Post? Spread the Word!
Comment and share to motivate us to write more!
About Author:

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

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*
Related Posts:
5 Responses to "Excel Formula : Convert data from long to wide format"
  1. YasserKhalil19 July 2015 at 13:01

    The data in Column A must be numbers?
    I tried text data and got wrong results

    ReplyDelete
    Replies
    1. Anonymous22 November 2017 at 15:27

      yes, it must definitely be numbers, in order

      Delete
    2. John Lunalo28 July 2018 at 04:55

      We have another formula for extracting unique numbers

      Alternatively you can copy on a different sheet and drop duplicates

      Delete
  2. Anonymous17 May 2016 at 12:03

    The second COUNTIF should be a MATCH() instead. It should be:

    =IF(COLUMN(A1) <=COUNTIF($A$1:$A$100,"="&$D2),OFFSET(INDIRECT("$B"& MATCH($D2,$A$1:$A$100,0)),COLUMN(A1)-1,0),"")

    ReplyDelete

We have Zero Tolerance to Spam. Comments with links will be deleted immediately upon our review.

Next → ← Prev
Subscribe to: Post Comments (Atom)
Scroll to Top