Help Required for Charting

[Vignesh M]

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

 

[vletm]

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?

 

[p45cal]

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

 

[Vignesh M]

@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]

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

 

[yuvsaha123]

thanks for the awesome information.

 

[Hui]

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

 

[yuvsaha123]

thanks for the awesome information.

thanks my issue has been fixed.