Charting selectable columns in Excel

Topic

New Reply

I have a simple table of data in Excel that I would like to chart, but I would like to be able to select which columns to chart without having to edit the chart each time. For example I have data like this

Code:

        A       B       C
Chart   Y       N       Y
Data    34      28      42

I would like my chart to graph only data for A and C, but if I change the datum for B from N to Y then it will automatically be included in the chart.

Can I do this with some macros, or will I need to fool around in VB? And if VB as there any suggestions?

Thanks in advance.

Reply | Quote

Replies

Macros are fooling around with VB….

Depending on your setup a simple way would be to use an IF to read the Y/N and the data and convert the “N” data to #NA error so that the points will not be plotted…

How well this work depends on what you have. Could you attach a sample workbook?

Steve

Reply | Quote

Macros are fooling around with VB….

Depending on your setup a simple way would be to use an IF to read the Y/N and the data and convert the “N” data to #NA error so that the points will not be plotted…

How well this work depends on what you have. Could you attach a sample workbook?

Steve

Sure Steve, here it is. The “Usage” sheet is to record what equipment is used each day. Some days may have more than one, some may have zero (but that hasn’t happened yet). “Summary” is the summarised data and the graph, and the others are to help calculate stuff for the summary sheet. If anything is not clear let me know. I like mucking about in Excel and have played with VB but I’m sure that I don’t do things in the best way.

I might fool about with that #NA method you suggested. Although I may just try and use VB as I’m sure that this simple workbook will grow over time as I try and add more stuff in.

Reply | Quote

Depending on your setup a simple way would be to use an IF to read the Y/N and the data and convert the “N” data to #NA error so that the points will not be plotted…

Steve, I just tried this and although the points are not plotted, they still appear in the chart. I am using a column chart and there are empty columns for the #N/A data. Thanks for the suggestion, I can imaging that using #N/A may be handy sometimes, but it doesn’t seem to help in this case.

Reply | Quote

Did you intend to attach file? There is none attached…

Steve

Reply | Quote

Sorry, I thought I had attached it.

Reply | Quote

No and you still haven’t….

Steve

Reply | Quote

Ahhh!! Now I see – I hadn’t noticed the “Upload” button!! Here we go.

Reply | Quote

Hello Craig – Your workbook looks like a good candidate for using Excel Database along with Excel Pivot Tables, Excel Pivot Charts, Excel Data Filters, etc.
Probably with very little need for Macros/VB.

On the “Summary” tab, what criteria determines if a Y or N goes in row 4?

Tim

PS: The usage of Equipment seems very low.

Reply | Quote

Thanks for your response, Tim.

The Y or N is changed by me. It doesn’t change too often, but some of the equipment that doesn’t get used much might get left off the chart sometimes.

I’m not sure what an Excel DB is, and although I have heard of pivot tables etc I haven’t used them much. Do you have any suggestions on how they might be used in this situation?

Reply | Quote

Hi Craig – I am working on Database, etc. I noticed that the chart in the example workbook plots LAHF and CP even though they both have an “N” in row 4 on Summary. Is that how it should be plotted?

Tim

Reply | Quote

Hi Craig – I am working on Database, etc. I noticed that the chart in the example workbook plots LAHF and CP even though they both have an “N” in row 4 on Summary. Is that how it should be plotted?

Tim

No, I had just added that row in as an example. I had done things manually before and had never had that row as it had always been in my head. Sorry for the confusion.

Reply | Quote

The easiest thing may be is to transpose your chart data table so that the Y/N are in a column. Then create the chart with all the transposed data. Add a autofilter to the Y/N column and filter for Y and only the visible data will be plotted…

Steve

Reply | Quote

Two useful tips:

1. The Y/N is a data mask, remove the N data (replace by null string), and then pack the data to the left to remove the N data.
2. The ranges you wish to plot are dynamic (width depends on number of Y data) – handle this by using dynamic named ranges.

In the attached file I’ve used the SMALL function to achieve task 1. I forget where I first encountered this method, I have found it to be extremely useful as it’s often desirable to remove blanks and shift the remaining data to the left (or to the top) so that there are no blank cells in the repacked data.

Once the data are in contiguous cells, you can set up named ranges, whose width is equal to the number of data points to appear in the plot. The ranges are defined by using the OFFSET range function. Insert the names into the ranges to be plotted.

The attached workbook shows 1 and 2 in action with your data.

Hope this helps!

RAK

Reply | Quote