Preventing Null values from plotting in Excel Charts.

fb1990
fb1990 used Ask the Experts™
on
Hello EE,

I am working on creating a report on data that is constantly changing.  I want to plot the only cells with records.  In the attached spreadsheet, if i click on column B7 to show Group 1, there are 21 records that needs to be plotted.  If i click on B7 to select Group 2 there are 6 records to be plotted.  For group 2, I want to delete or hide cells that are not populated and only plot the cells that are populated with data.  I am thinking this can be accomplished using vba.  My goal is to create 2 separate reports for Group 1 and Group 2

Thanks in advance for your assistance
My-Sample.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
For all the data series, replace the "" in the formulas with NA() like below...

e.g. the formula in C13 should be like below......

=IFERROR(INDEX('My Sample Data'!D$6:D$32,SMALL(IF('My Sample Data'!$C$6:$C$32=$B$7,ROW('My Sample Data'!D$6:D$32)-ROW('My Sample Data'!$B$6)+1,""),ROW(C1))),NA())

Open in new window

Change all the formulas in the same way for column D,E,F,J,K,L and M.

Author

Commented:
Thanks Subodh,

This does not work for me.  It is showing #N/A.  I do not want it to show.  Is there a way to hide the empty cells and only plot where there are records?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Actually it is the easiest workaround this issue.
Don't worry about the #N/A errors which you see in the cells, you can hide them with the conditional formatting by making a new rule for conditional formatting using the formula below and set the font color exactly same as that of the fill color.
First select the range A13:C33 and use the formula given below for conditional formatting.
=ISERROR(A13)

Open in new window

Please refer to the attached.
My-Sample.xlsx
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Author

Commented:
thanks again for staying with me on this.  Although the data for blank cells are not showing on chart, the space is still on the chart.  Is there a way to make the chart grow with the chart instead of showing it as blank space on the chart?
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Okay. I have created 7 named ranges and used them as a source in the charts.
You may customize the charts as per your requirement.

Please refer to the attached.
My-Sample.xlsx

Author

Commented:
Thank you very much Subodh.  I really appreciate your help
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome. Glad to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial