Excel formulas are powerful expressions that begin with an equal sign (=) and allow you to perform calculations, manipulate data, analyze information, and automate tasks in your spreadsheets. They can reference cell values, ranges, or other formulas, making Excel one of the most versatile tools for data management, finance, reporting, and business analysis.
Quick Tips for Using Formulas in Excel
Start every formula with = (e.g., =SUM (A1:A10)).
Use cell references (like A1), ranges (like A1:A10), or named ranges for flexibility.
Press Enter to apply the formula. Drag the fill handle (bottom-right corner of the cell) to copy it to other cells.
Use F2 to edit a formula or Ctrl + ` (grave accent) to view all formulas in the sheet.
Modern Excel (Microsoft 365/Excel 2021+) supports dynamic arrays that automatically "spill" results into multiple cells.
Always check for errors like #DIV/0!, #VALUE!, or #REF! and use tools like IFERROR if needed.
Below is a curated list of the top 20 most used Excel functions (based on popularity across professionals, analysts, and Microsoft documentation in 2026). Each includes:
A clear description.
Syntax.
A practical example with sample data.
Step-by-step instructions.
The expected result.
SUM
Description: Adds up a range of numbers. One of the most basic and frequently used functions for totals.
Syntax: =SUM(number1, [number2], ...)
Example Data: Cells A1:A5 contain: 100, 200, 150, 300, 250.
Step-by-step:
Click cell A6.
Type =SUM(A1:A5).
Press Enter.
Result: 1000
AVERAGE
Description: Calculates the arithmetic mean (average) of a range of numbers.
Syntax: =AVERAGE(number1, [number2], ...)
Example Data: Same as above (A1:A5: 100, 200, 150, 300, 250).
Step-by-step:
Click cell A7.
Type =AVERAGE(A1:A5).
Press Enter.
Result: 200
COUNT
Description: Counts only cells that contain numbers (ignores text, blanks, and errors).
Syntax: =COUNT(value1, [value2], ...)
Example Data: Cells B1:B6 contain: 10, 20, "Text", 30, "", 40.
Step-by-step:
Click cell B7.
Type =COUNT(B1:B6).
Press Enter.
Result: 4 (only the numbers are counted)
COUNTA
Description: Counts all non-empty cells (numbers, text, dates, errors—ignores blanks).
Syntax: =COUNTA(value1, [value2], ...)
Example Data: Same as above (B1:B6).
Step-by-step:
Click cell B8.
Type =COUNTA(B1:B6).
Press Enter.
Result: 5
MAX
Description: Returns the largest number in a range.
Syntax: =MAX(number1, [number2], ...)
Example Data: Cells C1:C5 contain: 45, 67, 23, 89, 12.
Step-by-step:
Click cell C6.
Type =MAX(C1:C5).
Press Enter.
Result: 89
MIN
Description: Returns the smallest number in a range.
Syntax: =MIN(number1, [number2], ...)
Example Data: Same as above (C1:C5).
Step-by-step:
Click cell C7.
Type =MIN(C1:C5).
Press Enter.
Result: 12
IF
Description: Performs a logical test and returns one value if true, another if false. Great for conditional logic.
Syntax: =IF(logical_test, value_if_true, value_if_false)
Example Data: Cells D1:D5 contain sales: 5000, 12000, 8000, 3000, 15000. In E1:E5 we will apply the formula.
Step-by-step:
Click cell E1.
Type =IF(D1>10000, "High", "Low").
Drag the fill handle down to E5.
Result:
E1: Low
E2: High
E3: Low
E4: Low
E5: High
SUMIF
Description: Adds numbers in a range that meet a single criterion.
Syntax: =SUMIF(range, criteria, [sum_range])
Example Data: Sales in A1:A5 (Region: North, South, North, East, North); Amounts in B1:B5: 1000, 2000, 1500, 3000, 2500.
Step-by-step:
Click cell B7 (to sum North region).
Type =SUMIF(A1:A5, "North", B1:B5).
Press Enter.
Result: 5000 (1000 + 1500 + 2500)
COUNTIF
Description: Counts cells in a range that meet a single criterion.
Syntax: =COUNTIF(range, criteria)
Example Data: Same as above (A1:A5 regions).
Step-by-step:
Click cell A7 (to count "North").
Type =COUNTIF(A1:A5, "North").
Press Enter.
Result: 3
VLOOKUP
Description: Searches vertically for a value in the first column of a table and returns a value from the same row in a specified column. (Note: XLOOKUP is preferred in modern Excel.)
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example Data: Table in A1:C4:
A1: "Apple", B1: "Fruit", C1: 1.50
A2: "Banana", B2: "Fruit", C2: 0.75
A3: "Carrot", B3: "Vegetable", C3: 0.90
Step-by-step:
In E1 type "Banana".
Click E2.
Type =VLOOKUP(E1, A1:C4, 3, FALSE).
Press Enter.
Result: 0.75 (price of Banana)
XLOOKUP
Description: Modern, flexible replacement for VLOOKUP. Searches in any direction and handles errors gracefully.
Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Example Data: Same table as VLOOKUP.
Step-by-step:
In F1 type "Carrot".
Click F2.
Type =XLOOKUP(F1, A1:A4, C1:C4, "Not found").
Press Enter.
Result: 0.90
TEXTJOIN
Description: Joins multiple text strings with a delimiter (e.g., commas). Ignores empty cells.
Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Example Data: Cells G1:G4 contain: "John", "Doe", "", "Jr".
Step-by-step:
Click G5.
Type =TEXTJOIN(" ", TRUE, G1:G4).
Press Enter.
Result: John Doe Jr
LEFT
Description: Extracts a specified number of characters from the start of a text string.
Syntax: =LEFT(text, [num_chars])
Example Data: Cell H1: "Excel2026".
Step-by-step:
Click H2.
Type =LEFT(H1, 5).
Press Enter.
Result: Excel
RIGHT
Description: Extracts a specified number of characters from the end of a text string.
Syntax: =RIGHT(text, [num_chars])
Example Data: Same cell H1: "Excel2026".
Step-by-step:
Click H3.
Type =RIGHT(H1, 4).
Press Enter.
Result: 2026
MID
Description: Extracts characters from the middle of a text string.
Syntax: =MID(text, start_num, num_chars)
Example Data: Same cell H1: "Excel2026".
Step-by-step:
Click H4.
Type =MID(H1, 6, 4).
Press Enter.
Result: 2026
LEN
Description: Returns the number of characters in a text string (including spaces).
Syntax: =LEN(text)
Example Data: Cell I1: "Hello World".
Step-by-step:
Click I2.
Type =LEN(I1).
Press Enter.
Result: 11
TRIM
Description: Removes extra spaces from text (leading, trailing, and between words).
Syntax: =TRIM(text)
Example Data: Cell J1: " Too many spaces ".
Step-by-step:
Click J2.
Type =TRIM(J1).
Press Enter.
Result: Too many spaces
ROUND
Description: Rounds a number to a specified number of digits.
Syntax: =ROUND(number, num_digits)
Example Data: Cell K1: 3.14159.
Step-by-step:
Click K2.
Type =ROUND(K1, 2).
Press Enter.
Result: 3.14
TODAY
Description: Returns the current date (updates automatically every day).
Syntax: =TODAY()
Step-by-step:
Click any empty cell (e.g., L1).
Type =TODAY().
Press Enter.
Format the cell as Date (Home → Number → Short Date).
Result: Current date (e.g., 05/03/2026)
NOW
Description: Returns the current date and time (updates automatically).
Syntax: =NOW()
Step-by-step:
Click any empty cell (e.g., M1).
Type =NOW().
Press Enter.
Format as Date & Time.
Result: Current date and time (e.g., 05/03/2026 14:26)
Final Tips for Mastery
Combine functions for power: e.g., =IF(SUMIF(...)>1000, "Bonus", "No Bonus").
Use dynamic arrays (Excel 365) for spill ranges with functions like XLOOKUP or FILTER.
Learn named ranges and tables (Insert → Table) to make formulas easier to read.
Practice with real data and explore Formula Auditing tools (Formulas tab).
Master these 20 functions and you’ll handle 90% of everyday Excel tasks efficiently. Start with a blank workbook, recreate the examples above, and experiment!
 |  |  |