Test Your Excel Data Model before Building Reporting Components on Top
This best practice for building Excel data models is simple. Make sure your data model does what it’s supposed to do before building dashboard components on top of it. In that vein, here are a few things to watch for:
Test your formulas to ensure they’re working properly. Make sure your formulas don’t produce errors and that each formula outputs expected results.
Double-check your main dataset to ensure that it’s complete. Check that your data table was not truncated when transferring to Excel. Also, be sure that each column of data is present with appropriate data labels.
Make sure all numeric formatting is appropriate. Be sure that the formatting of your data is appropriate for the field. For example, check to see that dates are formatted as dates, currency values are formatted properly, and the correct number of decimal places is displayed where needed.
The obvious goal here is to eliminate easily avoidable errors that may cause complications later.