Microsoft Excel Formulas and Data Analysis

Formulas and Functions

• Arithmetic Operators:
  • + (addition)
  • - (subtraction)
  • * (multiplication)
  • / (division)
  • % (modulus)
• Common Functions:
  • SUM(range): returns the sum of a range of cells
  • AVERAGE(range): returns the average of a range of cells
  • COUNT(range): returns the count of cells in a range that contain numbers
  • MAX(range) and MIN(range): returns the maximum and minimum values in a range
• Logical Functions:
  • IF(logical_test, [value_if_true], [value_if_false]): tests a condition and returns a value based on the result
  • IFERROR(cell, value_if_error): returns a value if an error occurs in a cell
• Text Functions:
  • LEN(text): returns the length of a text string
  • LOWER(text) and UPPER(text): converts text to lowercase and uppercase
  • PROPER(text): converts text to proper case
• Date and Time Functions:
  • TODAY() and NOW(): returns the current date and time
  • DATEDIF(start_date, end_date, unit): returns the difference between two dates in a specified unit (years, months, days)

Data Analysis

• Data Visualization:
  • Charts: column, line, pie, bar, and more
  • Tables: pivot tables and conditional formatting
• Data Manipulation:
  • Sorting: sorting data in ascending or descending order
  • Filtering: filtering data based on conditions
  • Grouping: grouping data by categories
• Data Analysis Tools:
  • PivotTables: summarizing and analyzing large datasets
  • Conditional Formatting: highlighting cells based on conditions
  • What-If Analysis: analyzing scenarios and forecasting outcomes
• Data Visualization Best Practices:
  • Choose the right chart type for the data
  • Use clear and concise labels
  • Avoid 3D charts and excessive use of color