Forum Discussion

Sajal Roychowdhury's avatar
Sajal Roychowdhury
Copper Contributor
Dec 05, 2017

Data Filling in Transpose

Hello ,       I have a data sheet which consists of date in Month-Year Format from 2015 to 2029. I have a column which has Project Names in it (Total 83) .   I want the data to be transposed so...
excel
Formulas and Functions
Macros and VBA
Wyn Hopkins's avatar
Wyn Hopkins
MVP
Dec 05, 2017

Hi Sajal

If you have Excel 2010 or later then Power Query is perfect for this

 

You turn your original data into a Table or a named range then use Power Query to "unpivot" it. 

 

Let me know what version of Excel you're on and if you've used Power Query before and I can give you some guidance.

 

 

 

  • Sajal Roychowdhury's avatar
    Sajal Roychowdhury
    Copper Contributor
    Dec 06, 2017

    Hello Wyn ,

              Thanks for the response . I am currently using Excel 2016 64 bit version . Haven't tried the Power Query . Can you provide some guidance into this ? 

     

    Regards,

    • Wyn Hopkins's avatar
      Wyn Hopkins
      MVP
      Dec 06, 2017

       

      Step 1: Convert table using Ctrl + t

       

       

       

       

      Step 2: Data > From Table

       

       

       

       

      Step 3: Delete Change Type Step

       

      Step 4:  Right Click on First Column and select Unpivot Other Columns

       

       

       Step 5:   Remove the Attribute Column (via right click)

       

       

      Step 6  Click on ABC123 and chose Date

       

       

      do the same on the Project column and choose Text

       

      Step 7: Rename Columns as Project and Date

       

       

       

      Step 8:  Change Query Name to something meaningful, then click Close and Load To...

       

      Choose Table,  and the converted data will be loaded back into Excel

      Demo File Attached

Resources