• 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.

Help Required for Charting

Vignesh M

New Member
Dears,

I am attaching a sample data which has weekly trend of inventories (Open balance, Purchase, Sales & Closing Balance). I would like to present it as a chart, please assist me as I am a basic level user in MS Excel.

thanks a lot in advance.

Regards,
Vignesh
 

Attachments

vletm

Excel Ninja
Vignesh M
Did You upload a correct file because there are missing those Open balance & Closing Balance?
p45cal ... I asked two columns ... of course, anyone could guess what are other columns than named Sales & Purchase.
Would You add Your data only to right or as well as to down?
Is that data layout only possible layout?
Do You have any idea - what would You chart look like?
 
Last edited:

p45cal

Well-Known Member
@vletm, opening balances are in columns B, E, H & K.
@Vignesh M in the attached is a Power Query solution. As long as the headers in row 3 have no duplicates and you maintain the week numbers format in row 4, you should be able to add to the table horizontally with more weeks and vertically with more stock names.
Once you've updated the data in your table, you will need to refresh the pivot table (eg. by right-clicking it and choosing Refresh, or by doing the same to the chart (you don't need to do both)) and the chart should update by itself.

Your arrangement of data is not friendly, either to the user or to analysing the data. I have suggested another arrangement on the sheet.
 

Attachments

Vignesh M

New Member
@vletm, opening balances are in columns B, E, H & K.
@Vignesh M in the attached is a Power Query solution. As long as the headers in row 3 have no duplicates and you maintain the week numbers format in row 4, you should be able to add to the table horizontally with more weeks and vertically with more stock names.
Once you've updated the data in your table, you will need to refresh the pivot table (eg. by right-clicking it and choosing Refresh, or by doing the same to the chart (you don't need to do both)) and the chart should update by itself.

Your arrangement of data is not friendly, either to the user or to analysing the data. I have suggested another arrangement on the sheet.
Thanks you so much for the response, and thanks for explaining the methodology for arranging the data in excel. The provided template is working fine and I will keep it for my learnings as well. Thanks again :)
 

Vignesh M

New Member
Vignesh M
Did You upload a correct file because there are missing those Open balance & Closing Balance?
p45cal ... I asked two columns ... of course, anyone could guess what are other columns than named Sales & Purchase.
Would You add Your data only to right or as well as to down?
Is that data layout only possible layout?
Do You have any idea - what would You chart look like?
Thanks so much for the response, going forward I will come up with a proper/organized data for help. I am now using the template file provided p45cal
 

Hui

Excel Ninja
Staff member
Why not use a Water Fall Chart

By doing that you can have an opening balance, a number of categories for Purchases and Sales and a Closing balance
 
Top