Best Practices for Documenting?

[Guest]

Hello,

I like to annotate and document the purpose and function of workbooks that I
create. This lets others who use my work what has been done, why it has been
done, how it has been done, etc. It is also useful to me when I return to a
workbook for reference after being away from it for a while.

Are there best practices for such documentation within a workbook?
Currently, I create a separate worksheet for notes, lookup tables, etc and
call it "Reference & Lookup" or whatever seems appropriate. Is there a
better / more accepted way to document in Excel?

Thanks!
Tom

 

[crispbd]

Inserting comments in the worksheets seems widely use

 

[JE McGimpsey]

I don't know of any "standards" for documentation, though several of my
clients have internal requirements.

When I provide an application to most of my clients I usually provide:

1) the workbook(s) should be self-documenting to the extent possible.
- Verbose labels.
- Meaningful variable names and named ranges, e.g.,
=PMT(monthly_rate, num_months,loan_amount, 0, due_at_EOM)
rather than
=PMT(A345/12,B332*12,-Sheet3!C294,0,0)
- calculations flow from left to right, top to bottom
- input uses userforms with validation where possible

2) If the client wishes (some emphatically don't), I include run-time
documentation in comments

3) I usually include an annotated separate sheet for each workbook
documenting each formula:
http://www.mcgimpsey.com/excel/listfunctions.html

4) When requested, I provide Help pages.

5) When requested I provide a user manual.


Applications that make use of extensive macros get a very detailed
annotated code base.

 

[Gord Dibben]

In-cell documentation is handy in some cases.

Use the +n to hide text comments. Can be seen in the Formula Bar.

=a2*a1+n("my wage+bonus-4 weekly loan repayments")


Gord Dibben Excel MVP

 

[mathurpc]

Can you please elaborate it a little more - where do I add the +n code?

Thanks

 

[JE McGimpsey]

Right where he had it in the example he gave. Add it anywhere to a
numerical formula, though at the end of the formula is the least
confusing.

For a formula which returns a string, you can use TEXT:

= A1 & B1 & TEXT("documentation goes here",";;;")

Which is even clearer if you define the name "THIS_IS_A_NOTE" as ";;;"
and use:

= A1 & B1 & TEXT("documentation goes here",THIS_IS_A_NOTE)