Separate data, calculations, and output
Source data must be separated from calculations and output tables. This separation makes it easier to update the data, perform checks, and identify errors.
Minimize Manual Handling
Copy-and-paste operations, deleted rows, and formulas that are modified at each closing are sources of risk. Power Query, structured tables, and consistent naming conventions can often help secure the process.
Create visible controls
- reconciliation with the source;
- duplicate checking;
- detection of missing values;
- period control;
- consistency sums;
- Alerts when the structure of a source file changes.
Avoiding Hidden Dependencies
External links, unused defined names, undocumented macros, and nested formulas make the file unstable. Dependencies should be reduced and documented.
Document the operation
What is the file used for, and who uses it?
What files or systems feed into the model?
What steps are involved in transforming the data?
How can you verify that the output is complete and accurate?
Schedule Maintenance
The file must be able to be taken over by another person. It is necessary to establish a reference version, a change history, a person in charge, and a testing procedure following any changes.
FAQ
Should you use VBA macros?
Not always. Power Query, Excel's modern functions, and a better structure are often enough. VBA remains useful for certain specific needs.
How can you tell if a file has become critical?
When it supports a financial statement, a report, a payment, a filing, or an important decision, it must be treated as a critical tool.
When Should You Replace Excel?
When file size, access rights, collaboration, traceability, or frequency make it difficult to secure the file.