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

Learn about arithmetic operators and basic formulas in Microsoft Excel, including functions like SUM, AVERAGE, and COUNT. Test your skills in creating formulas and functions in Excel!