Foolproof rules for creating formulas and functions

  • Name first: Name cells and ranges before creating formulas & functions that refer to them
  • Start with equal (=)
  • End with ENTER
  • Use your mouse to select references, or use a name
    • Whenever possible, use your mouse to select cells. Do not type cell addresses or ranges as doing so is kludgy and potentially inaccurate.
    • Click/drag on the worksheet to select a cell or range when the reference should change as you fill the formula (i.e. a relative reference)
    • Select a named cell or range if the reference should stay the same as you fill the formula (i.e. an absolute or mixed reference). Or press F3 or choose Insert → Name ? Paste to select a name. It's OK to break the rule and type the name, rather than using your mouse. But only when it's a named cell or range--no typing addresses!!
  • AutoFill the resulting formula: If you've entered a formula properly, you can then AutoFill or copy/paste it, and it will be correct.
Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.