Pivot Tables to capture blanks but not display them

I am looking to edit my pivot tables in the 'Open SR Pivots' tab and 'Closed SR Pivots' tab to capture the blanks but not display them in the charts in the 'Charts of SRs' tab. Essentially for each of the pivots, I want to capture about 10 blank fields down in case more information is added to the data source so it is captured when the data is refreshed on the charts. However, every time I try to do so, it displays the blanks on the charts.
DSE-Carelog-Report.xlsm
AckeemKAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Any particular reason you using normal charts in this case..As you can use pivot charts which will auto adjust itself when the pivot table increases..Enclosed is the same for your reference of what i made a pivot chart which looks same as yours...

You can replace all your charts with pivots charts which will essentially do what you are looking for..

Saurabh...
DSE-Carelog-Report.xlsm
FloraCommented:
check it is fixed now.
DSE-Carelog-Report.xlsm
AckeemKAuthor Commented:
Hi Saurabh,

I showed an example of adding in more values and the pivot not capturing it. Anyway to fix this? I want to add in those blank cells underneath to auto adjust as you mention but currently it is not doing so.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

AckeemKAuthor Commented:
I have attached your file back here.
DSE-Carelog-Report--6-.xlsm
Saurabh Singh TeotiaCommented:
AckeemK,

I don't exactly follow what's your problem..?? If you add the new data and refresh the pivot table it will show additional data automatically but you need to do refresh manually...

Saurabh...
AckeemKAuthor Commented:
If you notice, the Data Computing Appliance V1 and V2 even after refreshing is not in the chart.
Saurabh Singh TeotiaCommented:
Yeah because you have unchecked them in the pivot table..Enclosed is the snapshot of the same for your reference..Once you check them it will automatically show...
Image
AckeemKAuthor Commented:
Attached is after selecting those two and refreshing it manually. It is not included in the chart.
Screen-Shot-2015-04-17-at-8.30.05-AM.png
Saurabh Singh TeotiaCommented:
Yes..that's because your chart you created is a normal chart and the two new additional values are not part of chart..so every time a new value is added you need to edit the source manually to add them and thats why i asked you to create a pivot chart in first place as this will take care of the same..

image
Enclosed is your workbook for your reference where please refer to open SR Pivots sheet i created a pivot chart for your reference which you can replicate easily in other charts as well which easy to replicate..and as soon a new value is added it gets added..

Your workbook to refer...

Saurabh...
DSE-Carelog-Report.xlsm
Rob HensonFinance AnalystCommented:
As you are using a Standard Chart rather than a Pivot Chart, you will have to adjust the data range for the chart manually to include the new rows of data in the pivot.

Alternatively, create a Dynamic Range to capture the Data Range from the Pivot table and then use the Dynamic Range for the chart.

Thanks
Rob H

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.