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.