• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

need help in excel to convert the data from horizontal to vertical and viceversa

B

bekkems

New Member
Contract a/c Charge type Amount

700109234 ENERGY 59,885.00

700109234 RENT 968

700109234 TAX 1,210.00

700109235 ENERGY 5,670.00

700109235 RENT 231

700109235 TAX 75


FOR THIS I WANT OUPUT AS


contract a/c Energy Rent Tax

700109234 59,885.00 1,210.00 400.00

700109235 5,670.00 231 75


can you please let me know how to work on it in excel
 
You want a PivotTable.


Select the entire range of information (from "Contract" to the bottom-right dollar amount) and go to the "Insert" tab on the ribbon and click "PivotTable".


A box opens up the shows "Contract a/c", "Charge Type" and "Amount" with check boxes next to them. Grab the "Charge Type" and drag it down to "Column Labels". Drag "Contract" to "Row Labels". Drag "Amount" to "Values". You'll probably need to right-click the "Amount" and change it from "Count" to "Sum" by selecting the option "Value Field Settings".


It's a lot easier done than explained in words, so I suggest you check out a PivotTable video. It's very easy once you get the hang of it. If you're more hands-on, skip the video and just experiment with dragging different pieces into different sections of the PivotTable. The good thing is that you can't really mess anything up because your data will remain intact even though you may jack up a PivotTable. : )


Good luck.
 
You must log in or register to reply here.
Back
Top