Introduction to Common Excel Functions

Jun 22, 2024

Introduction to Common Excel Functions

Aggregate Functions

  • Sum: Adds all numbers in a range of cells.
  • Average: Calculates the average by summing all numbers and dividing by count.
  • Count: Counts the number of cells in a range that contain numbers.

Date Functions

  • YEAR(date): Extracts the year from a date.
    • Note: Excel can't process dates before 1900.
  • MONTH(date): Extracts the month from a date.
  • DAY(date): Extracts the day from a date.

Text String Manipulation

  • UPPER(text): Converts a text string to uppercase.
  • LOWER(text): Converts a text string to lowercase.
  • PROPER(text): Converts a text string to proper case (first letter in each word to uppercase).

Logical Functions

  • IF(logical_test, value_if_true, value_if_false): Checks a condition and returns one value if true and another if false.
  • COUNTIF(range, criteria): Counts the number of cells within a range that meet a given condition.
    • Example: COUNTIF(range, ">60")
  • AVERAGEIF(range, criteria): Returns the average of all cells in a range that meet a given condition.
    • Example: AVERAGEIF(range, ">60")

Lookup Functions

  • VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]): Searches for a value in the leftmost column and returns a value in the same row from a specified column.
    • Note: Ensure to anchor the table array.
  • XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]): Searches a range or array for a match and returns the corresponding item from a second range or array.
    • Preferred due to simpler syntax and flexibility.
  • HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]): Similar to VLOOKUP but searches horizontally.

Tips & Notes

  • Always anchor the table array in lookups to avoid shifting issues.
  • VLOOKUP is limited by needing the lookup value in the leftmost column, whereas XLOOKUP is more flexible.
  • HLOOKUP is less commonly used than VLOOKUP and XLOOKUP.

Conclusion

  • These functions are essential for day-to-day Excel usage, especially in data analysis scenarios.
  • Practice regularly to become proficient in using these tools.

For further learning, check out Analyst Builder for more resources on Excel, SQL, Pandas, Tableau, and more.