Create a DASHBoard like Chart & Graph

[Johnsei]

Hi,

I have a situation that looks like this, how can I present the data in a chart or graph effectively. All in one chart, please if anyone has done something like this, please provide me suggestions. Or any easy way to present this data with limited space.

preferability one chart, graph or table.


Thanks

 

[Somendra Misra]

Hi John,

I would propose as per attached sheet, I had made one List sheet to have options. Second Table sheet to have one table to have all the data. Now you can make chart as per your choice what you want to have, say quantity vs category, category vs no. of customer and so on.

Have a look and write back.

Regards,

 

[Johnsei]

cool, looks good, but can you have something like a visualization that shows everything at once ? The end result is not going to be in excel where the person has the option to choose what charts base on the filter he/she wants but it will turn into a PPT slide to show a visualization of everything at once, in either one table, charts, or graphs, ideally a pictorial. It looks like your table sheet has items that are missing and are not all there.

 

[Somendra Misra]

@Johnsei

The file was just a illustration, you can add items and can create dynamic named range. In sample I had given static range.

Secondly, what you want to see in graph?

Regards,

 

[Johnsei]

Secondly, what you want to see in graph?
-----------
all the item that shows in my spreadsheet that I attached, like how can I present it effectively without having user having a choice in what he/she wants to see but see everything in a visualization format. B/c the visualization is going to be on PDF, if a user has a choice in what to chose from your suggestion and we put that on PDF we only see some of the picture or data instead of all of it, the bigger picture.

In addition, what makes this more challenging and interesting is that for example, the excel file that I attached with all the data for example is a given week but we also have the same info but for another week and so how do you show that for the two weeks (as a comparison) then all in one chart/graph or pictorial to save space ?

Hope I make sense. Thanks !

 

[Somendra Misra]

Secondly, what you want to see in graph?
-----------
all the item that shows in my spreadsheet that I attached, like how can I present it effectively without having user having a choice in what he/she wants to see but see everything in a visualization format. B/c the visualization is going to be on PDF, if a user has a choice in what to chose from your suggestion and we put that on PDF we only see some of the picture or data instead of all of it, the bigger picture.

In addition, what makes this more challenging and interesting is that for example, the excel file that I attached with all the data for example is a given week but we also have the same info but for another week and so how do you show that for the two weeks (as a comparison) then all in one chart/graph or pictorial to save space ?

Hope I make sense. Thanks !

Will update something on graph in evening.

Regards,

 

[NARAYANK991]

Hi ,

It would be better if you post a real-life working file instead of an artificially made up sample , which may or may not resemble your actual data.

When you say data will get extended , will it extend across columns or down rows ? How will your 4 tables remain located when data is added ? Will it be added in the existing layout , or will more rows / columns be added ?

Your present data structure does not have any provision for specifying the week ; how will this be added when you have data for more than one week ?

Narayan

 

[Johnsei]

Hi Narayank991,

I wish I could post the real data and example, but these are not mine data and are likely confidential. However, the example that I provided are very close to the real thing.

Yes the data can be extended but with limit. In both row 2, 12, and column A, and H, it doesn't get more than 10 either on the row or column level.

How will you 4 tables remain located when data is added ? Will it be added in the existing layout , or will more rows / columns be added ?

These data are build using pivot table, the pivot table is in that layout, so if I build another pivot table base on another week etc, and if either the row, or column gets extended and the data within changes then it will show as that.

depending what is in the week that we run for, more row, and column can be added, in addition the data within the rows and column can change. But I don't think that will affect the chart/graph in our visualization if we have one pre-made that could handle this. There won't be more than 10 header/ rows & columns for the four tables.

Your present data structure does not have any provision for specifying the week ; how will this be added when you have data for more than one week ?

Yes sorry lets just say what I loaded is week 1, and week 2 will have similar data but the value and possible headers can change. Some headers can either repeat, and some rows can repeat or some will not repeat from previous month, but the limit is 10.

The challenge is how can we present this in a visualization (charts/graphs/pictorial) with limited space and in addition do a weekly comparison ?

I attached another example with week 1 and Week 2.

I think if we have a charting concept that works for this data structure, it can work regardless if the data rows, columns and data changes or get extended as long as there is a known limit.

Thanks !!

 

[NARAYANK991]

Hi ,

The data structure you have shown is a difficult-to-work-with format ; is it possible that you can provide for the maximum number of rows possible in each table , and then layout the next table below this limit ?

For example , your existing table 1 for category A is from A2 through F10 ; if it is possible to say that the maximum this can extend to is row 20 , then the present table 2 for category A can start from row 25 instead of its existing row 12.

A similar restriction can be placed on the weeks , so that Week1 will start from row 1 and extend to row 49 , Week2 starts from row 50 and extends possibly till row 99 , and so on.

Finally , I do not know what is meant by charting concept ; anything that will finally be done in Excel can only be done using either formulae or VBA ; the data layout will determine whether the resulting formula / code is difficult to develop and maintain , or is easy to construct and modify.

Narayan

 

[Johnsei]

For example , your existing table 1 for category A is from A2 through F10 ; if it is possible to say that the maximum this can extend to is row 20 ,

Yea I will try to give another data example, but from your quote on top, that means that the column headers are all the same and with the same limit, such as apple, orange, but the rows such as A, B, C, D etc extend to 20 ?

I do not know what is meant by charting concept ;

meaning that a graph or some kind of visualization that can show all the data that I included in a concise manner, comparing the two weeks. For example if you have a document PDF file or presentation paper, and you have an audience and you want to present these data (the ones I attached) the most easy to visualized,
see, and understand to your audience to give them the most information with limited space on your document bc you will also present other information from other categories (not shown), how can you do that, that is what I mean ?

You only have one page of paper that you want to show all these information, along with other information, ideally in a visualization so it can be easily seen and understand by your audience, how do you do that, that is what I mean ?

I think some sort of visualization or dashboard is needed.

So with these data, preferably in one graph/chart/ visualization etc.

anything that will finally be done in Excel can only be done using either formulae or VBA ; the data layout will determine whether the resulting formula / code is difficult to develop and maintain , or is easy to construct and modify.

I understand that you meant automation here with formulas and VBA, however how do you present the data after the automation ?

Your document for presentation is not in automation form but in PDF file.

Thanks !

 

[NARAYANK991]

Hi ,

Presentation is never an issue ; what ever be the format of presentation , if the data is available , it can always be manipulated to give you the presentation format that you want.

It is always the data that is the problem ; if the data layout is difficult to work with , it may even make the required presentation , irrespective of its format , too much trouble for others to attempt.

The data layout should be such that manipulation is not difficult , even though it may make the data entry somewhat inconvenient for the user. Paid software is different ; since you are paying for it , you have every right to expect that it will make things easy for you , even though the manipulation behind the scenes may be extremely difficult. That is why you are paying for it.

Narayan

 

[Johnsei]

Hi,

I did another sample data, it is something like this. How do you report this
in either a graph/ chart or something that is visual with limited spaces, doing a comparison between the two weeks ? Thanks !