A Generic Spreadsheet Template

Content

• Introduction
• Download Our Excel Starter Template
• A Table of Content
• A worksheet with settings
• A Manual
• Use of cell styles
• A worksheet displaying check calculations
• Helper routines

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.