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.*

image-leftThere 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.

image-leftAn 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


image-left 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.