Forum Discussion
blanks in my pivot table and data not appearing in pivot table, even after refreshing all.
I suggest you start from scratch. In version 3, delete the formulae at the bottom of the data table (rows 10 to 42). These cause Excel to see 33 records with a blank date. Hence, it adds a blank year (the check box without a year number) to the filter window.
Now convert your data table (Ctrl-T) into a so-called structured table. If you now enter a new date in column E, the table will automatically expand and copy down the formulae from the previous row.
Then, change the data source for the PT to this table, called "Table2" in the attached example. It will also avoid blanks. Referring column A:H is okay as well, but then you'll end up with the blanks that need to be filtered out.
I suggest you start from scratch. In version 3, delete the formulae at the bottom of the data table (rows 10 to 42). These cause Excel to see 33 records with a blank date. Hence, it adds a blank year (the check box without a year number) to the filter window.
Now convert your data table (Ctrl-T) into a so-called structured table. If you now enter a new date in column E, the table will automatically expand and copy down the formulae from the previous row.
Then, change the data source for the PT to this table, called "Table2" in the attached example. It will also avoid blanks. Referring column A:H is okay as well, but then you'll end up with the blanks that need to be filtered out.
Awesome Riny I will work on this and get back to you and thanks for attaching the sample ive tried it and it works well !!