Hi,

I am trying to create a dynamic dashboard using pivot tables.

I am coming across this issue: Since it is a dynamic dashboard, I have to use formulae to update the data. When I convert the table which has formulae to pivot table, I see 90% blank data which I cannot remove. I understand excel sees formula in cells with no values as a 'non-empty' cell.

However, not removing the data is skewing the data completely.

Example is as follows:

Grand Total                                                                            3999

This is an image from a pivot table. The number 3718 is for formula containing, non-value cells and is skewing up my plots completely.

If I can remove the 3718 from the above table, final data will have 281 entries (from all rows added up together). This will help me plot data easily.

What to do in this matter?

Please help.

PS: I have already used the option where I click on pivot table>Analyze>Options>Layout and Format>Replaced 'For empty cells show" from blank to 0. Not helping at all.