← Back to Blog
10 Essential Excel Formulas Everyone Should Know
Mastering Excel formulas can transform how you work with data. Here are 10 essential formulas that every Excel user should know:
1. VLOOKUP
Looks for a value in the leftmost column of a table and returns a value in the same row from a specified column.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
2. INDEX-MATCH
A more flexible alternative to VLOOKUP that can look left and is less prone to errors when columns are inserted.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
3. SUMIFS
Adds all numbers in a range based on multiple criteria.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
4. IFERROR
Returns a custom result when a formula generates an error, and the standard result when no error is detected.
=IFERROR(value, value_if_error)
5. CONCATENATE (or CONCAT/TEXTJOIN in newer versions)
Combines text from different cells into one cell.
=CONCATENATE(text1, [text2], ...)
6. DATE
Creates a date from year, month, and day values, useful for date calculations.
=DATE(year, month, day)
7. NETWORKDAYS
Calculates the number of working days between two dates, excluding weekends and holidays.
=NETWORKDAYS(start_date, end_date, [holidays])
8. COUNTIF
Counts the number of cells within a range that meet a single condition.
=COUNTIF(range, criteria)
9. LEFT/RIGHT/MID
Extracts a specific number of characters from text, from the start (LEFT), end (RIGHT), or middle (MID).
=LEFT(text, [num_chars])
=RIGHT(text, [num_chars])
=MID(text, start_num, num_chars)
10. PMT
Calculates the payment for a loan based on constant payments and a constant interest rate.
=PMT(rate, nper, pv, [fv], [type])
Mastering these formulas will give you a solid foundation for working with data in Excel efficiently.