Do you keep notes about the Excel formulas and macros that you create? What’s your documentation system, if you have one? Is it fancy or simple, and is it electronic or paper-based? Or is it all stored in your head?
Keeping Excel Notes
A couple of months ago, I got an email from Paul Hanson, who runs Westport Consulting, in Melbourne, Australia.
Paul has created a couple of 8.5″ x 11″ paper notebooks for spreadsheet users.
- There are 4 index sheets at the front of the notebook, where you can list each formula or function and its page number, after you enter notes about it.
- Then there are 100 nicely-structured pages for your formula and function notes.
Here’s a sample page from one of the notebooks. I like the clean, well-organized layout for the note pages.
My Excel Notes System
Paul offered to send me sample copies of his Excel notebooks, but I declined his offer, with thanks.
Instead of using paper, here’s what I do:
- Excel formula notes are stored in Notepad++ or in Excel workbooks, where I can do a digital search later.
- Favourite macros are stored in my “DebraMacros” file (xlam format), where I can always find and use them.
- Most macros have comments, to explain the steps, and I always hope that future me understands them!
- For some formulas and macros, I make blog posts, or pages on my Contextures website. Later, Google can help me find those!
Do You Keep Paper Notes?
Even though I don’t keep paper notes about Excel formulas, lots of people probably do.
Do you keep paper notes about your Excel formulas or custom functions, or your macros?
If you do, what kind of system do you have? Do you just scribble notes on bits of paper, and hope that you can find them later? Or do you have a fancy-ish? system for your Excel notes?
Do You Keep Electronic Notes?
If you don’t keep paper notes, do you have some kind of electronic system instead?
If you do, what program(s) do you use? Do you keep simple notes, or well-organized, carefully detailed notes?
Are Your Excel Notes Online?
Do you use a blog or forum posts to store your notes about Excel, and use Google (or something else) to search for them later?
In the olden days, I’d post things in Microsoft’s Excel newsgroups (sigh), and could search for them in Deja-Vu (sigh 2).
Now, I use Google to search for my own blog posts, and other people’s Excel posts or forum replies. Usually, I’m lucky, but it’s not a foolproof system.
Paul Hanson’s Paper Excel Notebooks
Anyway, if you’re a paper notes person, you can get copies of Paul Hanson’s paper Excel notebooks on Amazon.
First, there’s an Excel Power User Spreadsheet Notebook, in which you can store notes on your worksheet formulas and your VBA functions.
There’s also a simpler version, that’s for storing notes about formulas only – The Spreadsheet Notebook.
_________________________
Keep Notes on Excel Formulas and Macros
_________________________
Debra:
Have you tried the N() function? It allows you to keep a note inside the formula, so that if you copy it, the explanation goes with it. For instance,
=TODAY()-60+N(“number of days in the past”)
will still produce 12/6/2019 if today is 2/4/2020. You can use it multiple times in a formula, so you can document each piece for those monster formulae using several lines. Unfortunately, it does make the formula longer…
It evaluates to zero, so addition or subtraction of a term works fine, but it won’t work well in a text string formula, as it will add a zero into your text string.
Thanks for the good tips each week!
Thanks, Glenn, and I have tried the N function, and even wrote about it, way back in 2011.
https://contexturesblog.com/archives/2011/01/23/30-excel-functions-in-30-days-22-n/
I’ve only used it a couple of times in my Excel work though!
I wouldn’t keep paper notes, as they just end up in a drawer. I’d end up with hundreds of sheets and I couldn’t be bothered organising them.
For extensive notes, I create a Word document and put a hyperlink at the top of a page or on a control sheet.
For formula notes, I put them into a comment to help others understand them. However most people don’t realise that you can search comments. in the Find dialog box, click on the Options button, and in the ‘Look in’ drop down list select ‘Comments’. You can then search a whole workbook for them. Where this is useful is if I have used a complex formula but a year later I can’t remember where it was in a big workbook.
Unfortunately this only works with old-school comments which are now called Notes, it doesn’t work with the new Comments functionality (yet).