Beyond the Help files . . .



Access  ¨  Excel  ¨ FrontPage ¨ Outlook ¨ PowerPoint ¨  Word ¨    Miscellaneous  ¨  Tutorials  ¨  Windows  ¨  Free Downloads



Spreadsheet Design Tips for Microsoft Excel

The information in this article provided by Ken Puls of Thanks, Ken!

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

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.

  1. Design the layout of the sheet so it makes sense:
  1. Make it obvious where the user is supposed to enter data:
  1. Name the important cells and ranges:
  1. Use formulas wherever possible:

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)

  1. Add form controls (non-VBA):
  1. Apply Data Validation:
  1. Apply protection options:

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.)

  1. Consider saving your workbook as a template:

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

  1. That Extra Touch - VBA Automation:


Back to top... debuted on May 26, 2005. 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