Connect and integrate
What we can do together:
- Reduce risk in using Excel formulas, and maintenance and update costs;
- Organize Excel files clarifying their structure and documenting their processes;
- Integrate and create data models tailored to the task.
Let’s get started!
Linking and integrating means listing all the data needed in a given situation and, in the case of Excel, how to structure the file to facilitate data management and minimize the risk of errors.
Risk management in Excel
*Every formula in Excel is a risk factor. The spreadsheet must be designed to minimize this risk.*
There is a sport almost as popular as football/soccer: blaming Excel for mistakes that can even cost bankruptcy or influence macro-economic policies. In most cases, these are gross user errors: Excel’s great flexibility has associated risks that need to be minimized. Preventing these errors is a task often overlooked.
- The level of risk varies, but as a rule:
- Calculating values using a pivot table is safer than using formulas;
- Formulas in tables are safer than isolated or manually replicated formulas;
- Array formulas are safer than regular formulas;
- Formulas with error handling (IFERROR()) are more secure than without it;
- Importing text files is safer than opening them directly;
- Named ranges are safer than using the $A$1:$A$2 referencing system.
- More structured workbooks are safer.
Workbook and speadsheet design
Organizing the sheets in a workbook by function simplifies its structure, reduces error, and makes it easier for others to read.
An Excel workbook can have a virtually unlimited number of sheets. Defining a content type for each sheet helps structure the file and makes it easier for others to understand. At a minimum, there must be four types of sheets:
- Presentation (output) sheets;
- Datasheets;
- Sheets for intermediate calculations;
- Parameter sheets.
Excel files must be appropriately documented, either for future memory or for more effortless reading by others. With a structure in which the relationship between the various objects is more transparent, the documentation can be simplified and focused on logic rather than concrete steps.
Beyond VLOOKUP
The integration of Excel and PowerQuery means that, as a side effect, Excel users are more aware of the importance of good data structure (within users’ skills, not IT-level). A good data structure allows for more effective data analysis and reduces risk, maintenance, and upgrade costs.
It may be technically complex, but a data model is essentially just clarifying the relationships between data and between tables. The users must have the right skills to draw these relationships because they derive from subject-matter knowledge.