Most useful Excel Formulas

Amit Mahour Thursday, April 17, 2025

Excel Formulas





Here's a breakdown of some of the most useful Excel formulas, categorized for clarity:

1. Basic Arithmetic and Aggregation:

  • SUM: Adds values.
    • =SUM(A1:A10): Sums the values in cells A1 through A10.
    • =SUM(A1, B5, C8): Sums specific cells.
  • AVERAGE: Calculates the average.
    • =AVERAGE(A1:A10): Averages the values in cells A1 through A10.
  • COUNT: Counts the number of cells containing numbers.
    • =COUNT(A1:A10): Counts the numerical values in cells A1 through A10.
  • COUNTA: Counts the number of non-empty cells.
    • =COUNTA(A1:A10): Counts all non-blank cells in the range.
  • MAX: Finds the maximum value.
    • =MAX(A1:A10): Finds the largest number in cells A1 through A10.
  • MIN: Finds the minimum value.
    • =MIN(A1:A10): Finds the smallest number in cells A1 through A10.

2. Logical Functions:

  • IF: Performs a logical test and returns one value if true, another if false.
    • =IF(A1>10, "Yes", "No"): Returns "Yes" if A1 is greater than 10, otherwise "No".
    • =IF(AND(A1>0,B1<100), "In Range", "Out of Range") : Uses the AND function to test multiple conditions.
    • =IF(OR(A1="Red", A1="Blue"), "Color Match", "No Match"): Uses the OR function to test multiple conditions.
  • AND: Returns TRUE if all arguments are TRUE.
    • =AND(A1>10, B1<20)
  • OR: Returns TRUE if any argument is TRUE.
    • =OR(A1="Apple", B1="Banana")
  • NOT: Reverses the logic of its argument.
    • =NOT(A1>10)

3. Text Functions:

  • CONCATENATE: Joins multiple text strings into one string.
    • =CONCATENATE(A1, " ", B1): Combines the text in A1 and B1 with a space in between.
    • =A1&" "&B1 : alternative to concatenate.
  • LEFT: Returns the specified number of characters from the start of a text string.
    • =LEFT(A1, 3): Returns the first 3 characters of the text in A1.
  • RIGHT: Returns the specified number of characters from the end of a text string.
    • =RIGHT(A1, 4): Returns the last 4 characters of the text in A1.
  • MID: Returns a specified number of characters from the middle of a text string.
    • =MID(A1, 2, 3): Returns 3 characters from A1, starting from the 2nd character.
  • LEN: Returns the number of characters in a text string.
    • =LEN(A1): Returns the length of the text in A1.
  • FIND: Returns the starting position of one text string within another text string.
    • =FIND("a", A1): Finds the position of the first "a" in A1.
  • SEARCH: Similar to FIND, but not case-sensitive, and allows wildcards.
    • =SEARCH("a*",A1)
  • UPPER: Converts text to uppercase.
    • =UPPER(A1)
  • LOWER: Converts text to lowercase.
    • =LOWER(A1)
  • PROPER: Capitalizes the first letter of each word in a text string.
    • =PROPER(A1)
  • TRIM: Removes extra spaces from a text string.
    • =TRIM(A1)

4. Lookup and Reference Functions:

  • VLOOKUP: Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. 1
    • =VLOOKUP(A1, B1:C10, 2, FALSE): Looks for the value in A1 in the first column of B1:C10, and returns the value from the 2nd column of that range. The FALSE argument ensures an exact match.

  • HLOOKUP: Looks for a value in the top row of a table, and then returns a value in the same column from a row you specify.
    • =HLOOKUP(A1, B1:C10, 2, FALSE)
  • INDEX: Returns a value or the reference to a value from within a table or range.
    • =INDEX(B1:C10, 2, 1): Returns the value from the 2nd row and 1st column of B1:C10.
  • MATCH: Returns the relative position of an item in an array that matches a specified value in a specified order.
    • =MATCH(A1, B1:B10, 0): Finds the position of the value in A1 within the range B1:B10. The 0 argument ensures an exact match.
  • INDEX and MATCH (Combined): A more flexible alternative to VLOOKUP.
    • =INDEX(C1:C10, MATCH(A1, B1:B10, 0)): Finds the value from column C that corresponds to the value in A1 in column B.
  • OFFSET: Returns a reference to a range that is a specified number of rows and columns from a reference cell or range.
    • =OFFSET(A1, 1, 2): Returns the value that is 1 row down and 2 columns to the right of A1.

5. Date and Time Functions:

  • TODAY: Returns the current date.
    • =TODAY()
  • NOW: Returns the current date and time.
    • =NOW()
  • DATE: Returns the serial number that represents a particular date.
    • =DATE(2023, 10, 26)
  • YEAR, MONTH, DAY: Extracts the year, month, or day from a date.
    • =YEAR(A1), =MONTH(A1), =DAY(A1)
  • TIME: Returns the serial number that represents a particular time.
    • =TIME(14, 30, 0)
  • HOUR, MINUTE, SECOND: Extracts the hour, minute, or second from a time.
    • =HOUR(A1), =MINUTE(A1), =SECOND(A1)
  • DATEDIF: Calculates the number of days, months, or years between two dates.
    • =DATEDIF(A1, B1, "Y"): Returns the number of full years between A1 and B1.

6. Statistical Functions:

  • AVERAGEIF, AVERAGEIFS: Calculates the average of cells that meet specific criteria.
    • =AVERAGEIF(B1:B10, ">50")
    • =AVERAGEIFS(C1:C10, B1:B10, ">50", A1:A10, "Red")
  • COUNTIF, COUNTIFS: Counts the number of cells within a range that meet specific criteria.
    • =COUNTIF(B1:B10, ">50")
    • =COUNTIFS(B1:B10, ">50", A1:A10, "Red")
  • SUMIF, SUMIFS: Adds the cells in a range that meet specific criteria.
    • =SUMIF(B1:B10, ">50", C1:C10)
    • =SUMIFS(C1:C10, B1:B10, ">50", A1:A10, "Red")

  • 1. Financial Functions:

    • PMT:
      • Calculates the payment for a loan based on constant payments and a constant interest rate.
      • =PMT(rate, nper, pv)
        • rate: The interest rate per period.
        • nper: The total number of payment periods in a loan.
        • pv: The present value, or the total amount that a series of future payments is worth now; also known as the principal.  
    • FV:
      • Calculates the future value of an investment based on periodic, constant payments and a constant interest rate.
      • =FV(rate, nper, pmt, [pv], [type])  
    • NPV:
      • Calculates the net present value of an investment based on a series of periodic cash flows and a discount rate.
      • =NPV(rate, value1, [value2], ...)
    • XNPV:
      • Calculates the net present value for a schedule of cash flows that may not be periodic.
      • =XNPV(rate, values, dates)
    • IRR:
      • Calculates the internal rate of return for a series of cash flows.
      • =IRR(values, [guess])

    2. Statistical Functions (Advanced):

    • STDEV.S:
      • Calculates the sample standard deviation.
      • =STDEV.S(number1,[number2],...)
    • STDEV.P:
      • Calculates the population standard deviation.
      • =STDEV.P(number1,[number2],...)
    • PERCENTILE.INC:
      • Returns the k-th percentile of values in a range.
      • =PERCENTILE.INC(array,k)
    • QUARTILE.INC:
      • Returns the quartile of a data set.
      • =QUARTILE.INC(array,quart)

    3. Lookup and Reference Functions (Advanced):

    • INDEX and MATCH (Dynamic Lookups):
      • This combination is incredibly powerful, overcoming limitations of VLOOKUP.
      • =INDEX(return_range, MATCH(lookup_value, lookup_range, match_type))
        • It allows lookups to the left, and is not limited to the left most column like Vlookup is.
    • OFFSET (Dynamic Ranges):
      • Creates dynamic ranges that can expand or contract.
      • =OFFSET(reference, rows, cols, [height], [width])
    • INDIRECT (Dynamic Cell References):
      • Returns the reference specified by a text string.
      • =INDIRECT(ref_text)
        • This function allows you to use text to create cell references. This is very useful when needing to create dynamic references.
    • CHOOSE:
      • Chooses a value from a list of values.
      • =CHOOSE(index_num, value1, [value2], ...)

    4. Text Functions (Advanced):

    • SUBSTITUTE:
      • Replaces existing text with new text in a text string.
      • =SUBSTITUTE(text, old_text, new_text, [instance_num])
    • REPLACE:
      • Replaces part of a text string, based on the number of characters you specify, with a different text string.
      • =REPLACE(old_text, start_num, num_chars, new_text)  
    • TEXT:
      • Formats a number as text using a specified format code.=TEXT(value, format_text)

    Key Considerations:

    • Error Handling:
      • IFERROR is vital for handling potential errors in formulas, making your spreadsheets more robust.
    • Array Formulas:
      • These powerful formulas can perform complex calculations on multiple values. They are entered by pressing Ctrl + Shift + Enter.
    • Dynamic Arrays:
      • Modern Excel has implemented dynamic arrays that change the way formulas work and how data is outputted.

1. Web and Data Connection Functions:

  • WEBSERVICE:
    • Retrieves data from a web service.
    • =WEBSERVICE("URL")
    • This is fantastic for pulling data from APIs directly into Excel.
  • FILTERXML:
    • Returns specific data from XML content by using the specified XPath.
    • =FILTERXML(xml_text, xpath)
    • This is very useful when combined with the webservice function, to parse the returned XML.

2. Array Manipulation Functions (Dynamic Arrays):

  • FILTER:
    • Filters a range of data based on criteria you define.
    • =FILTER(array, include, [if_empty])
  • SORT:
    • Sorts a range or array.
    • =SORT(array, [sort_index], [sort_order], [by_col])
  • SORTBY:
    • Sorts a range or array based on the values in a corresponding range or array.
    • =SORTBY(array,by_array1,[sort_order1],...)
  • UNIQUE:
    • Returns a list of unique values in a range or array.
    • =UNIQUE(array, [by_col], [exactly_once])
  • SEQUENCE:
    • Generates a sequence of numbers.
    • =SEQUENCE(rows, [columns], [start], [step])
  • XLOOKUP:
    • A powerful lookup function that replaces VLOOKUP and HLOOKUP in many situations.
    • =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

3. Advanced Logical Functions:

  • IFS:
    • Checks multiple conditions and returns a value corresponding to the first TRUE condition.
    • =IFS(logical_test1, value1, [logical_test2, value2], ...)
    • This function simplifies nested IF statements.
  • SWITCH:
    • Evaluates one value (called the expression) against a list of values, and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned. 1  

    • =SWITCH(expression, value1, result1, [default or value2, result2], ...)

4. Information Functions:

  • ISERROR, ISNA, ISNUMBER, ISTEXT, ISBLANK:
    • These functions check the type of a value and return TRUE or FALSE.
    • They are valuable for error handling and data validation.
  • CELL:
    • Returns information about the formatting, location, or contents of a cell.
    • =CELL("info_type", [reference])
  • INFO:
    • Returns information about the current operating environment.
    • =INFO("type_text")

5. Cube Functions (for OLAP data):

  • These functions are used to work with Online Analytical Processing (OLAP) cube data.
    • CUBEVALUE: Returns a value from a cube.
    • CUBEMEMBER: Returns a member from a cube.
    • CUBERANKEDMEMBER: Returns the nth ranked member in a set.
    • CUBEKPIMEMBER: Returns a Key Performance Indicator (KPI) property.
    • These are used to create reports from OLAP data.

Key Strategies:

  • Nested Functions:
    • Combining multiple functions within a single formula can create very powerful calculations.
  • Named Ranges:
    • Using named ranges makes formulas easier to read and maintain.
  • Data Validation:
    • Excel's data validation tools are essential for ensuring data accuracy.
  • Power Query:
    • This is a very powerful tool that is used to transform and import data into excel. It is very versatile.

  • 1. Array Formulas (Legacy and Advanced):

    • FREQUENCY:
      • Calculates how often values occur within a range of values and then returns a vertical array of numbers.
      • =FREQUENCY(data_array, bins_array) 1 

      • This is excellent for creating histograms or frequency distributions.
    • TRANSPOSE:
      • Converts a vertical range of cells to a horizontal range, or vice versa.
      • =TRANSPOSE(array)
      • This is very useful for changing data layout.
    • TREND/FORECAST:
      • These functions perform linear regression to predict future values.
      • =TREND(known_y's, [known_x's], [new_x's], [const])
      • =FORECAST(x, known_y's, known_x's)
    • LINEST:
      • Calculates the statistics for a line.
      • =LINEST(known_y's, [known_x's], [const], [stats])
      • This function returns an array of regression statistics.

    2. Database Functions:

    • These functions are designed for working with data organized as a database (with headers and data rows).
    • DSUM, DAVERAGE, DCOUNT, DMAX, DMIN, DGET:
      • These functions perform calculations based on criteria.
      • =DSUM(database, field, criteria)
      • They are very useful for summarizing data based on specific conditions.
    • DGET:
      • Extracts a single value from a database that matches specified criteria.
      • =DGET(database, field, criteria)

    3. Engineering Functions:

    • Excel has a wide range of engineering functions for various calculations.
    • CONVERT:
      • Converts a number from one measurement system to another.
      • =CONVERT(number, from_unit, to_unit)
      • This is very useful for unit conversions.
    • BIN2DEC, DEC2BIN, HEX2DEC, etc.:
      • These functions convert numbers between different numeral systems (binary, decimal, hexadecimal).
    • COMPLEX:
      • Creates complex numbers.
      • =COMPLEX(real_num, i_num, [suffix])

    4. Add-ins and Power Tools:

    • Power Query (Get & Transform Data):
      • A powerful data transformation and cleansing tool built into Excel.
      • It allows you to import data from various sources, clean it, and transform it into a usable format.
    • Power Pivot:
      • An add-in that allows you to perform powerful data analysis and create data models.
      • It is particularly useful for working with large datasets.
    • Solver:
      • An add-in that allows you to perform optimization calculations.
      • It can be used to solve problems involving constraints and objectives.
    • Analysis ToolPak:
      • An excel add in that provides data analysis tools for statistical and engineering analysis.

    5. Automation with VBA (Visual Basic for Applications):

    • VBA allows you to automate tasks and create custom functions.
    • You can use VBA to:
      • Create custom functions.
      • Automate repetitive tasks.
      • Create user interfaces.
      • Connect to external data sources.

    Key Tips:

    • Function Library:
      • Explore Excel's function library to discover new and useful functions.
    • Help and Documentation:
      • Use Excel's built-in help and online resources to learn more about specific functions.
    • Practice:
      • The best way to learn Excel functions is to practice using them.

This is a robust starting point. Mastering these formulas will significantly boost your Excel proficiency.

Tags
Amit Mahour

Posted by Amit Mahour

Post a Comment

0 Comments

Please comment any query