Ignore blanks (" ") cells when plotting Bar Graph

**sgreenprice** · 10-11-2017, 11:19 AM

Hello all,

I have a quick question that I've been trying to find the solution to on the net, but with no avail!

I need to plot a bar graph that is dynamic, hence I need it to avoid plotting any fields with blanks or " " values in. I've attached a sample file where the blanks might be filled, else they would have " " values (I've not put the formulae in for now).

Any help would be appreciated.

P.S. I want to avoid the use of filtering/pivot charts if possible and prefer a more dynamic presentation. I don't mind using VBA but if I can get away with just formulae or a setting change that'd be great!

Kind Regards,
SGP

**BadlySpelledBuoy** · 10-11-2017, 11:42 AM

You can do this with 'dynamic named ranges'. In the attached I've altered the chart to run off of two DNRs.
One called XAxis to (unsurprisingly) give values to the X axis and uses this formula:

Formula:

Please Login or Register  to view this content.

and this one called Values... well you get the idea

Formula:

Please Login or Register  to view this content.

Press Ctrl + F3 to open the name manager to see the named ranges.
Charts edited in the usual way but to use the named ranges rather than specified cell ranges.

Any use?

BSB

**sgreenprice** · 10-18-2017, 04:21 AM

Apologies for the late reply but works a charm - thanks for your help!! Will mark as solved and +rep

**BadlySpelledBuoy** · 10-18-2017, 04:34 AM

Happy to help

BSB

Ignore blanks (" ") cells when plotting Bar Graph

LinkBack

Thread Tools

Rate This Thread

Display

Ignore blanks (" ") cells when plotting Bar Graph

Re: Ignore blanks (" ") cells when plotting Bar Graph

Re: Ignore blanks (" ") cells when plotting Bar Graph

Re: Ignore blanks (" ") cells when plotting Bar Graph

Thread Information

Users Browsing this Thread

Similar Threads

Missing "Value from Cells"" option when formatting data labels on scatter graph on Mac

[SOLVED] How to get VBA to ignore "blank" cells or cells with "text"

Auto fill the cell with a value and replace underscores with "dash" and "blanks"

Data Validation - how to ignore cells with "0" or "-"

[SOLVED] array formula count "number of" - can it ignore blanks

Plotting when some cells in the data range get "" from a formula

Problem "plotting visible cells only" in List

Bookmarks

Bookmarks

Posting Permissions