Spreadsheet tips with Lotta Rosenkallums

Data cells and formula cells

©2004, Information Disciplines, Inc., Chicago

Some cells on a spreadsheet contain data, while others contain formulas that refer to other cells. Unfortunately, spreadsheet processors don't enforce any discipline in distinguishing between them. If a user accidentally enters data into a formula cell, the original formula is destroyed and results are almost certain to be in error.

Spreadsheet processors provide several ways of distinguishing between data cells and formula cells:

1. You can set the background color.
2. You can designate protection for certain cells, to disallow data entry.
3. You can conceal some of the formula cells:
   • in a hidden column, or
   • on another worksheet or layer

Formal spreadsheet models should use a combination. Specifically:

• Give data entry cells a distinctive background color. Light blue, gray, or yellow are easy on the eyes. Cells containing formulas, identifiers, tables, or other fixed information should be white.
• Specify cell protection for all cells except those intended for data entry, i.e. the ones with the colored background.
• Use hidden columns and supporting worksheets to simplify what the user sees and to facilitate future change.

Return to Spreadsheet guidelines
IDI home page

Last modified May 15, 2004