Microsoft Excel Formula Basics

Formulas in Microsoft Excel

Formula Basics

• A formula is an expression that calculates a value from values in other cells
• Formulas begin with an equal sign (=) followed by a calculation
• Example: =A1+A2 adds the values in cells A1 and A2

Formula Types

• Arithmetic formulas: perform mathematical operations (e.g., +, -, *, /, %)
• Comparison formulas: compare values (e.g., =, <, >, <=, >=)
• Logical formulas: evaluate true or false conditions (e.g., IF, AND, OR)
• Text formulas: manipulate text strings (e.g., LEN, LOWER, UPPER)
• Date and time formulas: work with dates and times (e.g., TODAY, NOW, EOMONTH)

Formula Operators

• Arithmetic operators:
  • + (addition)
  • - (subtraction)
  • * (multiplication)
  • / (division)
  • % (modulus)
• Comparison operators:
  • = (equal to)
  • <> (not equal to)
  • < (less than)
  • > (greater than)
  • <= (less than or equal to)
  • >= (greater than or equal to)
• Logical operators:
  • AND (logical and)
  • OR (logical or)
  • NOT (logical not)

Formula Errors

• #NAME?: formula contains a typo or unknown name
• #VALUE!: formula contains a value that is not a number
• #REF!: formula references a cell that does not exist
• #DIV/0!: formula attempts to divide by zero
• #N/A: formula contains an invalid argument or function

Formula Tips

• Use absolute references ($A$1) to lock cell references
• Use relative references (A1) to allow formulas to adjust when copied
• Use named ranges or references to make formulas more readable
• Use the F2 key to edit a formula without leaving the cell

Formula Basics

• Formulas in Microsoft Excel begin with an equal sign (=) followed by a calculation, such as =A1+A2, which adds the values in cells A1 and A2.

Formula Types

• Arithmetic formulas perform mathematical operations like addition, subtraction, multiplication, division, and modulus.
• Comparison formulas compare values using operators like equal to, not equal to, greater than, and less than.
• Logical formulas evaluate true or false conditions using functions like IF, AND, OR, and NOT.
• Text formulas manipulate text strings using functions like LEN, LOWER, UPPER, and more.
• Date and time formulas work with dates and times using functions like TODAY, NOW, EOMONTH, and more.

Formula Operators

• Arithmetic operators include + for addition, - for subtraction, * for multiplication, / for division, and % for modulus.
• Comparison operators include = for equal to, for not equal to, for greater than, `` for greater than or equal to, and more.
• Logical operators include AND for logical and, OR for logical or, and NOT for logical not.

Formula Errors

• #NAME? errors occur when a formula contains a typo or unknown name.
• #VALUE! errors occur when a formula contains a value that is not a number.
• #REF! errors occur when a formula references a cell that does not exist.
• #DIV/0! errors occur when a formula attempts to divide by zero.
• #N/A errors occur when a formula contains an invalid argument or function.

Formula Tips

• Use absolute references (AAA1) to lock cell references and prevent them from changing when the formula is copied.
• Use relative references (A1) to allow formulas to adjust when copied.
• Use named ranges or references to make formulas more readable and easier to maintain.
• Press the F2 key to edit a formula without leaving the cell.