Excel 2010 - Pivot Table Chart

**jmancha** · 08-17-2011, 04:05 PM

I have several Pivot table charts and in each of my colums on the main data sheet there are drop downs and some if functions. The Pivot Tables are counting these cells. Although the cells are technically blank the pivot table will not exclude them. I could remove "blank" from each of my fields, however this a good way for me to verify that my database is being updated correctly. Any help would be greatly appreciated. Exhausted from searching the web in vain.

**romperstomper** · 08-18-2011, 05:45 AM

You should use dynamic ranges as the source for your pivot table - see here for example - so that you do not include blank cells.

**jmancha** · 08-22-2011, 11:02 AM

I tried this option but the blanks are still coming through. Any other thoughts?

**MarvinP** · 08-22-2011, 11:06 AM

Hi,
Have you simply clicked on the row filter and unchecked the blanks? This would then leave them out in your counts. If there isn't a multi select feature int he dropdown try to filter them out using the Label or Value Filters.

See a picture on this link http://blog.contextures.com/archives...t-table-field/ and watch video.

**tiger10012** · 08-22-2011, 11:10 AM

You could make a copy of the worksheet with your data. Then you will be able to delete the blanks of the worksheet you are making the pivot on, and you will have the original data on your copy to compare against.

**romperstomper** · 08-22-2011, 11:12 AM

Unless the blanks are within your data (i.e. not at the end of it) then the Contextures method should work if done properly. We can either keep guessing, or you could provide a sample workbook...