Excel formula

1.Basic to advance Excel formula

by

An Excel formula is an expression that performs calculations on values in a worksheet, always beginning with an equal sign (=). Formulas can use values, cell references, operators, and functions to provide results automatically in the spreadsheet.

Excel formula
Excel formula

Basic Structure:
=FUNCTION(Argument1, Argument2, …)

  • = : The trigger that tells Excel “what follows is a formula.”
  • FUNCTION: A predefined word that performs a specific calculation (e.g., SUM, AVERAGE, VLOOKUP).
  • Arguments: The values, cell references, or ranges that the function uses. They are enclosed in parentheses and separated by commas.
Operators: Symbols that specify the type of calculation (e.g., + (addition), – (subtraction), * (multiplication), / (division)).

Excel formula

How Excel Formulas Work

  • All formulas start with =, followed by the calculation or function.

  • Formulas can add, subtract, multiply, divide, or use built-in functions for complex tasks.

  • Example: =A1+B1 adds the values in cells A1 and B1.

Example Formulas

  • Basic Math: =1+1, =A1-A2, =A3*2, =C1/D1

  • Using Functions: =SUM(A1:A5) (sum cells A1 to A5), =AVERAGE(B1:B10) (average values), =IF(C1>10, "Yes", "No")

Entering a Formula

  1. Select a cell.

  2. Type = and your calculation (e.g., =A1+B1).

  3. Press Enter to see the result.

Operators and Usage

  • Addition: + (e.g., =A2+B2)

  • Subtraction: - (e.g., =A2-B2)

  • Multiplication: * (e.g., =A2*B2)

  • Division: / (e.g., =A2/B2)

  • Percentage: % (e.g., =A2*10%)

  • Exponent: ^ (e.g., =A2^3)

Excel formulas are the foundation for effective data analysis and automation within spreadsheets.

Here is a list of the most common and essential Excel formulas used for calculations, data analysis, and manipulation, along with their basic purposes and example syntax:

Excel formula

Mathematical & Statistical Functions

  • SUM: Adds up numbers. =SUM(A1:A5)

  • AVERAGE: Calculates the mean. =AVERAGE(B1:B10)

  • COUNT: Counts numbers. =COUNT(C1:C8)

  • COUNTA: Counts non-empty cells. =COUNTA(D1:D10)

  • MAX: Highest value. =MAX(E1:E6)

  • MIN: Lowest value. =MIN(F1:F4)

  • ROUND: Rounds a number. =ROUND(G1,2)

Logical Functions

  • IF: Conditional test. =IF(H1>10, "Pass", "Fail")

  • AND: Checks if all are TRUE. =AND(I1>0, I2<5)

  • OR: Checks if any is TRUE. =OR(J1="Yes", J2="No")

  • IFERROR: Custom result if error. =IFERROR(K1/L1, "Error")

Lookup & Reference

  • VLOOKUP: Vertical lookup. =VLOOKUP(M1, A1:B10, 2, FALSE)

  • HLOOKUP: Horizontal lookup. =HLOOKUP(M1, A1:J2, 2, FALSE)

  • INDEX: Returns value at a specific position. =INDEX(N1:O10, 2, 1)

  • MATCH: Returns position of a value. =MATCH(P1, Q1:Q10, 0)

  • OFFSET: Reference offset from a cell. =OFFSET(A1, 2, 1)

Summing & Counting with Criteria

  • SUMIF: Condition-based sum. =SUMIF(R1:R10, ">50")

  • COUNTIF: Count with condition. =COUNTIF(S1:S10, "Apple")

  • SUMIFS: Multiple conditions sum. =SUMIFS(T1:T10, U1:U10, "Red", V1:V10, ">0")

  • COUNTIFS: Multiple conditions count. =COUNTIFS(W1:W10, "A", X1:X10, ">10")

Text Functions

  • CONCATENATE / CONCAT: Combines text. =CONCATENATE("Hello ", "World")

  • TEXTJOIN: Joins with delimiter. =TEXTJOIN(", ", TRUE, Y1:Y5)

  • LEFT: Leftmost characters. =LEFT(Z1, 3)

  • RIGHT: Rightmost characters. =RIGHT(AA1, 2)

  • MID: Substring from middle. =MID(AB1, 2, 3)

  • UPPER / LOWER / PROPER: Changes case. =UPPER(AC1)

  • LEN: Text length. =LEN(AD1)

  • TRIM: Removes extra spaces. =TRIM(AE1)

Date & Time Functions

  • TODAY: Current date. =TODAY()

  • NOW: Current date/time. =NOW()

  • DATE: Creates date. =DATE(2025,8,25)

  • DAY / MONTH / YEAR: Extract parts. =DAY(AF1)

  • EOMONTH: End of month. =EOMONTH(AG1, 1)

  • DATEDIF: Date difference. =DATEDIF(AH1, AI1, "d")

Financial Functions

  • PMT: Loan payment. =PMT(rate, nper, pv)

  • XNPV / XIRR: Finance calculations. =XNPV(rate, values, dates)

Data Tools

  • UNIQUE: Unique values from a range. =UNIQUE(AJ1:AJ10)

  • SORT: Sorts a range. =SORT(AK1:AL10)

  • FILTER: Filter data by criteria. =FILTER(AM1:AN10, AO1:AO10>50)

Array & Other Useful Formulas

  • SUMPRODUCT: Sum of products. =SUMPRODUCT(AP1:AP5, AQ1:AQ5)

  • CELL: Info about a cell. =CELL("address", AR1)

  • INDIRECT: Reference from text. =INDIRECT("A"&AS1)

For exhaustive official references, Excel’s alphabetic and category lists cover all built-in formulas.microsoft+1

Excel formula

 Advanced Formula Concepts

  • Cell References: Absolute vs. Relative
    • Relative (A1): Changes when copied to another cell. (Default)
    • Absolute ($A$1): Locks the reference to a specific cell. Doesn’t change when copied. (Press F4 to toggle).
    • Mixed (A$1 or $A1): Locks either the row or the column.

Example: If you copy the formula =$B$1 * A2 from C2 to C3, it becomes =$B$1 * A3. The reference to B1 is locked.

  • Nested Functions: Using a function as an argument inside another function.
    • Example: =IF(SUM(A1:A10)>100, “Goal Met”, “Keep Going”)
      This first sums the range A1:A10, then the IF function checks if that sum is greater than 100.
  • Array Formulas (Dynamic Arrays in Office 365): Formulas that perform multiple calculations on one or more items in an array. Modern Excel (with the UNIQUE, SORT, FILTER, SEQUENCE functions) makes this incredibly powerful and easy.

Example: =SORT(UNIQUE(FILTER(A2:A100, B2:B100=”West”)))

Additional Resources

  • For a much larger, searchable formula list (including advanced and new Excel functions), Microsoft’s official support and specialized Excel sites offer extensive catalogs.

  • You can also visit this sites :https://sampurnjankari.com/

Leave a Comment