Recently I was helping a client visualize their excel data using Power BI and ran across an error in Power BI when it couldn’t deal with “#DIV/0!” as a cell value in Excel:
“DataFormat.Error: Invalid cell value ‘#DIV/0!’.”
You get this error in excel whenever you have a calculated formula that is trying to divide by zero or a blank cell. Similarly, you get “DataFormat.Error: Invalid cell value ‘#VALUE!'” typically when your data formats don’t match – like there’s a word in a number-formatted column.
It’s a simple fix: just modify your Excel formula to be wrapped in =IFERROR((YourFormula),0) so that if your formula attempts to divide a value by zero, it returns a value of zero instead of an error code.
=IFERROR(Your_Formula, Value_If_Error)
