I have a straight table and pivot that contains null or missing values. When user want to exports the tables to excel, sometimes he/she wants to add up selected cells across a row using the arithmetic operator '+' and paste that same formula down the column. The problem is when one of those cells contain a null value excel returns the #VALUE! error.
I've tried replacing the null and missing symbols within the tables with nothing, but when the tables exports to excel it's not equivalent to an empty cell.
(There's an over-simplified example attached, export the chart to excel and try =B2+D2, you'll get #VALUE!, but try =D2+ any cell outside the table and it will actually calculate a value for you)
Replacing the null symbol with '0' works, but for aesthetic reasons we don't want a bunch of 0s all over the tables . I've also tried changing text values to match the background color in the chart (so that the null values represented by '0' are there, but just can't be seen) and I'm not so happy with that just because once it's in excel if someone happens to accidentally overwrite the cell value with a different value you won't see it unless you have the cell selected.