Very true, and that's why I prefaced my comment with "only helpful to an extent". In some cases, data validation[1] on the cells directly can help. Particularl

Very true, and that's why I prefaced my comment with "only helpful to an extent". In some cases, data validation[1] on the cells directly can help. Particularly if the data is going to be manually entered by the end user, rather than a bulk copy/paste job from elsewhere. In other cases, you apply defensive checks within the PowerQuery processing. If a field is supposed to contain US-formatted phone numbers, you can explicitly set the column type to text, split at dashes and keep the first part, verify it only contains numerical content, optionally check for non-sense values (all zeros, all nines, 12345, etc), then left pad it with zeros to ensure they're all 5 digits. You can also package up all of those steps into a function that can easily be added into future worksheets via copy/paste and save you from re-creating the wheel. And depending on the data itself, it may be handy to call out to a third-party API for validation/normalization. To maintain sanity, you can also create a helper column for validation. If any of the columns can't be "safely" normalized or validated, you update this field with details on what failed. At the end, split out any records where that column is not null into its own table. That way you both filter those records out of the "final" table, and can also easily add in an Exceptions Report section to the spreadsheet, clearly calling out the rows that had issues and providing the error details to the end user. It sounds complicated, and it is compared to the alternative (Excel's undefined behavior silently swallowing it). But it's actually incredibly straightforward to implement and a fairly natural design pattern for someone coming from a software development background. You just start treating Excel as a UI and PowerQuery as a backend. All of the business/processing logic gets decoupled into PowerQuery, and formulas become primarily tools for presentation logic (such as adding an an Exceptions notice to the primary worksheet if COUNT(EXCEPTION_TABLE)>0). [1] https://www.howtoexcel.org/tips-and-tricks/11-awesome-examples-of-data-validation/ https://www.howtoexcel.org/tips-and-tricks/11-awesome-exampl...