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
- Design the layout of the sheet so it makes sense:
- The sheet should be easy to navigate, and data entry should follow a
- 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
Consider the following formulas: =sum() to total data
=round() to avoid
=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
- This can take many forms, but ultimately, it ensures that only valid data
- 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
- Hiding formulas prevents users from seeing proprietary formulas (see
- 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
- 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)
OfficeArtilces.com debuted on May 26, 2005.
MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on
this web site are provided "as is" and we do not guarantee that they can be used
in all situations.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered
trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.
All contents © 1998-2014 by MrExcel Consulting | All rights reserved