Documenting Excel Projects

Function Choice as Documentation

Excel sometimes provides multiple functions to reach a result. When selecting functions choose the one most descriptive of intent. As an example, SUMIFS and SUMPRODUCT are often interchangeable. SUMIFS isn't an option for users of older Excel versions but when SUMIFS is an option it conveys intent more readily than SUMPRODUCT.

Names and Structured References

These are a type of label and they can turn cryptic cell referenced based formulas into self-documenting formulas.

Example: =C2-B2 can become =Income-Expenses

See also: https://support.office.com/en-us/article/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e

Name Comments

Peter Bartholomew reminded me that names have their own comment section allowing us even more descriptive text.

Textboxes

After I initially posted this page Patrick O'Beirne pointed out Textboxes are "Flexible in size and movable." Textboxes are similar to comments but are always visible making them valuable when we want something displayed whether the mouse hovers over the cell are not.

Data Validation

We should add data validation (DATA > Data Validation) to all user input cells. The data validation’s restrictions are essential for maintaining model integrity and on their own provide critical information about the cell. In addition to validation rules we should complete the Input Message parameter which displays, like comments, when our mouse hovers over the cell.

See Also: https://support.office.com/en-us/article/Apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249

NOTE! We can use Data Validation Input Messages as alternatives to Comments.

Hyperlinks

We can add hyperlinks to most cells (PivotTable cells are an exception) which, when clicked, take us to additional information or another section of our workbook. Many modelers use hyperlinks only for things like a Table of Contents but they can be used for context sensitive information for formula cells as well. Use this when a simple comment or data validation input message is insufficient for documentation.

See Also : https://www.techrepublic.com/blog/microsoft-office/tip-a-quick-way-to-create-hyperlinks-in-excel/

Documentation Worksheets

We can create one or more worksheets dedicated to explaining our project’s overall purpose and structure.

NOTE! Do not repeat properties captured in Excel’s Workbook Properties. Duplicated information is a leading cause of data synchronization problems.

External Documentation

As an alternative to documentation worksheets I prefer creating end user and technical documentation in Microsoft Word and publish to PDFs because MS Word is far superior to Excel for this purpose and PDFs are universally readable. The disadvantage of this method is it creates two objects (the Excel workbook and the PDF) for one project. To mitigate this we can add a hyperlink within our Excel workbook to the documentation.

For advanced developers, create a project tab in the Excel Ribbon with a documentation icon. In the example below the About group in this Ribbon tab includes the following icons:

• About - Displays selected Excel Properties and copyright information

• Guide - Links to End User and Technical documentation (PDF)

• Website- Link to the developer’s website where additional references are available.