I created a pivot table which is drawing data from couple of columns filled with formulas. The formulas start with iferror and in cases of error displays blank (""). However, the pivot table recognizes these blank cells as data in my filter and shows a blank box in the slicer. Note that this is not the regular blank boxes in slicers which can be removed from options. How can I overcome this issue?
Much appreciated.
Excel does not have a way to return a true empty cell with a formula in that cell. The blank value is actually a text value and that is what the pivot table is picking up.
If you want to remove these blank cells you may need to clean up the table after the formulas have done their work. A non-destructive way would be to use Power Query (Get & Transform), load the table and replace all blanks with the null value. Then load the query into the spreadsheet and point the pivot table to use the query result.
When your original data source refreshes, the Power Query and the Pivot Table can be refreshed with "Refresh All" in one go.
