A Generic Spreadsheet Template
Content
Introduction
In October 2017 I talked about End
User Computing in my newsletter. Since that time, I've been involved
in a couple of spreadsheet quality projects. One thing I found is that many
spreadsheet models lack basic things. I've listed them here.
Download Our Excel Starter
Template
To make things easier, I've designed a starter template for your next
spreadsheet project. As you may have guessed, this starter template contains
the elements I describe below. Download the spreadsheet for free here:
Spreadsheet
Model Template. Of course we're open to suggestions. There is a comments
section near the bottom of this page!
A Table of Content
If your workbook tabs do not fit between the tab selection controls and
the scroll bar, your workbook becomes harder to navigate and it will be
difficult for your users to find the information they need.
Consider adding a Table Of Content to your workbook. It makes it easier
to understand and to navigate the model. Your Table of Content might look
like this:
A worksheet with settings
Spreadsheet models often have parameters that affect how the model works.
So a location to contain information about the state of the model, like
the reporting year, the model's build number and etcetera is needed. It
is a very good idea to give those settings a place in your model where they
can be found easily. Here's a screen-shot of the Settings sheet in our free
Spreadsheet
Model Template:
As you can see, each setting has a clear name. In addition, it is a good
idea to apply range names to model-wide settings. It makes it very easy
to use them in formulas and in VBA code.
A Manual
On average, a spreadsheet model is used by 13 people. This means you'll
have to explain your work to others. Important spreadsheets deserve an instruction
manual!
Notice how the Table of Content screen-shot shown above has a Remarks
column. This is a good first attempt of starting a manual: explain what
each worksheet is for. The Manual does not necessarily have to be on a separate
worksheet. If laid out and designed well, a spreadsheet model explains itself.
Clear labelling and a well-thought out and consistent design will go a long
way in making an actual manual obsolete.
If you can, try to keep the instruction manual within the model. A separate
file is harder to maintain and having the manual inside the model makes
it easier to access the manual for your users. Also, you can add hyperlinks
to relevant sections of your manual from the areas in the model that need
some extra explanation.
Having the manual inside the model also has some drawbacks (thanks to
Craig Hatmaker, the first one to comment on this article):
-
Excel is a lousy word processor.
-
Excel workbooks often get copied which creates multiple instances of
the documentation making updates and errata just about impossible.
An alternative method is to store the documentation in a central location
and add a hyperlink in the workbook to said documentation.
Use of cell styles
Designing a model takes a lot of thought. Selecting inputs, choosing
the best structure, designing formulas, creating nice output and perhaps
a dashboard. All very well, but have you considered to use Cell Styles to
format cells, preferably by their function? And to do this consistently
in your entire workbook? Our Spreadsheet model template has a number of
pre-defined styles. They look like this:
Notice how we created a separate Style for each number format?
Here is where Styles are explained.
A worksheet displaying
check calculations
If you want your user to trust your model, include as many check calculations
as you possibly can. Moreover; collect their results on a central check
sheet which also calculates an "All is well" verdict which you can display
on all worksheets. Having sufficient and well-thought out and -structured
checks in your spreadsheet model is no guarantee the model is free of mistakes,
but at least you are conveying the message that you take quality seriously.
A check sheet might look like this one from our Spreadsheet Model template:
Helper routines
(in VBA and a bit of RibbonX code to access them)
Our Spreadsheet Model Template comes with three buttons on the Developer
tab of the ribbon:
The buttons closely interact with the Template:
- Update ToC
updates the Table of Contents sheet
- Update Styles
updates the Styles sheet
- Update Setting Names
takes the information of the Settings table and generates a Range Name
for each of them
By entering FALSE into to the DeveloperMode setting cell you hide the
three buttons.
