You are writing about a problem that can be solved if you know what you are doing:
Store your data in a table ( https://www.contextures.com/xlExcelTable01.html ), then make a pivot table out of it.
You will never have problems with missing data.
In fact good practice is to add a check, just to see if your pivot table was refreshed and the data there matches the data in the source table. Just like you make tests in software.