Moving Data Table in Chart area

[cmissal]

Hi everyone! I have used chandoo.org (and pingback sites) for the last few months with great success. I thank everyone for your generosity in sharing your wisdom. (I too hope to share some of mine soon!)


I am creating a dynamic dashboard and have two challenges that I have not been able to resolve through reading/research -- One I will post here:


I need to tighten the 'real estate' used by a bar chart, however I cannot move the data table at the bottom to remove the blank area between the table and the chart above. Other items on my other charts(for example the legend) can be moved with no difficulty. Does anyone know how this can be done? (Note: VBA code is fine... I have plenty in my app already.)


Thanks again!

 

[Luke M]

Unfortunately, the data table is unforgiving in the limitations it has, one of those being size/placement. First question, is the table really necessary? Usually we use charts to make it so we don't have to read a table of data.


If it's really needed, maybe you can build your own? Create the table somewhere, then select it, Copy. Holding down Shift, go to Edit - Picture Link. You now have a picture that is linked to the cells (in case the data changes) and you could place it where you want on your chart.

 

[cmissal]

well, the data values are needed, however since some of the bars are far longer than others putting the value in the stack bar (3 values per bar) is really ugly/overlapping. Since I am creating this 'tool' for all of our PM's they won't have the skill (in all cases) to be able to manually edit the chart and move the labels (plus this is undesirable).


The table approach is a very good idea (I was thinking of doing this because, for some reason Excel is inverting the order of the labels in the table (top horizontal bar is listed at the far right column of table???) -- but like most of you (I suspect), I am also trying to explore excel and it's limitations, so I didn't want to give in so easily.


if there is no way I will proceed with the table approach.


PS. Here is an image of the chart I am referring to:


http://cid-06d2dcf988984b17.office.live.com/self.aspx/.Public/Task%20Performance%20Dashboard%20Module.jpg


Thanks again!


Casey

 

[Luke M]

Casey,


Good luck on your endeavor, and please post back a solution if you find one. =)

For the table order, I can answer that one. Whenever you have a chart, think of it as pouring water into a bucket from a pitcher. The "first" part of the water goes into the bucket, but then gets covered up by the "middle" and "last" parts. How this plays out in a chart is that later series will be plotted over (in case of a line chart) or on top of (in case of stacked charts), or to the right/above column and bar charts.


So, in your bar chart, the series that is on top is really the last series, and in your data table that's displayed at the right.


Now, you can format the y-axis to display data in reverse order (and check that the y-axis cross at maximum value) and this should arrange the bars in a more logical order compared to the data table.

 

[cmissal]

I haven't found a solution other than your suggestion of creating a separate table. This works great for the example mentioned in this thread, but I will continue to search for a solution. I have a similar situation where I need to tighten the horizontal axis values closer to the axis and this too is a fixed box!

 

[Hui]

Cmissal

I agree with Luke that Excel data Tables attached to Charts are poor and offer limited customisation.


However there is always a work around


Construct your chart without a Data Table

Where you want your data table, insert enough Columns to suit

Change the Chart/Column sizes so that the columns line uop with your Charts series

Add data and Format cells as required.


Another way would eb to use a Camera Tool to link to a Nicely Formatted Data area setup elsewhere and then position a camera Tool below the chart

 

[cmissal]

Thanks Hui and Luke.


I did use the Camera Tool to take a picture and place under the chart on my dashboard. This approach allows more flexibility.