Spreadsheet Design Tips for Microsoft Excel

The information in this article provided by Ken Puls of www.ExcelGuru.ca. Thanks, Ken!

There are three types of spreadsheets built in the world today:

The "quick-n-dirty-for-your-eyes-only" type spreadsheet. It doesn't need to be pretty, and doesn't need a ton of time spent making it readable or understandable for the rest of the world.
The well-thought-out, re-usable, stable spreadsheet. This one is used many times, always works as intended, and can be relied on over and over again.
Version 1, trying to act as version 2. This is by far the most frustrating spreadsheet out there. Usually they have evolved from a "quick 'n' dirty" that had to be handed off to someone else, but whatever their origin, they are hard to use, and their accuracy may be called into question. Many times the users have improperly maintained them, overwritten important formulas, or just plain lost the methods needed to use it properly.

Every well designed spreadsheet has one thing in common: The designer invested time and effort planning and designing the sheet to meet their eventual goals, and the needs of the users. In addition, the designer also made attempts to ensure that the spreadsheet would still be functioning as they intended, long after they were gone.

If you want to build a solid, reusable Excel application, the following is a list of steps which you may want to consider before you start creating your spreadsheet.

Design the layout of the sheet so it makes sense:

The sheet should be easy to navigate, and data entry should follow a logical flow
Consider grouping data entry fields together, instead of making the user jump around to find them. Keep in mind that if worksheet protection is activated, the tab key will take you from one unprotected cell to the next, skipping the protected cells

Make it obvious where the user is supposed to enter data:

Colour the cells where data should be entered (I prefer to use a light green shading, as I find it easy on the eyes)
Unprotect the data entry cells

Name the important cells and ranges:

Makes your formulas more readable
Makes your formulas self documenting
Saves you from having to update your VBA code every time you manipulate your worksheet (VBA does not re-index its code when you modify your worksheet)

Use formulas wherever possible:

Ensure that the user never has to do anything manually that can be done by Excel.

Consider the following formulas: =sum() to total data

=round() to avoid rounding errors

=today() or =now() to input the current date

=if() to account for conditions or problems

=hlookup() or =vlookup() to look up data in tables, based on values that your user supplies (or are computed by Excel)

Add form controls (non-VBA):

These controls come from the "Forms" toolbar, are easy to set up and maintain, and do not require any VBA skills
They make a visually appealing method of forcing choices
Drop down lists (combo boxes) can be used to look up data from lists (ensuring that your users only choice is from a list you set up)
Option buttons (placed in frames) force your user to choose one of a defined number of options

Apply Data Validation:

This can take many forms, but ultimately, it ensures that only valid data is entered
Preventative data validation methods Cell level data validation ensures that only valid data can be entered directly into a cell
Restricting values through the use of form controls (see below)
Reactive data validation methods Cell formatting can make it obvious that something is not rendering correctly
Formulas with messages can make it obvious that an error has occurred, or that invalid data has been entered

Apply protection options:

Remember that cells are protected by default, so you need to unprotect them if you want users to be able to access them once sheet level protection is turned on
Hiding ranges or sheets can prevent users from seeing or manipulating critical information
Hiding formulas prevents users from seeing proprietary formulas (see caveat below)
Consider protecting all your worksheets with or without a password. I frequently protect sheets with no password, as it allows the end users the freedom to change something if absolutely necessary, but makes sure that they don't do it by accident
If your file should only be used by authorized parties, consider implementing a workbook level password

WARNING! Any VBA guru can remove your sheet level protection very easily, so if your data is extremely confidential, Excel may not be the program for you. Workbook level security is far more difficult to remove, but not impossible, as there are several hacking programs available for sale on the internet. (See more here.)

Consider saving your workbook as a template:

Best if your file is going to be used over and over again, and it is important that it always start fresh and clean every time.
The file is available by choosing "New" from the file menu.
Opening the file by either the above method, or by double clicking it from the Windows Explorer creates a copy of the template, so that the user cannot overwrite your file.

WARNING! A user who opens your template by choosing "Open" from the file menu will have regular access to modify your template.

That Extra Touch - VBA Automation:

Virtually anything that you do in Excel, with the exception of data input, can be automated... and sometimes even that can be done!
Some examples of automation are: Having Excel automatically enter your username in the "prepared by" field of your file Truly powerful when coded into a template, as the program automatically figures out who opened it.
Clearing out all the data entry fields for new data entry
Printing worksheet(s), while forcing the correct page setup
Copying current data to historical tables
Saving backups with a predefined file name
Creating custom menus to do things in your workbook(s)

Back to top...

Watch the latest videos on YouTube.com