Data integrity and Accuracy is the most important thing that must be addressed before attempting data analysis or reporting.
IMPORTANT: Having clean data must be your priority if you intend to carry out a lot of analysis or you intend to automate your reports. Spend the time required to educate those in charge of your data to always follow the 7 rules when preparing, storing and retrieving data.
1. One Row of Headings
Each column must have a descriptive heading typed out in the first row.
2. No Empty Rows
You must have content in at least one cell of each row. Never have a completely empty row.
3. No Empty Columns
Just as above. You should never have a completely empty column
Note: If you have headings for each column then this means you do not have any empty columns.
4. No obstructions around your data
DO NOT type comments at the end of your data or insert a total row at the bottom, or type something directly above your headings. All these examples are obstructions that break the rule.
5. All Dates must be in a Single Column
Note: Dates are arguably the most important field in your data, they must be entered vertically (each record/each row, must have a date). If this rule is broken, you will be unable to automatically create reports by month, quarter and year or take advantage of date intelligence calculations in Power Pivot.
6. Every Unique Data must have its own Column
e.g. You cannot have a column containing department names as well as dealer names.
Each of these data types must have their own columns.
7. No Totals or Subtotals anywhere in your Table
Totals and subtotals are for your report sheet, they have absolutely no place in your data sheet.