Important Excel Functions Used by Data Analysts
1. VLOOKUP:
• Example: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• Usage: Searches for a value in the first column of a table and returns a value in the same row from another column.
2. HLOOKUP:
• Example: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
• Usage: Similar to VLOOKUP, but searches in the first row of a table.
3. INDEX-MATCH:
• Example: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
• Usage: A more flexible alternative to VLOOKUP or HLOOKUP for lookups.
4. SUMIFS:
• Example: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
• Usage: Adds values based on multiple criteria.
5. COUNTIFS:
• Example: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2, ...])
• Usage: Counts cells based on multiple criteria.
6. AVERAGEIFS:
• Example: =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
• Usage: Calculates the average based on multiple criteria.
7. CONCATENATE:
• Example: =CONCATENATE(text1, [text2, ...]) or =text1 & [text2]
• Usage: Combines text from multiple cells into one cell.
8. IF:
• Example: =IF(logical_test, value_if_true, value_if_false)
• Usage: Performs conditional logic based on a specified condition.
9. PivotTables:
• Usage: Allows for dynamic data summarization and analysis in a table format.
10. SUM, AVERAGE, COUNT:
• Examples: =SUM(range), =AVERAGE(range), =COUNT(range)
• Usage: Basic functions for simple calculations on a range of cells.
I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://lnkd.in/dF2KKWBY
Hope this helps you 😊