Forum Discussion
PivotTable does not group column with mix of formulas and dates
Here's an example.
Tab Expenses - does not have an IF formula in Stat column. Empty lines do not have any formulas at all.
Pivot (date) tab - has a PivotTable that has Date from source table used as Columns, Grouped by date. So that I can see months and years and not each date seprately.
Tab Expenses (formulas in Stat col) - has IF formula in Stat column copied in whole column. Few cells have specific date instead of the formula (they are highlighted with yellow).
Pivot (Stat) tab - has a PivotTable that uses Stat from source table (from Expenses (formulas in Stat col) sheet) instead of Date as Columns. I can't Group it by Years and Months.
I'd add helper column to the source as
which returns only two values.
We may filter PivotTable on this value to exclude blank dates. That could be classic filter or slicer
Slicer itself could be moved on another sheet, as variant into hidden one, if we always use TRUE only.
Even better not to keep blank rows in structured table, it expands automatically if you add new data at the bottom.
