Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
not showing blank cells in a chart
I would like to have a pie chart that selects data points from a static range
only if they are non-blank cells. searching on the internet i found the code that i am useing now which gets me a pie chart with only the datapoints i want, but the legend still shows all possible points even if they are blank. i have defined two names as ranges: statevalues =OFFSET(statechtdata!$A$2,0,0,COUNTA(statechtdata! $A:$A)-1,1) statevaluesY =OFFSET(statevalues,0,1) and the chart series is as follows: =SERIES(statechtdata!$A$1,'sample portfolio.xls'!statevalues,'sample portfolio.xls'!statevaluesY,1) also, the pie chart originally only had one color for all the data point which i went through and began to manually change but I am wondering if there is a setting or an option to enusre that they come in as different colors in the first place. -- Thanks, Galen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
not showing blank cells in a chart
AFAIK, with a single series Pie chart there is no automatic way to prevent
blank points showing in the Legend. You can programmatically delete individual LegendEntries if say the relative point is blank. To add them back if values change, need to delete and recreate the Legend. Post back if not sure how to go about that. To reset the default individual segment colours, select the pie, and format Fill with Automatic. Regards, Peter T "Galen" wrote in message ... I would like to have a pie chart that selects data points from a static range only if they are non-blank cells. searching on the internet i found the code that i am useing now which gets me a pie chart with only the datapoints i want, but the legend still shows all possible points even if they are blank. i have defined two names as ranges: statevalues =OFFSET(statechtdata!$A$2,0,0,COUNTA(statechtdata! $A:$A)-1,1) statevaluesY =OFFSET(statevalues,0,1) and the chart series is as follows: =SERIES(statechtdata!$A$1,'sample portfolio.xls'!statevalues,'sample portfolio.xls'!statevaluesY,1) also, the pie chart originally only had one color for all the data point which i went through and began to manually change but I am wondering if there is a setting or an option to enusre that they come in as different colors in the first place. -- Thanks, Galen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
not showing blank cells in a chart
yeah that's what i ended up doing. Thanks.
-- Thanks, Galen "Peter T" wrote: AFAIK, with a single series Pie chart there is no automatic way to prevent blank points showing in the Legend. You can programmatically delete individual LegendEntries if say the relative point is blank. To add them back if values change, need to delete and recreate the Legend. Post back if not sure how to go about that. To reset the default individual segment colours, select the pie, and format Fill with Automatic. Regards, Peter T "Galen" wrote in message ... I would like to have a pie chart that selects data points from a static range only if they are non-blank cells. searching on the internet i found the code that i am useing now which gets me a pie chart with only the datapoints i want, but the legend still shows all possible points even if they are blank. i have defined two names as ranges: statevalues =OFFSET(statechtdata!$A$2,0,0,COUNTA(statechtdata! $A:$A)-1,1) statevaluesY =OFFSET(statevalues,0,1) and the chart series is as follows: =SERIES(statechtdata!$A$1,'sample portfolio.xls'!statevalues,'sample portfolio.xls'!statevaluesY,1) also, the pie chart originally only had one color for all the data point which i went through and began to manually change but I am wondering if there is a setting or an option to enusre that they come in as different colors in the first place. -- Thanks, Galen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blank Cells showing as 0 in pivot | Excel Worksheet Functions | |||
Avoiding empty cells showing as zero in line chart without na() | Charts and Charting in Excel | |||
Showing Zero for all blank cells in a range | Excel Discussion (Misc queries) | |||
blank cells showing up as a month 1 | Excel Worksheet Functions | |||
showing blank cells | Excel Discussion (Misc queries) |