Highline Excel 2016 Class 02-01 Excel’s Golden Rule

Excel’s Golden Rule

Full Rule:

If a formula input can change, put it into a cell and refer to it in the formula with a cell reference.
If a formula input will not change, you can type it into a formula.
ALWAYS Label Your Formula Inputs!!!

Details of Rule:

If a formula input can change, put it into a cell and refer to it in the formula with a cell reference.

Examples of formula inputs that can change:

  • Tax Rates.
  • Commission Percentages.
  • Criteria for counting or adding.

If a formula input will not change, you can type it into a formula.

Examples of formula inputs that will Not change:

  • Number of months in a year: 12.
  • Number of hours in a day: 24.
  • Answers to a question like: “Yes” or “No”.

Always label your formula inputs so that the formula input can be clearly understood by any user of the spreadsheet solution; by doing this we properly “document the spreadsheet solution (spreadsheet model).

Why Excel’s Golden Rule?

It provides good documentation of the spreadsheet solution or spreadsheet model:

  • It is easy to understand how the spreadsheet is constructed if the formula inputs are visible on the face of the spreadsheet.
  • The labels for the formula inputs indicate exactly what the formula input represents.

Easy to Update the spreadsheet solution or spreadsheet model:

  • It is easy to change the formula inputs when they are listed on the face of the spreadsheet.

The inventors of the spreadsheet, Dan Bricklin and Bob Frankston, envisioned the first spreadsheet as a visual calculator, where all formula inputs where visually list on the face of the spreadsheet so that changes could be easily and quickly made.

Violating Excel’s Golden Rule by “Hard Coding” leads to errors:

  • “Hard Coding” means you type the formula input directly into the formula, like with the tax deduction formula: =ROUND(A44*0.0765,2).
  • Research has shown that the #1 causes most common error in spreadsheets is “Hard Coding”
  • It is very hard to accurately and consistently change formula inputs if values are “Hard Coded” into formulas.




Be the first to comment

Leave a Reply