blanks in my pivot table and data not appearing in pivot table, even after refreshing all.

Occasional Contributor

I have two versions of my workbook here, version 3 and 4 attached here. I created version 4 to see if I could get rid of the problem, basically I cant get rid of the blanks that appear on my pivot table. As I add new rows to my database on sheet 1, I get these blanks on the pivot table.


I want to give this workbook to someone with no data yet in the sheet 1 datatable. They will simply be adding  a date in the date column, dollar amount in the "invoice" column, invoice number and client name.


They should then click on the pivot table in sheet 2, refresh all or CTRL ALT F5 to update the pivot table and graph (version 3 workbook).


but these BLANKS are driving me nuts.


the other problem is, as I add more data to sheet 1, the data seems to appear in the FIELD LIST FILTERS but UNTICKED. Why is that? Even if I Refresh all, the fields remain unticked and dont appear inside of the pivot table. 


I didnt have that problem before.


WeChat Screenshot_20200407113612.pngAnnotation 2020-04-07 112914.jpgAnnotation 2020-04-07 112408.jpgAnnotation 2020-04-07 112115.jpgAnnotation 2020-04-07 112103.jpgAnnotation 2020-04-07 112047.jpg

3 Replies
Best Response confirmed by jitterbug888 (Occasional Contributor)


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 !!