Microsoft Excel Formulas and Functions

Formulas and Functions

• Arithmetic Operators:
  • + is used for addition
  • - is used for subtraction
  • * is used for multiplication
  • / is used for division
  • ^ is used for exponentiation
• Basic Formulas:
  • The = sign is used to begin a formula
  • A1+B1 adds values in cells A1 and B1
  • A1*B1 multiplies values in cells A1 and B1
• Functions:
  • SUM(range) calculates the sum of a range of cells
  • AVERAGE(range) calculates the average of a range of cells
  • COUNT(range) counts the number of cells in a range that contain numbers
  • IF(logical_test, [value_if_true], [value_if_false]) tests a condition and returns one value if true and another value if false
• Text Functions:
  • LOWER(text) converts text to lowercase
  • UPPER(text) converts text to uppercase
  • PROPER(text) converts text to proper case
  • LEN(text) returns the length of a text string
• Logical Functions:
  • AND(logical1, [logical2],...) returns true if all conditions are true
  • OR(logical1, [logical2],...) returns true if any condition is true
  • NOT(logical) returns the opposite of a condition

Data Analysis

• Data Tools:
  • Sort sorts data in ascending or descending order
  • Filter filters data based on conditions
  • PivotTables summarize and analyze data
• Data Visualization:
  • Charts create charts to display data
  • Tables create tables to display data
  • Sparklines create small charts to display data
• Data Analysis Functions:
  • VLOOKUP looks up a value in a table and returns a value from another column
  • INDEX/MATCH looks up a value in a table and returns a value from another column
  • TREND returns values along a linear trend
  • FORECAST returns a forecasted value based on existing values
• Data Manipulation:
  • Remove Duplicates removes duplicate values from a range
  • Text to Columns splits text into separate columns
  • Data Validation restricts input data to a specific format or range