How to make collapsible columns for a table?

How to not chart blank columns

• QUESTION: hoping you can help me with achart issue. I am using2007 and have created a pivot table from a massive data dump. The resulting table has six columns of data (product type 1 -6) and 11 rows (half year returns), I then use this table of ¡°actuals¡± for forecasting, creating a number of other tables (6). The issue arises when the user only wants to compare 2 products (say product 3 and 5). There are no problems with the pivot table and associated chart, as this functionality automatically does not display blank columns and hence does not plot them, but the additional tables with the forecasting are the problem. I use a ¡° ¡° so the columns appear empty, I have made copies of the tables with #N/A, N/A() and 0, but all plot on the line chart and bar chart (and the legend of course). Thanks heaps Tracey ANSWER: Try this workaround with the formula you are using (from now on REFERENCE) =if(REFERENCE=0,na(),REFERENCE) before pressing ENTER, make sure to hold CTRL+SHIFT as well This will put a #N/A Sign which will be ignored by the chart. The problem, Which I reckon you wont be having in this case, is when there is a series data and you only wish to ignore one since with this formula the chart will repeat any previous data on that series. In your case you want to ignore the whole series of data. I have found cases in which I had to actually clear the contents of a cell, or hide them using either hide function or autofilter, which could be annoying when you need an automated spreadsheet for others to see, and in which case you can always use VB. Hopefully this is not your case and the above workaround will suit you ---------- FOLLOW-UP ---------- QUESTION: A N/A in the tables works a treat for the values (ie no line along the x axis), but i cannot seem to make the legend (ie the heading from the table) not show. I have tried using the formula to result in a NA(), N/A, O or " " in the cell, but the line with or without a description always shows. Tracey

• Answer:

  Unfortunatelly, that is the case I talked you about, you have either manually hide those columns or write a macro that do it for you. try using this macro it might help you Change range and sheet name acordingly _____________________________ Sub HideColumns() Dim c As Range For Each c In Worksheets("sheet1").Range("a1:H1").Cells If Application.Or(c.Value = 0, UCase(c.Value) = "n/a") Then c.Entirecolumn.Hidden = True Else c.Entirecolumn.Hidden = False End If Next c End Sub _______________________________