0% found this document useful (0 votes)
25 views4 pagesUseful Excel Formulas - Intermediate To Advance
The document provides a comprehensive overview of Excel formulas, categorized into various functions including Text, Date & Time, Math & Statistical, Logical, Lookup & Reference, Financial, Array & Dynamic, Advanced, and Power Functions. Each category lists specific functions along with their descriptions and usage examples. This serves as a guide for users to enhance their Excel skills from intermediate to advanced levels.
Uploaded by
Murtaza SunelwalaCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
25 views4 pagesUseful Excel Formulas - Intermediate To Advance
The document provides a comprehensive overview of Excel formulas, categorized into various functions including Text, Date & Time, Math & Statistical, Logical, Lookup & Reference, Financial, Array & Dynamic, Advanced, and Power Functions. Each category lists specific functions along with their descriptions and usage examples. This serves as a guide for users to enhance their Excel skills from intermediate to advanced levels.
Uploaded by
Murtaza SunelwalaCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
/ 4
Excel Formulas – From Intermediate to Advanced
1. Text Functions (working with words, cleaning data)
• LEFT(text, num_chars) → Extracts characters from the left.
• RIGHT(text, num_chars) → Extracts characters from the right.
• MID(text, start_num, num_chars) → Extracts characters from the middle.
• LEN(text) → Counts number of characters.
• TRIM(text) → Removes extra spaces.
• UPPER(text) → Converts to uppercase.
• LOWER(text) → Converts to lowercase.
• PROPER(text) → Capitalizes first letter of each word.
• TEXT(value, format_text) → Converts value into a text format (e.g., date, number).
• CONCAT(text1, text2, …) → Joins text strings (new Excel).
• TEXTJOIN(delimiter, ignore_empty, text1, …) → Joins text with separators.
• SEARCH(find_text, within_text, [start_num]) → Finds text position (case-
insensitive).
• FIND(find_text, within_text, [start_num]) → Finds text position (case-sensitive).
• SUBSTITUTE(text, old_text, new_text, [instance_num]) → Replace specific text.
2. Date & Time Functions (time management, schedules)
• TODAY() → Current date.
• NOW() → Current date + time.
• DATE(year, month, day) → Creates a date.
• EDATE(start_date, months) → Add/subtract months.
• EOMONTH(start_date, months) → Last day of month.
• DATEDIF(start_date, end_date, unit) → Difference between dates (Y, M, D).
• NETWORKDAYS(start_date, end_date, [holidays]) → Working days between two
dates.
• WEEKDAY(date, [return_type]) → Day of the week.
• WORKDAY(start_date, days, [holidays]) → Next working day.
• HOUR(time) → Extract hour.
• MINUTE(time) → Extract minute.
• SECOND(time) → Extract second.
3. Math & Statistical Functions
• SUM(A1:A10) → Adds values.
• SUMIF(range, criteria, [sum_range]) → Sum with condition.
• SUMIFS(sum_range, criteria_range1, criteria1, …) → Multiple conditions.
• AVERAGE(A1:A10) → Mean.
• AVERAGEIF(range, criteria, [average_range]) → Conditional average.
• AVERAGEIFS(average_range, criteria_range1, criteria1, …) → Multiple conditions.
• ROUND(number, num_digits) → Rounds number.
• ROUNDUP(number, num_digits) → Rounds up.
• ROUNDDOWN(number, num_digits) → Rounds down.
• INT(number) → Integer part only.
• MOD(number, divisor) → Remainder after division.
• POWER(number, power) → Exponent.
• SQRT(number) → Square root.
• PRODUCT(A1:A10) → Multiply all values.
• LARGE(array, k) → Kth largest.
• SMALL(array, k) → Kth smallest.
• RANK(number, ref, [order]) → Ranking.
• MEDIAN(A1:A10) → Middle value.
• MODE(A1:A10) → Most frequent value.
4. Logical Functions (decision-making)
• IF(condition, value_if_true, value_if_false)
• IFS(condition1, result1, condition2, result2, …) → Multiple IFs simplified.
• IFERROR(value, value_if_error) → Avoid errors.
• AND(condition1, condition2, …) → All must be TRUE.
• OR(condition1, condition2, …) → Any TRUE.
• NOT(condition) → Reverse logic.
• SWITCH(expression, value1, result1, …) → Alternative to nested IFs.
5. Lookup & Reference Functions (very powerful )
• VLOOKUP(lookup_value, table_array, col_index, [range_lookup]) → Vertical
lookup.
• HLOOKUP(lookup_value, table_array, row_index, [range_lookup]) → Horizontal
lookup.
• INDEX(array, row_num, [col_num]) → Get value by row/column.
• MATCH(lookup_value, lookup_array, [match_type]) → Position of value.
• XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],
[match_mode], [search_mode]) → Modern VLOOKUP replacement.
• LOOKUP(lookup_value, lookup_vector, result_vector) → Older lookup function.
• OFFSET(reference, rows, cols, [height], [width]) → Dynamic ranges.
• INDIRECT(ref_text, [a1]) → Convert text to reference.
6. Financial Functions (finance/accounting use )
• PMT(rate, nper, pv, [fv], [type]) → Loan payment.
• IPMT(rate, per, nper, pv, [fv], [type]) → Interest portion.
• PPMT(rate, per, nper, pv, [fv], [type]) → Principal portion.
• NPV(rate, value1, …) → Net present value.
• IRR(values, [guess]) → Internal rate of return.
• FV(rate, nper, pmt, [pv], [type]) → Future value.
• PV(rate, nper, pmt, [fv], [type]) → Present value.
7. Array & Dynamic Functions (modern Excel )
• UNIQUE(array, [by_col], [exactly_once]) → Unique values.
• SORT(array, [sort_index], [sort_order], [by_col]) → Sort.
• FILTER(array, include, [if_empty]) → Filter with conditions.
• SEQUENCE(rows, [columns], [start], [step]) → Generate numbers.
• RAND() → Random between 0–1.
• RANDBETWEEN(bottom, top) → Random between numbers.
8. Advanced Functions
