Sehar has attended:
Excel VBA Introduction course
Excel Advanced - Formulas & Functions course
Pivot Chart
Hello,
I have created an excel spreadsheet which contains asset prices and tracks the prices every Monday. Some of the assets will not have a price every week and so the value pulled through to the pivot chart is zero. I am using a line chart to track the movement of the price however the zero values are throwing off the results, is there any way for excel to ignore the zero value and skip to the next result? So for example if I had a price 99 zero 100 100 for the line to just go gradually from 99 to 100 rather than dip down to zero. I have tried using the pivot table fields and adding a value filter of greater than 1 but this doesn't seem to work, my data is formatted as values.
Thanks
Sehar
RE: Pivot Chart
Hi Sehar,
Thank you for the forum question.
The only way of doing this is to replace zero with a blank cell. You can do this with the If function.
Select the chart. On the Design tab click Select Data on the Data group. In the bottom left corner click Hidden and Empty cells and then select Connect Data points with Lines.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Pivot Chart
Hello Jens,
Thank you for your response I have just tried what you suggested however it doesn't seem to work. I had an N/A value in the cell which was derived from a formula as I had no priice, i've tried changing this to blank and zero and refreshing the data in the pivot table but the pivot chart doesn't seem to change,
Thanks
Sehar
RE: Pivot Chart
Hi Sehar,
Is it possible to send me the file.
info@microsofttraining.net
I have done what I suggested many times, so there must be something in your file, which make it not work for you.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Pivot Chart
Hello Jens,
In trying to send you an example I managed to get the file to work by recreating the chart and having no values in the cell, thanks for your help,
Sehar
RE: Pivot Chart
Hi Sehar,
I wonder if you have send us the file? I haven't received any.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
