# Reducing un-used chart variables, for paramount scaling

• ozgrid.com/forum/core/index.php?attachment/42755/

I have a chart that displays all variables, as normal, but if some of the variables are not being used
it makes the chart seem un-tidy, harder to read and includes space not required, thus reducing the viewable span of the chart or graph. I was hoping there may be a couple of tricks and a function or two to solve this problem.. as in attachments.
ash

• Re: Reducing un-used chart variables, for paramount scaling.

Replacing the zero values with #N/A will stop them being plotted. Excel doesn't plot error values

Use something like

=IF(A1=0, NA(),A1)

There are three types of people in this world.
Those who can count and those who can&#039;t.

• Re: Reducing un-used chart variables, for paramount scaling.

Thanks Neale
the variables adjust to N/A but the graphs still show large sections of nothing.

Must be because the cells are selected whatever is in them

• Re: Reducing un-used chart variables, for paramount scaling

To make the chart smaller you need to hide those rows wit zero amounts

hidden cells don't display at all

N/A are not plotted

There are three types of people in this world.
Those who can count and those who can&#039;t.

• Re: Reducing un-used chart variables, for paramount scaling

Not sure if this is a viable solution for you since I don't know the complete details about your data.

However, to dynamically plot just the contiguous cells that are greater than zero you could use a helper column to facilitate a named range that describes the cell and is used as the inputs for the chart series.

See the attached example.

Column-A is used as the helper column with this formula: =IF(AND(C7>0,D7>0),ROW(),"")

The following named ranges were created:

Chart_Labels =OFFSET(Sheet1!\$A\$6,MATCH(MIN(Sheet1!\$A\$6:\$A\$29),Sheet1!\$A\$6:\$A\$29,0)-1,1,COUNT(Sheet1!\$A\$6:\$A\$29),1)
BHP1_Data =OFFSET(Chart_Labels,0,1)
BHP2_Data =OFFSET(Chart_Labels,0,2)

To see how the named ranges are used in the chart, right click on the chart and choose Select Data from the context menu.
After the dialog is open, select BHP 1 and chose Edit Series and review the contents of the Series parameter boxes.

Initially these boxes show something like Sheet1!\$C\$7:\$C\$29.
The range range reference is replaced with the named range so that it become Sheet1!BHP1_Data
After Saving the workbook Excel converts this to show as =Oz_160883.xlsx!BHP1_Data

Another option (easiest and most flexible) is to use a Pivot Chart. See Sheet8 of the attachment.

## Files

• Oz_160883.xlsx

(30.82 kB, downloaded 55 times, last: )

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]