Quick tip: Prevent #DIV/0! and #VALUE! errors in Excel files

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)