Exclude zero values from chart
Hi guys/gurls I have attached an example to help. What i am trying to achieve is not have space for departments with zero value as it takes up space and from chart it doesnt seem that clear what department it is refering. If anyone could help me on this it would be much appreciated.
I would also like this chart to be replicated on varous others costs with same format e.g. fuel if there is a vba or macro that i can be helped with.
Thanks
The simplest way to remove categories, rather than series, is to use Autofilter.
If you filter your data and exclude those with a zero value in Q3:Q14 your chart will exclude,
September
December
February
May
June
Hi Andy i see your point but I can only filter 1 row at a time on condition if row not equal to zero.. department 1, 3, 8,9 11,12,13,14,15 are zero too..
If you want both categories and series excluded then you need to build the reporting data using pivot table. This will also require a change in the data layout.
Both rows and columns have been filtered for values less than zero.
Andy thanks for guiding in the right directon but is there a VBA or Macro that i can use and plus i have hardly used pivot tables and how to link charts to pivot tables using 2 filter i wouldnt have clue.
Once you create your PT the ribbon has a button to create the chart.
You filter the row data by selecting the cell labeled Row Labels.
Use the built-in filter button to pick Filter Values.
Pick Less than and enter zero.
Repeat for Column
The chart will automatically update.
I can not see the need for code as it is all built-in and relatively straight forward.
Hi Andy thanks for the help I shall give it a go !!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks