Documenting Excel Projects

Post date: Jan 6, 2018 11:29:47 PM

Excel projects of any significance are very often complicated. Documenting such projects is crucial for auditing and maintainability. Fortunately, Microsoft provides several options for documenting Excel projects.

Workbook Name and Path

This is our starting point. We can create directory and subdirectory names so our workbook path documents whether our project is for a particular customer, general-purpose application/model, or a particular period, etc. We should also make our workbook’s name meaningful so that even before we open our model, we have some idea of its purpose.

Example: …CurrentProjects/Construction/JohnsonStreetBridge/ClassCEstimate.xlsx

Workbook Properties

Click Excel’s FILE tab then click Info to reveal the workbook’s properties. Excel automatically tracks much or our project’s properties such as where our project resides and who last modified it and when. And we can extend Excel's properties with our own custom items that can link to named ranges within our workbook. Properties are also available to view before the workbook is opened and can be searched for by Windows Explore.

Model Structure

Our project structure should segregate Inputs, Process and Outputs. These groupings are a type of documentation indicating the cells’ purpose. I favor segregating sections by worksheet which we should name to make their purpose obvious. Excel’s tables can also provide structure and documentation.

Though not related to documentation, making sure, as much as possible, to organize calculations in reading order (precedents to the left or above) help make models easier to read.

Titles

Add meaningful titles to worksheets, tables, and charts.

Value Labels

Never leave a value unlabeled. Unlabeled cells are not transparent (opaque) and the presence of opaque values makes our entire project opaque. Label every value cell, whether constant or formula. Depending on the value type, labels should contain a very brief description, units of measure, and/or number format (such as date format). At right is one example of good value labeling in an assumptions table (darker grey cells are input enabled).

Cell Comments

When good labeling is insufficient we can right click any cell and select Insert Comment to add a short description. Excel displays this comment when our mouse hovers over the cell. Excel places a small red triangle in the cell’s upper right corner to indicate a comment’s presence.

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

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.

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.

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.

In Formula Documentation

After posting this I received this comment on LinkedIn:

Gabriel Hargens, CSSBB, CRISC, ITILF - Hey Craig you missed the +N("[INSERT YOUR DOCUMENTATION HERE"]) trick (for inline commenting of formulas.)

Here is a link to help explain Gabriel's trick:

Like most everything there are pros and cons to this. Ian O'Donoghue warns us:

...The N() function is not passive so there is a risk of unintended consequences, including errors. ... For example, we could use N() to document a formula:

=IF(A1>=0,B1,"Must be positive")+N("Check that input value is positive")

But if the value in A1 is not >=0, or B1 contains anything other than a number, then the formula returns a #VALUE! error rather than the expected text "Must be positive".