Charts using Excel Table columns as data source

[dleseward]

Reading the Charts and Graphs book for Excel 2010, I can't find any references to creating charts by referencing Table Columns using structured references. Is this possible?

I expected to be able to use structured references such as =Table1[Column1] in the chart data field, but it doesn't like this. I also tried defining a name that referred to a column (e.g. Name1 = Table1[Column1]) and then using Name1, but this didn't work either.

thanks.

 

[Derek Brown]

Try using the name of the workbook in the chart reference.
Something like:
='My Workbook.xls'!Name1

 

[MrExcel]

It is strange the way that Excel is behaving....

Create a small table {Region, Q1, Q2, Q3} in row 1
Values for East in row 2
Values for Cental in row 3
Values for West in row 4

Use Ctrl+T to define the range as a table.

Build the chart from A1:D4.

The SERIES formula will refer to A2:A4 and B2:B4 instead of the table nomenclature. Yet, when you add a new region in row 5 or add a column E to hold Q4, the chart grows with the data. This would seem to indicate that the table is re-building the SERIES formulas. Using the structured table reference would seem to be a much better way for Microsoft to have gone.

I will run this past the Excel project managers to see if I can find an answer.

Bill

 

[dleseward]

Thank-you both.

In reply to Derek's suggestion. I have tried this, but when I try to save the modified Series function, Excel complains of an error in thw worksheet, range, etc. I originally defined the name at Workbook level and then tried with it defined at Sheet level. the Series functions just does not seem to like Names that are mapped to Table Columns.

I currently have the graph working with direct cell references, and yes it adjusts for adding new rows but...

What I really wanted to do was to allow the bubble chart to use different Columns selected by the user for the labels, values and bubble size dimensions. For example

A1 = Table[Column A] (driven by a validation drop down list)
A2 = Table[Column D]
A3 = Table[Column G]
A4 = Table[Column P]

Names then are:
Xarray = indirect(A1)
Yarray = indirect(A2)
Zarray = indirect(A3)
LableArray= indirect(A4)

Which should work out as a Series function that looks like:
Series("Series A",Sheet!Xarray,Sheet!Yarray,1,Sheet!Zarray)

Every way I have tried using a name does not work. Even defining Yarray = Sheet1!$C$1:$C$4 at Workbook & Sheet level causes an Excel error when I try to substitute it into the Series formula. Weirdly if you use Name Manager to define the name and then select all the rows in a Table Column, it automatically inserts the range as Table[Column] rather than Sheet1!$C$1:$C:$4.

Curiously, I installed the "XY Chart Labeler" add-in from (www.appspro.com) to be able to label the bubbles and it quite happily copes with using the name LableArray as described above.

It all strikes me as different Microsoft teams within the Excel development group not talking to each other. And of course any documentation on Tables and the use of structured references does not mention Charts.

 

[MrExcel]

I had a chance to e-mail with the Microsoft project manager on charts.

In the planning for Excel 2007, they were rewriting the whole chart engine. They considered the question if they should handle table nomenclature.

For any possible feature, they have to consider:
(a) how many people will benefit
(b) by doing this feature, how many other features won't get done

It seemed that relatively few people would want to do this and the development expense to support it would have cost too many other features to not get done.

So, it was a conscious decision to leave it out.

Bill

 

[dleseward]

Bill

Thanks for this. Not the answer I wanted, but at least I will quit trying to figure it out. Do you know if one of the Charting Add-ons out there would allow me to parametrise a chart in this way?

Given the "focus on usability" that they keep talking about, it would seem to me that having functionality (e.g. names, functions) work consistently across the product would be one of those fundamentals that they'd do.

thanks,
David

 

[Derek Brown]

Hello Bill,
Thank you very much for looking into this.
This is probably going to give me quite a bit of work to do when we upgrade from 2007 to 2010 in the next few weeks.
I make extensive use of 'names' in tables so that it is very clear as to what data is being used - some datasets are very large.
I haven't tried this with my scrolling/zooming charts yet, so it will be interesting to see if they are affected.
However, there does appear to be an 'untidy' solution to the problem - using 'dummy' data in the first data-row (that can be hidden).
Using the data you gave in your post, I added a 'dummy row' with zero values and created the 'names' that I needed. I used OFFSET just to ensure that it would always dynamically extend. So for Column B this would be, with the name "Q1data":
=OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$B:$B)-2,1)
and the chart series was given as
='Chart Bug.xlsx'!Q1data
I found that I could then add rows to the table without Excel rebuilding the Series formulas.
Thanks again.
Kind Regards
Derek