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.

Many tabs makes a model hard to navigate

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 Table Of Content

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:

Settings sheet

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:

Styles in our Excel model

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:

Check sheet in our Excel model

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:

Helper buttons for our Excel model

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.


Comments

All comments about this page:


Comment by: Craig Hatmaker (8-5-2020 14:50:00) deeplink to this comment

Hi Jan Karel - You know I bow to your expertise, but...

I completely understand having user documentation "in the model" for ease of distribution. However, Excel is a lousy word processor. Another problem is Excel workbooks often get copied which creates multiple instances of the documentation making updates and errata just about impossible.

To overcome these issues I create my documentation in MS Word, Export to PDF, then post in Dropbox, or Google drive, or OneDrive. I then add a hyperlink to the PDF in the workbook.

Pros:
1) Documentation looks superior
2) Workbook carries link so distribution is easy.
3) Link makes documentation 'feel' like it is in model.
4) Only one place to make updates
5) Updates are immediately available to all users


Comment by: Jan Karel Pieterse (8-5-2020 16:41:00) deeplink to this comment

Hi Craig,

Thanks for your comment. Indeed, having the documentation in the file does pose the problem of how to keep that information updated. Good suggestion, I have updated the page accordingly.


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].