Dynamically Exclude Chart Columns With Zero Values

[Oksana]

I have a series of charts (over 100) that are comparing year-over-year year-to-date dollars spent by category.

Because it is YTD there are some categories that have zero dollars today but may have dollars added in a few months. Currently I have to manually filter out (on every chart) which columns need to be excluded (removing those with zero values). And YES...I have to...management gets annoyted at looking at 'nothing' columns as it makes the charts less valuable. I am looking for a way to tell the charts to always exclude columns that have zero values; without doing anything to remove them from the source data table. This is intended to better automate the monthly updates.

Important to Know: Because of security issues on our network I am NOT able to use VBA.

Thanks in advance for any help!

[eibi]

How are you current 'filtering' out the data -- manually deleting them, or hiding them, or using an autofilter? Are they always columns or are they sometimes rows?

Also: regarding VBA. You wouldn't necessarily need VBA in your file in order to use VBA as a tool to work on the file. At my office, I have several VBA files in my pesonal xlb, this code lives on my hard drive only, not in the individual excel files that I work on. And when I open files from the network, I use the macros I've saved in my personal xlb; then when I save the updated files on the network, the VBA stays on my computer and does not become part of the file that I save. Kind of like setting up default print settings on my computer; those print settings are not saved with the file on the network...they are local. Could this be feasible within your organization?

[Oksana]

Hi! Thanks for the reply!
-I am using the filter options on the chart itself.
-The data sets are always in 'rows' on the data table; they always translate into 'columns' on the charts.
-Regarding VBA, I comment may have been a bit harsh/inacurate...the problem has often been that it is a file centralized on th intranet, linked to other documents, utilized by multiple users, sometimes via virtual desktop or citrix. If there is no other way other than VBA to achieve my goal I am will to try it; but I've found it to be super glitchy and easily corruptable with all of those variables (even with protecting the sheets).
Thanks in advance!!

[eibi]

It would be helpful to see a sample of your data if you can do that -- just one data table (including some of the zero rows) and one of your charts...

You can replace anything sensitive with dummy data if confidentiality is a concern. But it would be good to see the structure of your data in order to offer a helpful suggestion.

[Oksana]

2016.03.28 SAMPLE for Chart Filter Automation Question.pngHi there,
I just made a sample spreadsheet but cannot upload it for some reason. It is .xlsx and is very few KB. I'll keep trying. Thanks for your patience.
UPDATE: I just took a screenshot and it looks like I was able to upload that. I know it's not ideal but at least you can see the sample.
Thanks!!!

[Oksana]

I found a way! (Just posting to bring closure to the thread.)
I created a new lookup from the source data and turned it into a 'table'. I added a calculated column that said if 2015 data + 2016 data = 0 then put 'exclude', otherwise put 'include'. Then I filtered this table on that include/exclude column.
The chart now looks up only data showing in this table (be sure to uncheck 'show data in hidden rows' when selecting the chart data).
Each table should automatically/independantly adjust each month to add in any items that get values larger than 0.

The main trick was setting up the tables (one for every one of the 200+ graphs) one on top of the other so that hidden rows in one table didn't affect each other.
I'll mark this as solved but feel free to post back if this was not clear.
Thanks!