Help Hide (Blank) from Pivot table drop down lists

[maximillianrg]

When it comes to Excel my wife is a true novice. Her company generates daily reports on the retail stores she oversees and I’ve created an excel sheet that allows her to easily copy the data into the data sheet and some pivot tables for her to easily capture sales figures by day, week, or month.

I’ve set up the pivot tables so they only have one drop down list but since there are blanks in the data sheet she copies her data into, the drop down lists contains (blank). For example in the pivot table for the month the drop down looks like:

[]October
[]November
[]December
[](Blank)

There is no way to have 100% of the data populated in the range the pivot tables are set to use and I have formulas that calculate from the pivot tables that use =if(OR statements to eliminate #DIV/0! If data does not exist. If she selects [](blank) it can throw off the tables

Is there a way to tell an excel pivot table not to display (Bank) in the drop down list?

Thanks

Max

 

[AlphaFrog]

There is no way to have 100% of the data populated in the range the pivot tables are set to use

You can have the pivot tables use a Dynamic Data Source. Maybe that would eliminate the blanks from the pivot table's data range?

 

[Access Beginner]

Not sure if this is what you're after, but can't you just deselect "Blank" from the Pivot Table drop down lists